How to use Vlookup in vba

Question

I am trying to use vlookup through VBA to fetch values across two different workbooks (A,B) and fill some values in a column of workbook A

The important requirment for me is that the search range in workbook B should be dynamic.

Therefore I have already calculated the number of rows of the table in workbook B.

Here is the code I tried to write.It does not compile due to the syntax error inside the Vlookup:

ActiveWorkbook.Sheets("Sheet1").Activate
     Columns("M:M").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("M2").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(VLOOKUP(RC[-12],[petest.xlsx]Sheet1!A1:"&lrow2,8,FALSE),RC[-1])"
    Range("M2").Select
    Selection.AutoFill Destination:=Range("M2:M" & lrow)
    Range("M2:M" & lrow).Select

Here lrow contains the last column of the table of the first workbook while lrow2 the last column in the second workbook ( the one I am searching into)

Answer 1

Try changing this line :

 ActiveCell.FormulaR1C1 = _
    "=IFERROR(VLOOKUP(RC[-12],[petest.xlsx]Sheet1!A1:"&lrow2,8,FALSE),RC[-1])"

to

 ActiveCell.FormulaR1C1 = _
        "=IFERROR(VLOOKUP(RC[-12],[petest.xlsx]Sheet1!A1:L" & lrow & ",8,FALSE),RC[-1])"

This will insert the actual value of lrow into the formula

UPDATE

You need to add "L" to the vlookup formula so it has A1:L and not just A1. I have edited the code above. Try that please