when value of Variant is empty assign value in another Variant

July 18, 2022

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

>Solution :

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