Error using MATCH with dates in variables
 

If I match a named range ("Criteria_12") I get the correct result but if I
assign the cell value to a variable I get an #N/A error. It makes no
difference if I declare (DIM) the variables dt or curr_date.

Why?

TIA



dt = Range("Criteria_12")
curr_date = DateSerial(Year(dt), Month(dt), Day(dt))

' This works OK .......

Range("A5") = Application.Match(Range("Criteria_12"), Range("1:1"), 0)

' These return error conditions ....WHY?

Range("A6") = Application.Match(dt, Range("1:1"), 0)
Range("A7") = Application.Match(curr_date, Range("1:1"), 0)

Error using MATCH with dates in variables
 

Solved it!

Range("A6") = Application.Match(Clng(dt), Range("1:1"), 0)

"Toppers" wrote:


If I match a named range ("Criteria_12") I get the correct result but if I
assign the cell value to a variable I get an #N/A error. It makes no
difference if I declare (DIM) the variables dt or curr_date.

Why?

TIA



dt = Range("Criteria_12")
curr_date = DateSerial(Year(dt), Month(dt), Day(dt))

' This works OK .......

Range("A5") = Application.Match(Range("Criteria_12"), Range("1:1"), 0)

' These return error conditions ....WHY?

Range("A6") = Application.Match(dt, Range("1:1"), 0)
Range("A7") = Application.Match(curr_date, Range("1:1"), 0)

Error using MATCH with dates in variables
 

What does the "CLng" do?


"Toppers" wrote in message
...
Solved it!

Range("A6") = Application.Match(Clng(dt), Range("1:1"), 0)

"Toppers" wrote:


If I match a named range ("Criteria_12") I get the correct result but if
I
assign the cell value to a variable I get an #N/A error. It makes no
difference if I declare (DIM) the variables dt or curr_date.

Why?

TIA



dt = Range("Criteria_12")
curr_date = DateSerial(Year(dt), Month(dt), Day(dt))

' This works OK .......

Range("A5") = Application.Match(Range("Criteria_12"), Range("1:1"),
0)

' These return error conditions ....WHY?

Range("A6") = Application.Match(dt, Range("1:1"), 0)
Range("A7") = Application.Match(curr_date, Range("1:1"), 0)

Error using MATCH with dates in variables
 

Converts date to Long (Integer) which is how the dates are held (as you
know!) so the MATCH now works..

"PCLIVE" wrote:

What does the "CLng" do?


"Toppers" wrote in message
...
Solved it!

Range("A6") = Application.Match(Clng(dt), Range("1:1"), 0)

"Toppers" wrote:


If I match a named range ("Criteria_12") I get the correct result but if
I
assign the cell value to a variable I get an #N/A error. It makes no
difference if I declare (DIM) the variables dt or curr_date.

Why?

TIA



dt = Range("Criteria_12")
curr_date = DateSerial(Year(dt), Month(dt), Day(dt))

' This works OK .......

Range("A5") = Application.Match(Range("Criteria_12"), Range("1:1"),
0)

' These return error conditions ....WHY?

Range("A6") = Application.Match(dt, Range("1:1"), 0)
Range("A7") = Application.Match(curr_date, Range("1:1"), 0)

Error using MATCH with dates in variables
 

Thanks. While playing around with your code (before you solved it, of
course), I noticed that I could set the variable to equal the value of the
date, as in:
=VALUE(TODAY())
In today's date, this gave me 39232. Using that set to a variable instead
of the "dt" variable allowed the match function to match properly. So I
assumed that must be the issue, but I didn't know how to correct it. Thanks
for the info. Another something new learned today.

Regards,
Paul


"Toppers" wrote in message
...
Converts date to Long (Integer) which is how the dates are held (as you
know!) so the MATCH now works..

"PCLIVE" wrote:

What does the "CLng" do?


"Toppers" wrote in message
...
Solved it!

Range("A6") = Application.Match(Clng(dt), Range("1:1"), 0)

"Toppers" wrote:


If I match a named range ("Criteria_12") I get the correct result but
if
I
assign the cell value to a variable I get an #N/A error. It makes no
difference if I declare (DIM) the variables dt or curr_date.

Why?

TIA



dt = Range("Criteria_12")
curr_date = DateSerial(Year(dt), Month(dt), Day(dt))

' This works OK .......

Range("A5") = Application.Match(Range("Criteria_12"),
Range("1:1"),
0)

' These return error conditions ....WHY?

Range("A6") = Application.Match(dt, Range("1:1"), 0)
Range("A7") = Application.Match(curr_date, Range("1:1"), 0)