VBA - Enter value if column not blank

[albytross]

Hi,

In sheet XYZ I have values in column B.

I need column A in XYZ to be populated with the value from cell A2 in worksheet "ABC" - but only as far as column B is populated.

Should be easy, maybe so easy nobody has asked before because I can't find a solution online.

Would appreciate some help if anyone has a moment.

 

[Anthony47]

I assume you meant "but only as far as column B in sheet XYZ is populated".
Thus, in XYZ-A2:

=IF(B2<>"",ABC!A2,"")

 

[albytross]

Hi Anthony, thanks - this is how i've written it in VBA but its coming up with Run-Time error 1004.

Would you know where I've gone wrong?

Sub XYX_Populate()
    ActiveSheet.Name = "XYZ"
    Range("A:A").Value = "=IF(B2<>"",ABC!A2,"")"
    
End Sub

 

[James006]

Hi,
Watch out Column A has over 1 Million Rows . !!! 1'048'576 to be precise ...
Better define your actual Range ...

 

[James006]

You can test following

Sub XYZ_Populate()
Dim i As Long
Dim lastr As Long
lastr = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row
    For i = 2 To last
        Range("A" & i) = Evaluate("=IF(B" & i & "<>"""",ABC!A" & i & ","""")")
    Next i
End Sub

 

[albytross]

Hi,
Watch out Column A has over 1 Million Rows . !!! 1'048'576 to be precise ...
Better define your actual Range ...

thanks. I confined the range to A200 but still get the error. i figure its down to the actual syntax, but don't know where abouts..

 

[James006]

Have you tested the macro suggested in post #5 ...???

 

[albytross]

You can test following

Sub XYZ_Populate()
Dim i As Long
Dim lastr As Long
lastr = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row
    For i = 2 To last
        Range("A" & i) = Evaluate("=IF(B" & i & "<>"""",ABC!A" & i & ","""")")
    Next i
End Sub

Did it compile for you? I'm getting a syntax error

 

[James006]

Sorry for typo ...
just replace last by lastr

Sub XYZ_Populate()
Dim i As Long, lastr As Long
lastr = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row
For i = 2 To lastr
     Range("A" & i) = Evaluate("=IF(B" & i & "<>"""",ABC!A" & i & ","""")")
Next i
End Sub

 

[James006]

Once you have tested the macro, feel free to share your comments