vlookup table in another workbook

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Hi Rachel,

application.WorksheetFunction.VLookup(workbooks("book1").worksheets("sheet1").range("a1"),workbooks("book2").worksheets("sheet1").range("table01"),2)
 
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 said:
Hi Rachel,

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

--
Kind regards,

Niek Otten

Rachel said:
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
Click to expand...
Click to expand...
 
Hi Rachel,

Worked OK for me in Excel 2003

Sorry!

--
Kind regards,

Niek Otten

Rachel said:
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 said:
Hi Rachel,

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

--
Kind regards,

Niek Otten

Rachel said:
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
Click to expand...
Click to expand...
Click to expand...
 
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).



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
Click to expand...
 
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 said:
Hi Rachel,

Worked OK for me in Excel 2003

Sorry!

--
Kind regards,

Niek Otten

Rachel said:
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 said:
Hi Rachel,

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

--
Kind regards,

Niek Otten

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
Click to expand...
Click to expand...
Click to expand...
 
Check to see if the =vlookup() returns an error...

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


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 said:
Hi Rachel,

Worked OK for me in Excel 2003

Sorry!

--
Kind regards,

Niek Otten

Rachel said:
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

:

Hi Rachel,

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

--
Kind regards,

Niek Otten

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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
Hi Rachel,

Worked OK for me in Excel 2003

Sorry!
Click to expand...
application.WorksheetFunction.VLookup(workbooks("book1").worksheets("sheet1"
).range("a1"),workbooks("book2").worksheets("sheet1").range("table01"),2)
--
Kind regards,

Niek Otten

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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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).



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
Click to expand...
Click to expand...
 
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 said:
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 said:
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).



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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

vlookup in vba 2
xlfTextRef from another workbook 1
How to Refer to Another Excel Workbook 1
Calling excel macro not located in that workbook. 2
WORKING WITH COLORINDEX AND MULTIPLE WORKBOOKS 1
How to Copy from One File to Another File 4
help with a basic code? 0
Subject out of range 3

Back
Top