Bill:

I agree. It doesn't make complete sense to me either. Look at the
thread a little further down regarding: Can't Find Value.
It seems like the same problem involving passing values between VB and
XL using variables. Something in the object model seems inconsistent to
me, but it is more likely that I don't understand it as much as I would
like to.


Tushar's solution seems to be a good workaround to convert the variable
to a value by applying the type conversion function to the variable.

Bill Buckner wrote:
JWolf,

That makes sense except in the same worksheet function call I use another variable "insurance" which is defined in the following code. So if your response is the reason I keep getting "type" errors, why does it work for some and not for others? Could the fact that one is a date and one is an integer have anything to do with my problem? Maybe the fact that I do some calculations on the variable "insurance" before it is sent to the worksheet function causes some type conversions to take place???

Dim insurance As Integer
Dim retireDate As Date
insurance = Range("Insurance_Selection")
retireDate = CDate(Range("Retire_Date_Primary").Value)

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