VBA Vlookup help!

[Billy C]

I have a user form that obtains details of a vehicle hire, in particular the date of hire, i then post this date back to a cell in a worksheet and then perform a vlookup to populate the next cell looking up the date from a table located in a different sheet within the same workbookin the code i have used is :- CurrDate = ActiveCell.Offset(0, 18).Value
ActiveCell.Offset(0, 13).Value = Application.WorksheetFunction.VLookup(CurrDate, RefTables!("B:C"), 2, False)
RefTables is the name of the sheet. When i run the code I get the error "Function not defined" can anyone please explain

 

[Fausto]

Hello Billy C

Try editing the macro as follows:

ActiveCell.Offset(0, 13).Value = Application.WorksheetFunction.VLookup(CurrDate, Sheets("RefTables").range("B:C"), 2, False)

I hope this helps

 

[Billy C]

Thanks but i am getting an error
"Unable to get the Vlookup property of the worksheet function class"

 

[Fausto]

Hello

Wierd, but this error only occurs when working with dates. Please try the following and see if it serves your purpose:

Dim currdate As Long
currdate = DateValue(ActiveCell.Offset(0, 18).Value)
ActiveCell.Offset(0, 13) = Application.WorksheetFunction.VLookup(currdate, Sheets("RefTables").Range("B:C"), 2, False)

I managed to replicate your error message when I was using the dates. Once I changed the variable declaration on the currdate variable (plus using the datevalue function), it works. Obviously the date is read in as a number and then looked up. Dont know why VBA vlookup doesnt like dates (maybe some of the PRO's can answer), however it looks like my solution is a decent work around.

Hope this helps

 

[Billy C]

You are a STAR!!!
Thanks very much