vlookup in vba
 

I try to get the vlookup results to a variable via a vba macro
however, the table of data is in book2 and my macro and active sheet is in
book1. Both books are on the same directory
I use excel2000 and windows xp and it does not work
the specific line of the vba is:
VLUV = application.worsheetfunction.vlookpu(27,book2!rang e("a1:g12"),3,false)

actually I'd rather like to use book2!table01 instead of the range function
but
none of this two ways work

please help

vlookup in vba
 

Is the otherworkbook open ?
There is a typo ("vlookpu") in the code you posted - is it also in your
orignal code?

if the workbook is open try something like

dim r as range
set r = workbooks("book2.xls").sheets("sheetname").range(" A1:G12")
VLUV = application.worsheetfunction.vlookup(27,r,3,false)

Tim



"Rachel" wrote in message
...
I try to get the vlookup results to a variable via a vba macro
however, the table of data is in book2 and my macro and active sheet is in
book1. Both books are on the same directory
I use excel2000 and windows xp and it does not work
the specific line of the vba is:
VLUV =
application.worsheetfunction.vlookpu(27,book2!rang e("a1:g12"),3,false)

actually I'd rather like to use book2!table01 instead of the range
function
but
none of this two ways work

please help

vlookup in vba
 

Hi Rachel

The word vlookup is spelt wrongly in the code you need to correct taht
first.

To use book2!table01 in your code first name the range a1:g12 in Book2.
This is how you do it.

Select a1:g12 in Book2 and then go to Insert Name Define.
Type table01 under 'Names in workbook:' field and the field 'Refers
to:' should get picked up automatically & click OK. Now the required
range has a defined name and can be used in formulas and VBA code.
Replace book2!range("a1:g12") in your code with book2!table01

These 2 should take care of your Macro errors.

Thanks
Karthik Bhat