VLookup function in VBA problem?
 

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

Try declaring retireDate as type Long (or Integer) rather than type Date.