vlookup in vba

[Rachel]

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!range("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

[Tim Williams]

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" <Rachel@discussions.microsoft.com> wrote in message
news:B8E4C24C-B8C2-48FB-A1E4-AAA84E5A5B59@microsoft.com...
>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!range("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

[Karthik Bhat - Bangalore]

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