It is not possible to use a Date datatype as the VLookup value because
the function doesn't know what to do with a Date data type. XL knows
only three data types: number, text, and boolean. It implements a date
as a number. It is only through formatting (which, for the most part,
has no effect on the type of data, only on how it is displayed) that
distinguishes between a number and a date.

So, the following works:

Sub testIt2()
Dim retireDate As Date, insurance As Long
retireDate = Range("Retire_Date_Primary")
insurance = -18
MsgBox Application.VLookup(Range("Retire_Date_Primary"), _
Range("Pension_Table"), 21 + insurance - 1)
MsgBox Application.VLookup(CDbl(retireDate), _
Range("Pension_Table"), 21 + insurance - 1)
End Sub


--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
I am perplexed by a data type problem that occurs when I use a date to VLookup on a table. If I use a date directly from a spreadsheet via Range("Retire_Date_Primary") the lookup performs perfectly. However if I declare a variable As Date and the assign this worksheet range to this variable, the lookup fails with a type mismatch.

Dim retireDate As Date
retireDate = Range("Retire_Date_Primary")
Application.VLookup(Range("Retire_Date_Primary"), Range("Pension_Table"), 21 + insurance - 1) '<= This works
Application.VLookup(retireDate , Range("Pension_Table"), 21 + insurance - 1) '<= This fails

You are welcome. Glad to be of help.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Multi-disciplinary business expertise
+ Technology skills
= Optimal solution to your business problem
Recipient Microsoft MVP award 2000-2004

In article ,
says...
Tushar,

Thank you for the solution (and an excellent explaination).

Interesting to note that if I do the CDbl conversion in the assignment statement instead of the VLookup function call it still returns a type conversion. The conversion has to be done in the function call.

Thanks again.