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.

 

[albytross]

Sub XYZ_PopulateFields()
    Dim i As Long, lastr As Long
    'Is activesheet syntax required?
        ActiveSheet.Name = "XYZ"
        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

This macro will be triggered from a different sheet, so is activesheet syntax required?

The macro showed no interaction with the cells on sheet XYZ.
Also the only entered value on sheet XYZ should come from cell ABC!A2, to populate column A.

Thanks for your help James

 

[albytross]

Hi James,

Solutions:

Sub XYZ_PopulateFields()
    Dim ws As Worksheet
    Dim lRow As Long

    Set ws = ThisWorkbook.Sheets("XYZ")
    'Column A Project
    With ws
        lRow = .Range("B" & .Rows.Count).End(xlUp).Row

        .Range("A2:A" & lRow).Formula = "=If(B2<>"""",""=ABC!A2"","""")"
        .Range("A2:A" & lRow).Value = .Range("A2:A" & lRow).Value
    End With

 

[James006]

You are welcome