vlookup table in another workbook
 

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

vlookup table in another workbook
 

Hi Rachel,

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

--
Kind regards,

Niek Otten

"Rachel" wrote in message
...
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

vlookup table in another workbook
 

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("b ook1").worksheets("sheet1").range("a1"),workbooks( "book2").worksheets("sheet1").range("table01") ,2)

--
Kind regards,

Niek Otten

"Rachel" wrote in message
...
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

vlookup table in another workbook
 

Hi Rachel,

Worked OK for me in Excel 2003

Sorry!

--
Kind regards,

Niek Otten

"Rachel" wrote in message
...
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("b ook1").worksheets("sheet1").range("a1"),workbooks( "book2").worksheets("sheet1").range("table01") ,2)

--
Kind regards,

Niek Otten

"Rachel" wrote in message
...
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

vlookup table in another workbook
 

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

vlookup table in another workbook
 

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("Tab le01")
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" wrote in message
...
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("b ook1").worksheets("sheet1").range("a1"),workbooks( "book2").worksheets("sheet1").range("table01") ,2)

--
Kind regards,

Niek Otten

"Rachel" wrote in message
...
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

vlookup table in another workbook
 

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("Tab le01")
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" wrote in message
...
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("b ook1").worksheets("sheet1").range("a1"),workbooks( "book2").worksheets("sheet1").range("table01") ,2)

--
Kind regards,

Niek Otten

"Rachel" wrote in message
...
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

vlookup table in another workbook
 

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("Tab le01")
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" wrote in message
...
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("Tab le01")
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" wrote in message
...
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("b ook1").worksheets("sheet1"
).range("a1"),workbooks("book2").worksheets("sheet 1").range("table01"),2)

--
Kind regards,

Niek Otten

"Rachel" wrote in message
...
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

vlookup table in another workbook
 

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

vlookup table in another workbook
 

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("Tab le01")
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" wrote in message
...
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