VBA Assign Vlookup Result To Variable

[Lewzerrrr]

Hey,

I’ve searched around for this, what I’m trying to do is store a vlookup result inside my variable. At first I switched my sheet to R1C1 and put the VLOOKUP inside an EVALUATE which worked fine. However when I switched back from R1C1 on the sheet it gave me a type mismatch.

So I tried to use a normal VLOOKUP which also gives type mismatch. Overall, my code is a worksheet change only looking at column J, from there I then need a =VLOOKUP(Target.Offset(,-8),’Sheet2’!A:I,9,0). In normal it needs to be a VLOOKUP(B1,’Sheet2’!A:I,9,0) but the lookup value is relative.

Is it better to use a function? Something like this?

Any suggestions?

Dim V as String

V = Worksheetfunction.VLOOKUP()

 

[Fluff]

How about

Dim v as String
v = Evaluate("VLOOKUP(" & Target.Offset(, -8).Address & " ,'Records'!A:I,9,0)")
MsgBox v

 

[Lewzerrrr]

How about

Dim v as String
v = Evaluate("VLOOKUP(" & Target.Offset(, -8).Address & " ,'Records'!A:I,9,0)")
MsgBox v

Thanks! Hopefully should work, I was 1 syntax off ? “.address”