How do you create a VLOOKUP using VBA 7.1?

Question

I would like to use VBA to update cells in an Excel Worksheet to VLOOKUP values in another worksheet in the same spreadsheet, this is in the likes of:

Cells(2, intColNum + 1).Formula = "=VLOOKUP(R[0]C[-1],NameKeyIndex!C:D,2,FALSE)"

However, when I run it in VBA, it goes into Excel like this:

=VLOOKUP(E3,NameKeyIndex!F:F:D,2,FALSE)

If I change it manually in Excel to C:D , it works, however is there a way it can not change my code and parse it as it is correctly?

Answer 1

If you're using R1C1 notation in your formula, then set it using FormulaR1C1 (and don't mix A1 and R1C1 within the same formula)

Cells(2, intColNum + 1).FormulaR1C1 = "=VLOOKUP(R[0]C[-1],NameKeyIndex!C3:C4,2,FALSE)"