Copying a vlookup code on newly created document not working

[Damian37]

Hello everyone,
I'm attempting to use a vlookup formula within my VBA code. I recorded a macro and attempted to remove as much noise as I could. However I continue to receive a "Run-Time error '1004'
Application-defined or object-defined error. The code in bold red is the line of code that is being highlighted when debug is clicked on the error message.

ub vlookup()
'
' vlookup Macro
'   SetWs = ActiveWorkbook.Worksheets("Sheet1")

'
    Set wkb2 = ActiveWorkbook.Worksheets("Sheet1")
    
    wkb2.Range("AE1").Value = "SMMO_EMAIL"
    ActiveCell.FormulaR1C1 = "SMMO EMAIL"
    wkb2.Range("AE2:AE").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(VALUE(RC[-30]),'[Mobile Stores - SMMO Listing.xlsx]Sheet1'!C1:C5,5,FALSE)"
    wkb2.Range("AE3").Select
    'Workbooks("Negative Replenishment file_" & Format(Date, "mm.dd.yyyy") & ".xlsx")
    LastRow = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
    wkb2.Range("A2:AE" & LastRow).FillDown
End Sub

I'm trying to get the Vlookup formula to copy all the way down to the last row that has data. I appreciate any and all help I receive. Thank you.
D.

 

[Fluff]

Try

Sub VLookup()
'
' vlookup Macro
'   SetWs = ActiveWorkbook.Worksheets("Sheet1")

'
    Set wkb2 = ActiveWorkbook.Worksheets("Sheet1")
    LastRow = wkb2.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

    wkb2.Range("AE1").Value = "SMMO_EMAIL"
    wkb2.Range("AE2:AE" & LastRow).FormulaR1C1 = _
        "=VLOOKUP(VALUE(RC[-30]),'[Mobile Stores - SMMO Listing.xlsx]Sheet1'!C1:C5,5,FALSE)"
    'Workbooks("Negative Replenishment file_" & Format(Date, "mm.dd.yyyy") & ".xlsx")
    
End Sub

 

[Damian37]

Try

Sub VLookup()
'
' vlookup Macro
'   SetWs = ActiveWorkbook.Worksheets("Sheet1")

'
    Set wkb2 = ActiveWorkbook.Worksheets("Sheet1")
    LastRow = wkb2.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

    wkb2.Range("AE1").Value = "SMMO_EMAIL"
    wkb2.Range("AE2:AE" & LastRow).FormulaR1C1 = _
        "=VLOOKUP(VALUE(RC[-30]),'[Mobile Stores - SMMO Listing.xlsx]Sheet1'!C1:C5,5,FALSE)"
    'Workbooks("Negative Replenishment file_" & Format(Date, "mm.dd.yyyy") & ".xlsx")
    
End Sub

Hello Fluff,
Thank you very much that worked like a charm!
D.

 

[Fluff]

You're welcome & thanks for the feedback