I'm currently writing VBA code which includes a Vlookup. The code I have at the moment is this:

**ActiveCell.FormulaR1C1 = \_
 "=VLOOKUP(RC[-7],'[Equities EMIR New.xlsx]Valuation Summary'!R2C4:R100C5,1,FALSE)"**

The problem I have is that the column that the Vlookup will be in a cell in a newly inserted column each time it is run. Therefore having RC[-7] for the lookup value won't work once the macro has been run more than once.

The lookup value will remain in the same cell (A3), however when I try and replace RC[-7] with A3, I get a #NAME? error.

I've been searching for days and I can't find a solution to this, is anyone able to assist?

Thanks.