VBA Vlookup where Lookup_value column number is specifies

1
votes

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.

2
vbaexcelvlookup
convert the whole formula to use just the .Formula property not the .FormulaR1C1 property (msdn.microsoft.com/en-us/library/office/…) – sous2817
Use R3C1 (Row 3, Column 1) - it's the absolute reference to cell A3 in R1C1 format. – Darren Bartrup-Cook

2 Answers

1
votes

This will solve your problem

ActiveCell.FormulaR1C1 = _ "=VLOOKUP(R3C1,'[Equities EMIRNew.xlsx]Valuation Summary'!R2C4:R100C5,1,FALSE)"

Remember in VBA that when using .FormulaR1C1 to use R1C1 formats for your references.

0
votes

there is a quick and dirty solution to your problem. The idea is that FormulaR1C1 expects a string, thus you should indent every time l_counter by -1.

Public Sub vlookup()

Dim l_counter As Long: l_counter = 7
'your loops here
    l_counter = l_counter - 1
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-" & l_counter & "],'[Equities EMIR New.xlsx]Valuation Summary'!R2C4:R100C5,1,FALSE)"
 'your loops here
 End Sub