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

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 Intege
Dim retireDate As Dat
insurance = Range("Insurance_Selection"
retireDate = CDate(Range("Retire_Date_Primary").Value

Application.VLookup(Range("Retire_Date_Primary"), Range("Pension_Table"), 21 + insurance - 1) '<= This work
Application.VLookup(retireDate , Range("Pension_Table"), 21 + insurance - 1) '<= This fail

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

Bill:
It's not what I thought. It's your dim statement. Change to Dim
retireDate As Single and it works fine without the cDbl function. It
seems that although there is a date data type in VBA, there is none in
XL, only date data formats. The Vlookup is failing because XL is only
working on dates as floating point data.

JWolf wrote:

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

I would disagree with the categorization of my solution as a
workaround. I suspect that the belief that an explicit datatype
coercion is a workaround stems from the all the implicit type
conversions that both XL and VBA engage in -- so much so that when the
type conversion doesn't happen automatically, people get stuck.

Typically, one should recognize the fact that different functions work
with different datatypes. In those cases where the developer knows how
to coerce one data type into something else that is acceptable to a
function, it should be the responsibility of the developer to do so.

IMO, the software has no business doing implicit conversions.

In the OP's problem, we had a variable of the Date datatype, and a
function that required a double (or a string) for its argument. Since
we know that XL's dates are stored as numbers (i.e., XL has no date
datatype), it was safe to coerce the date into a double.

One of the benefits of VB.Net is the ability to use a 'Option Strict'
clause. This will require the developer to explicitly request every
datatype-to-datatype conversion; none will be automatic.

--
Regards,

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

In article ,
says...
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