vba 'Match' function with dates

[BigLRIP]

Hi,

I'm trying to use the Match function to take a specific date and find it within a row of cells. However, dates are giving me problems as the match function seems to be functioning poorly.

...
Sheets("Sheet1").Select
date = Cells(row_index, 5).Text
lookup_range = Sheets("Sheet2").Range("2:2")
date_col_index = Application.Match(date, lookup_range, 0)
...

date_col_index returns 'Error 2042'

My dates are in the format of mmm-yy.
I was thinking about casting my date as a long, but the lookup_range would still be in normal date format. Any ideas around this?

 

[DonkeyOte]

Don't use Date as a variable name - Date is the equivalent of Today() in VBA.

Once the above is corrected I would cast the variable to Long in the Match as you suggest.

 

[BigLRIP]

Don't use Date as a variable name - Date is the equivalent of Today() in VBA.

Once the above is corrected I would cast the variable to Long in the Match as you suggest.

I'm actually not using date as a variable name, just changed it for this forum.

Dim quarter As String
quarter = Sheets("Sheet1").Cells(row_index, 5).Text
quarter = CDate(quarter)
quarter = CLng(quarter)

Final line keeps giving me a <'type mismatch> error

 

[DonkeyOte]

...quarter is declared as a string.

...accepting the above fact and adapting your earlier pseudo-code perhaps:

Dim quarter As String
quarter = Sheets("Sheet1").Cells(row_index, 5).Text
Set lookup_range = Sheets("Sheet2").Range("2:2")
date_col_index = Application.Match(CLng(CDate(quarter)), lookup_range, 0)
Set lookup_range = Nothing

It's unclear what date_col_index is declared as but it should probably be a Variant (test variable result IsNumeric to ensure valid Match has been found)

 

[BigLRIP]

...quarter is declared as a string.

...accepting the above fact and adapting your earlier pseudo-code perhaps:

Dim quarter As String
quarter = Sheets("Sheet1").Cells(row_index, 5).Text
Set lookup_range = Sheets("Sheet2").Range("2:2")
date_col_index = Application.Match(CLng(CDate(quarter)), lookup_range, 0)
Set lookup_range = Nothing

It's unclear what date_col_index is declared as but it should probably be a Variant (test IsNumeric to ensure valid Match has been found)

Thanks Donkey I'll test it out.

I actually just changed

quarter = Sheets("Sheet1").Cells(row_index, 5).Text

to:

quarter = Sheets("Sheet1").Cells(row_index, 5).Value

Then did

Cdate(quarter)

and it seems to be working fine.

 

[DonkeyOte]

Thanks for posting your solution.