vlookup table in another workbook

[Rachel]

I want to get the vlookup results to a variable, where that table data is in
book2
and the key search is in book1 somthing like
Vlook = application.worksheet.vlookup(book1!sheet1[a1],
book2!sheet1[table01], 2)
this does not work. Both worbooks are in the same directory, and I use excel
2000
and windoew xp
thanks rachel

[Niek Otten]

Hi Rachel,

application.WorksheetFunction.VLookup(workbooks("book1").worksheets("sheet1").range("a1"),workbooks("book2").worksheets("sheet1").range("table01"),2)

--
Kind regards,

Niek Otten

"Rachel" <Rachel@discussions.microsoft.com> wrote in message
news:6DA588B6-D353-4EB1-9F7D-FEC97F9059CE@microsoft.com...
>I want to get the vlookup results to a variable, where that table data is
>in
> book2
> and the key search is in book1 somthing like
> Vlook = application.worksheet.vlookup(book1!sheet1[a1],
> book2!sheet1[table01], 2)
> this does not work. Both worbooks are in the same directory, and I use
> excel
> 2000
> and windoew xp
> thanks rachel

[Rachel]

Dear Niek,
this does not work, I get this error result that does not really help
"Object does not support this property or methos"
can you figuer it out?
Thanks in advance
rachel

"Niek Otten" wrote:

> Hi Rachel,
>
> application.WorksheetFunction.VLookup(workbooks("book1").worksheets("sheet1").range("a1"),workbooks("book2").worksheets("sheet1").range("table01"),2)
>
> --
> Kind regards,
>
> Niek Otten
>
> "Rachel" <Rachel@discussions.microsoft.com> wrote in message
> news:6DA588B6-D353-4EB1-9F7D-FEC97F9059CE@microsoft.com...
> >I want to get the vlookup results to a variable, where that table data is
> >in
> > book2
> > and the key search is in book1 somthing like
> > Vlook = application.worksheet.vlookup(book1!sheet1[a1],
> > book2!sheet1[table01], 2)
> > this does not work. Both worbooks are in the same directory, and I use
> > excel
> > 2000
> > and windoew xp
> > thanks rachel
>
>
>

[Niek Otten]

Hi Rachel,

Worked OK for me in Excel 2003

Sorry!

--
Kind regards,

Niek Otten

"Rachel" <Rachel@discussions.microsoft.com> wrote in message
news:94ACE6D4-E80B-47E0-B211-6F17B5EB067D@microsoft.com...
> Dear Niek,
> this does not work, I get this error result that does not really help
> "Object does not support this property or methos"
> can you figuer it out?
> Thanks in advance
> rachel
>
> "Niek Otten" wrote:
>
>> Hi Rachel,
>>
>> application.WorksheetFunction.VLookup(workbooks("book1").worksheets("sheet1").range("a1"),workbooks("book2").worksheets("sheet1").range("table01"),2)
>>
>> --
>> Kind regards,
>>
>> Niek Otten
>>
>> "Rachel" <Rachel@discussions.microsoft.com> wrote in message
>> news:6DA588B6-D353-4EB1-9F7D-FEC97F9059CE@microsoft.com...
>> >I want to get the vlookup results to a variable, where that table data
>> >is
>> >in
>> > book2
>> > and the key search is in book1 somthing like
>> > Vlook = application.worksheet.vlookup(book1!sheet1[a1],
>> > book2!sheet1[table01], 2)
>> > this does not work. Both worbooks are in the same directory, and I use
>> > excel
>> > 2000
>> > and windoew xp
>> > thanks rachel
>>
>>
>>

[Dave Peterson]

I find using variables make it a bit simpler to debug.

dim LookUpTable as range
dim LookUpCell as Range
dim res as variant

set lookupTable = workbooks("book2.xls").worksheets("sheet1").range("table01")
set lookupCell = workbooks("book1.xls").worksheets("sheet1").range("a1")

res = application.vlookup(lookupcell.value, lookuprng, 2)
'or for an exact match:
res = application.vlookup(lookupcell.value, lookuprng, 2, false)

if iserror(res) then
'error was returned, what happens?
else
msgbox res
end if

===
And by using application.vlookup() instead of
application.worksheetfunction.vlookup(), I can test the result to see if it
returned an error.

And I think it's always a good idea to include the extension of the file
(assuming that book1 and book2 have both been saved).




Rachel wrote:
>
> I want to get the vlookup results to a variable, where that table data is in
> book2
> and the key search is in book1 somthing like
> Vlook = application.worksheet.vlookup(book1!sheet1[a1],
> book2!sheet1[table01], 2)
> this does not work. Both worbooks are in the same directory, and I use excel
> 2000
> and windoew xp
> thanks rachel

--

Dave Peterson

[Rachel]

Hi everybody, thanks for the cooperation.
I now have some progress, the error value is 2042,
on the line begins with LUResults = (see below)
but it does not afect the flow of the macro.
Except that later on I have the line
MsgBox LUResults there the macro stucks and the error note is
type mismatch error no 13
I have excel2000 and windows xp
The entire macro is
Sub VLU()
Dim Taarich As Date ' Taarich is Date in Hebrew
Dim LUResults As Variant
Dim LupTable As Range
Set LupTable = Workbooks("book2.xls").Sheets("sheet1").Range("Table01")
Taarich = ActiveCell.Offset(0, -1).Value
LUResults = Application.VLookup(Taarich, LupTable, 3, True)
MsgBox LUResults
End Sub

Any mor help, please.........
rachel
"Niek Otten" wrote:

> Hi Rachel,
>
> Worked OK for me in Excel 2003
>
> Sorry!
>
> --
> Kind regards,
>
> Niek Otten
>
> "Rachel" <Rachel@discussions.microsoft.com> wrote in message
> news:94ACE6D4-E80B-47E0-B211-6F17B5EB067D@microsoft.com...
> > Dear Niek,
> > this does not work, I get this error result that does not really help
> > "Object does not support this property or methos"
> > can you figuer it out?
> > Thanks in advance
> > rachel
> >
> > "Niek Otten" wrote:
> >
> >> Hi Rachel,
> >>
> >> application.WorksheetFunction.VLookup(workbooks("book1").worksheets("sheet1").range("a1"),workbooks("book2").worksheets("sheet1").range("table01"),2)
> >>
> >> --
> >> Kind regards,
> >>
> >> Niek Otten
> >>
> >> "Rachel" <Rachel@discussions.microsoft.com> wrote in message
> >> news:6DA588B6-D353-4EB1-9F7D-FEC97F9059CE@microsoft.com...
> >> >I want to get the vlookup results to a variable, where that table data
> >> >is
> >> >in
> >> > book2
> >> > and the key search is in book1 somthing like
> >> > Vlook = application.worksheet.vlookup(book1!sheet1[a1],
> >> > book2!sheet1[table01], 2)
> >> > this does not work. Both worbooks are in the same directory, and I use
> >> > excel
> >> > 2000
> >> > and windoew xp
> >> > thanks rachel
> >>
> >>
> >>
>
>
>

[Dave Peterson]

Check to see if the =vlookup() returns an error...

if iserror(luresults) then
msgbox "not found!
else
msgbox Luresults
end if



Rachel wrote:
>
> Hi everybody, thanks for the cooperation.
> I now have some progress, the error value is 2042,
> on the line begins with LUResults = (see below)
> but it does not afect the flow of the macro.
> Except that later on I have the line
> MsgBox LUResults there the macro stucks and the error note is
> type mismatch error no 13
> I have excel2000 and windows xp
> The entire macro is
> Sub VLU()
> Dim Taarich As Date ' Taarich is Date in Hebrew
> Dim LUResults As Variant
> Dim LupTable As Range
> Set LupTable = Workbooks("book2.xls").Sheets("sheet1").Range("Table01")
> Taarich = ActiveCell.Offset(0, -1).Value
> LUResults = Application.VLookup(Taarich, LupTable, 3, True)
> MsgBox LUResults
> End Sub
>
> Any mor help, please.........
> rachel
> "Niek Otten" wrote:
>
> > Hi Rachel,
> >
> > Worked OK for me in Excel 2003
> >
> > Sorry!
> >
> > --
> > Kind regards,
> >
> > Niek Otten
> >
> > "Rachel" <Rachel@discussions.microsoft.com> wrote in message
> > news:94ACE6D4-E80B-47E0-B211-6F17B5EB067D@microsoft.com...
> > > Dear Niek,
> > > this does not work, I get this error result that does not really help
> > > "Object does not support this property or methos"
> > > can you figuer it out?
> > > Thanks in advance
> > > rachel
> > >
> > > "Niek Otten" wrote:
> > >
> > >> Hi Rachel,
> > >>
> > >> application.WorksheetFunction.VLookup(workbooks("book1").worksheets("sheet1").range("a1"),workbooks("book2").worksheets("sheet1").range("table01"),2)
> > >>
> > >> --
> > >> Kind regards,
> > >>
> > >> Niek Otten
> > >>
> > >> "Rachel" <Rachel@discussions.microsoft.com> wrote in message
> > >> news:6DA588B6-D353-4EB1-9F7D-FEC97F9059CE@microsoft.com...
> > >> >I want to get the vlookup results to a variable, where that table data
> > >> >is
> > >> >in
> > >> > book2
> > >> > and the key search is in book1 somthing like
> > >> > Vlook = application.worksheet.vlookup(book1!sheet1[a1],
> > >> > book2!sheet1[table01], 2)
> > >> > this does not work. Both worbooks are in the same directory, and I use
> > >> > excel
> > >> > 2000
> > >> > and windoew xp
> > >> > thanks rachel
> > >>
> > >>
> > >>
> >
> >
> >

--

Dave Peterson

[Tom Ogilvy]

Sub VLU()
Dim Taarich As Date ' Taarich is Date in Hebrew
Dim LUResults As Variant
Dim LupTable As Range
Set LupTable = Workbooks("book2.xls").Sheets("sheet1").Range("Table01")
Taarich = ActiveCell.Offset(0, -1).Value
LUResults = Application.VLookup(Taarich, LupTable, 3, True)
if iserror(LUResults) then
msgbox Taarich & " was not found"
else
MsgBox LUResults
end if
End Sub

2042 is equivalent to #N/A returned in the worksheet

--
Regards,
Tom Ogilvy

"Rachel" <Rachel@discussions.microsoft.com> wrote in message
news:77AB29D4-A55F-48C8-9C7D-3A67CEDDFFA8@microsoft.com...
> Hi everybody, thanks for the cooperation.
> I now have some progress, the error value is 2042,
> on the line begins with LUResults = (see below)
> but it does not afect the flow of the macro.
> Except that later on I have the line
> MsgBox LUResults there the macro stucks and the error note is
> type mismatch error no 13
> I have excel2000 and windows xp
> The entire macro is
> Sub VLU()
> Dim Taarich As Date ' Taarich is Date in Hebrew
> Dim LUResults As Variant
> Dim LupTable As Range
> Set LupTable =
Workbooks("book2.xls").Sheets("sheet1").Range("Table01")
> Taarich = ActiveCell.Offset(0, -1).Value
> LUResults = Application.VLookup(Taarich, LupTable, 3, True)
> MsgBox LUResults
> End Sub
>
> Any mor help, please.........
> rachel
> "Niek Otten" wrote:
>
> > Hi Rachel,
> >
> > Worked OK for me in Excel 2003
> >
> > Sorry!
> >
> > --
> > Kind regards,
> >
> > Niek Otten
> >
> > "Rachel" <Rachel@discussions.microsoft.com> wrote in message
> > news:94ACE6D4-E80B-47E0-B211-6F17B5EB067D@microsoft.com...
> > > Dear Niek,
> > > this does not work, I get this error result that does not really help
> > > "Object does not support this property or methos"
> > > can you figuer it out?
> > > Thanks in advance
> > > rachel
> > >
> > > "Niek Otten" wrote:
> > >
> > >> Hi Rachel,
> > >>
> > >>
application.WorksheetFunction.VLookup(workbooks("book1").worksheets("sheet1"
).range("a1"),workbooks("book2").worksheets("sheet1").range("table01"),2)
> > >>
> > >> --
> > >> Kind regards,
> > >>
> > >> Niek Otten
> > >>
> > >> "Rachel" <Rachel@discussions.microsoft.com> wrote in message
> > >> news:6DA588B6-D353-4EB1-9F7D-FEC97F9059CE@microsoft.com...
> > >> >I want to get the vlookup results to a variable, where that table
data
> > >> >is
> > >> >in
> > >> > book2
> > >> > and the key search is in book1 somthing like
> > >> > Vlook = application.worksheet.vlookup(book1!sheet1[a1],
> > >> > book2!sheet1[table01], 2)
> > >> > this does not work. Both worbooks are in the same directory, and I
use
> > >> > excel
> > >> > 2000
> > >> > and windoew xp
> > >> > thanks rachel
> > >>
> > >>
> > >>
> >
> >
> >

[Rachel]

Hi everybody, thanks for trying to help
What I may not put clear is, that for testing my vba procedure
I chose a date that is in the table01 col A
and still get an error no 2042
while writing the function interactively
=VLOOKUP(A5,Book2.xls!Table01,3,FALSE)
I get the expected results
Any Idea?
Thanks rachel

"Dave Peterson" wrote:

> I find using variables make it a bit simpler to debug.
>
> dim LookUpTable as range
> dim LookUpCell as Range
> dim res as variant
>
> set lookupTable = workbooks("book2.xls").worksheets("sheet1").range("table01")
> set lookupCell = workbooks("book1.xls").worksheets("sheet1").range("a1")
>
> res = application.vlookup(lookupcell.value, lookuprng, 2)
> 'or for an exact match:
> res = application.vlookup(lookupcell.value, lookuprng, 2, false)
>
> if iserror(res) then
> 'error was returned, what happens?
> else
> msgbox res
> end if
>
> ===
> And by using application.vlookup() instead of
> application.worksheetfunction.vlookup(), I can test the result to see if it
> returned an error.
>
> And I think it's always a good idea to include the extension of the file
> (assuming that book1 and book2 have both been saved).
>
>
>
>
> Rachel wrote:
> >
> > I want to get the vlookup results to a variable, where that table data is in
> > book2
> > and the key search is in book1 somthing like
> > Vlook = application.worksheet.vlookup(book1!sheet1[a1],
> > book2!sheet1[table01], 2)
> > this does not work. Both worbooks are in the same directory, and I use excel
> > 2000
> > and windoew xp
> > thanks rachel
>
> --
>
> Dave Peterson
>

[Tom Ogilvy]

convert your date to a long in the vlookup. This usually works better:

Sub VLU()
Dim Taarich As Date ' Taarich is Date in Hebrew
Dim LUResults As Variant
Dim LupTable As Range
Set LupTable = Workbooks("book2.xls").Sheets("sheet1").Range("Table01")
Taarich = ActiveCell.Offset(0, -1).Value
LUResults = Application.VLookup(clng(Taarich), LupTable, 3, True)
if iserror(LUResults) then
msgbox Taarich & " was not found"
else
MsgBox LUResults
end if
End Sub

--
Regards,
Tom Ogilvy


"Rachel" <Rachel@discussions.microsoft.com> wrote in message
news:278D1109-E4E9-4E3C-9777-F6C2579F374E@microsoft.com...
> Hi everybody, thanks for trying to help
> What I may not put clear is, that for testing my vba procedure
> I chose a date that is in the table01 col A
> and still get an error no 2042
> while writing the function interactively
> =VLOOKUP(A5,Book2.xls!Table01,3,FALSE)
> I get the expected results
> Any Idea?
> Thanks rachel
>
> "Dave Peterson" wrote:
>
> > I find using variables make it a bit simpler to debug.
> >
> > dim LookUpTable as range
> > dim LookUpCell as Range
> > dim res as variant
> >
> > set lookupTable =
workbooks("book2.xls").worksheets("sheet1").range("table01")
> > set lookupCell = workbooks("book1.xls").worksheets("sheet1").range("a1")
> >
> > res = application.vlookup(lookupcell.value, lookuprng, 2)
> > 'or for an exact match:
> > res = application.vlookup(lookupcell.value, lookuprng, 2, false)
> >
> > if iserror(res) then
> > 'error was returned, what happens?
> > else
> > msgbox res
> > end if
> >
> > ===
> > And by using application.vlookup() instead of
> > application.worksheetfunction.vlookup(), I can test the result to see if
it
> > returned an error.
> >
> > And I think it's always a good idea to include the extension of the file
> > (assuming that book1 and book2 have both been saved).
> >
> >
> >
> >
> > Rachel wrote:
> > >
> > > I want to get the vlookup results to a variable, where that table data
is in
> > > book2
> > > and the key search is in book1 somthing like
> > > Vlook = application.worksheet.vlookup(book1!sheet1[a1],
> > > book2!sheet1[table01], 2)
> > > this does not work. Both worbooks are in the same directory, and I use
excel
> > > 2000
> > > and windoew xp
> > > thanks rachel
> >
> > --
> >
> > Dave Peterson
> >