vlookup in VBA

[John Pierce]

I am using Excell 2000 with Windows 2000 Professional.
I am trying to get the result of a vlookup into a cell by using

For I = 2 To numrows
Cells(I, "F") = Evaluate("=VLOOKUP(cells(i, "B"),Box_Log,6)")

Next I

I get a "Compile Error: Expected list separator or )" at the "B".
What gives?

 

[Don Guillett]

I just tested this for one
x = Application.VLookup(Cells(1, "d"), Range("f1:i6"), 2, 0)

or

For i = 1 To 9
Cells(i, "f") = Application.VLookup(Cells(i, "b"), Range("g1:i6"), 2, 0)
Next i

 

[Tom Ogilvy]

Cells(I, "F") = Evaluate("=VLOOKUP(cells(i, "B"),Box_Log,6)")

should be


Cells(I, "F") = Evaluate("VLOOKUP(cells(i, ""B""),Box_Log,6)")

you have to double up on double quotes contained within a string.

But Don gives a better solution by not using evaluate.