Issue with FormulaR1C1

Question

I need a little help with the below formula in VBA:

=IFERROR(VLOOKUP(J3,'Int Data'!D:J,7,0),"")

I've tried to run the following:

Range("AP2").Select    
ActiveCell.Formula = "=IFERROR((VLOOKUP(RC[-1],(J3,'Agent Table'!D:J,7,0),"")"    
Range("AP2").Select    
Selection.Copy    
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False    
Cells.Select    
Cells.EntireColumn.AutoFit

When I try to run this statement -

ActiveCell.Formula = "=IFERROR((VLOOKUP(RC[-1],(J3,'Agent Table'!D:J,7,0),"")"

I got a:

Run-time error '1004'

Can you please advise what is wrong with this statement?

Answer 1

You need .FormulaR1C1 and not .Formula:

ActiveCell.FormulaR1C1 = "=IFERROR(VLOOKUP(R3C10,'Agent Table'!C4:C10,7, 0)"""")"

R3C10 is the R1C1 notation for J3.

If you are using the Macro Recorder to see the formula, select "Use Relative References" in the developer tab to change the way the address is displayed: