I am trying to make range(cells(1,1), cells(10,1) to have value in Range AAA and when value is empty in AAA, it shows value in Range BBB. But the result what I got is only have Range BBB in given range.
For example:
- When AAA(1) = 12345, Cells(1,1) = 12345
- When AAA(2) = empty, Cells(2,1) = BBB(2) = 54321
With Activesheet
Range(cells(1,1), cells(10,1) = AAA
If Range(cells(1,1), cells(10,1) = "" Then
Range(cells(1,1), cells(10,1) = BBB
End if
End With
CodePudding user response:
Iterate AAA
and replace the blanks with the corresponding value in BBB
then apply the array:
Dim i As Long
For i = LBound(AAA, 1) To UBound(AAA, 1)
If AAA(i, 1) = "" Then AAA(i, 1) = BBB(i, 1)
Next i
With ActiveSheet
.Range(.Cells(1, 1), .Cells(10, 1)) = AAA
End With
If you do not want to modify AAA
then create a third Array and use that for the output:
Dim CCC As Variant
CCC = AAA
Dim i As Long
For i = LBound(AAA, 1) To UBound(AAA, 1)
If AAA(i, 1) = "" Then CCC(i, 1) = BBB(i, 1)
Next i
With ActiveSheet
.Range(.Cells(1, 1), .Cells(10, 1)) = CCC
End With