Syntax - set variable to result of Vlookup

[Guest]

I need some syntax help please…trying to set a variable to the result of this
vlookup formula:

myVariable = "=IF(ISERROR(VLOOKUP($E2,mySheet!myNamedRange," &
myVariablePositon & ",FALSE)),0,VLOOKUP($E2,mySheet!myNamedRange," &
myVariablePositon & " ,FALSE))"

I’ve tried various forms of WorksheetFunction.Vlookup & can’t get it. I
could replace the variable myVariablePosition to a specific column number,
but it is important to me to return a zero in the event of an error.

Ultimately, I want to do something like:

For i = 2 to lstrow

myVariable1 = "=IF(ISERROR(VLOOKUP($E2,mySheet!myNamedRange," &
myVariablePositon1 & ",FALSE)),0,VLOOKUP($E2,mySheet!myNamedRange," &
myVariablePositon1 & " ,FALSE))"

myVariable2 = "=IF(ISERROR(VLOOKUP($E2,mySheet!myNamedRange," &
myVariablePositon2 & ",FALSE)),0,VLOOKUP($E2,mySheet!myNamedRange," &
myVariablePositon2 & " ,FALSE))"

myResult = myVariable1 + myVariable2

cells(i,6) = myResult

Next i

Thanks in advance!

 

[Tom Ogilvy]

MyResult = Evaluate(myVariable1) + Evaluate(myVariable2)

 

[Guest]

Excellent. I had spent waaay to much time assuming that I had the
myVariable/vlookup part wrong!

One more question if I could - are myVariable1 and myVariable2 declared as a
strings then, or must they be a variant?

Thanks so much... I do appreciate it!

 

[Tom Ogilvy]

What ever is returned by the vlookup. If it will be a string, then use
string, but change the + to &

if they will return numbers, then dim it as double or long depending on
whether it will be decimal number or not.