I am trying to vlookup a dynamic Column. here's my current code: Range(s3 & FirstCol).Formula = "=VLOOKUP(" & nge & ",'[" & Range("D1").Value & "]Pivot Data'!R1C2:R65535C11,10,0)" with Dim nge as String nge = "A3:A" However the result from excel (after running the code) is : =VLOOKUP('A3':A,'[Input WS# here-Historic_Price_Check-111715.xls]Pivot Data'!$B$1:$K$65535,10,0)" =#NAME? Normally, vba shows this as RC[xx] however I cannot use RC since the position of my lookup cell is dynamic. Anybody who can help me this is simple prob?

If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.

Dynamic VlookUp

Thread starter pjkaphlen
Start date Nov 17, 2015

pjkaphlen

Board Regular

Nov 17, 2015

I am trying to vlookup a dynamic Column. here's my current code:

Code:

Range(s3 & FirstCol).Formula = "=VLOOKUP(" & nge & ",'[" & Range("D1").Value & "]Pivot Data'!R1C2:R65535C11,10,0)"

with

Dim nge as String
nge = "A3:A"

However the result from excel (after running the code) is :

Code:

=VLOOKUP([B][COLOR=#ff0000]'A3':A[/COLOR][/B],'[Input WS# here-Historic_Price_Check-111715.xls]Pivot Data'!$B$1:$K$65535,10,0)"

=#NAME?

Normally, vba shows this as RC[xx] however I cannot use RC since the position of my lookup cell is dynamic.

Anybody who can help me this is simple prob?