You are using a worksheet function: Vlookup.
You can't use a VBA variable in a worksheet function, you can only use
(from XL help) a value, a reference, or a text string.
In your first statement you are passing the reference
Range("Retire_Date_Primary").
In your second statement you are passing the variable retireDate and a
variable is not a value, reference, or text string.

Bill Buckner wrote:

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