CODEWITHSUNDEEP
vbavariant
CPS
CPS

Reputation: 3

when value of Variant is empty assign value in another Variant

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:

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

Upvotes: 0

Views: 64

Answers (1)

Scott Craner
Scott Craner

Reputation: 152465

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

Upvotes: 1

Related Questions