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