How to define table array in Vlookup function using VBA?

[michaellm]

there's one integer variable, j.
I want to use VLookup function here, and the table Array parameter
should be Column j to j+1
But how to write this code in VBA?
Application.VLookUp(Cells(1,1), COLUMNS(j,j+1), 2 FALSE) ???
It report erros like above.
Thanks.

 

[weavtennis]

If you set up a Vlookup function in the spreadsheet...(un-used cell)

and identify a cell to paste the lookup value... (lookup function first
term)

you can use Excel to do the lookup and then just "get" the value from
the function cell...

something like:

Cells(1,1)="MyValue"
Return_value=cells(1,2)

where the lookup value is in cell(1,1) and the lookup formula is in
cell(1,2)...

 

[Bob Phillips]

Try

Application.VLookUp(Cells(1,1), COLUMNS(j).Resize(,2), 2 FALSE)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[michaellm]

Thanks Bob, it works!
appreciated

Bob Phillips 写é“：