Match Failing

[Ark68]

Can anyone see any problems with this line of code which would contribute to an "Unable to get the match property of the Worksheet Function class" error.

With ws_master
    dRow = Application.WorksheetFunction.Match(n_date, .Columns(1), 0)
End With

n_date = 2020-06-04

In worksheet ws_master (defined publically as worksheet), 2020-06-04 is found in call A172, yet this code is unable to find that match.

 

[teylyn]

date stored as text? Is the date a string or a date? What happens if you write the formula into a worksheet cell for testing?

 

[Ark68]

Hi Teylyn ... I believe it is stored as a date. If I change the cells in column A of ws_roster to number, the value changes to the correct serial for that date.
At the worksheet level, =match(HG2,A:A) where HG2 = 2020-06-04 returnas a value of 172, which would be correct.

 

[Ark68]

It would appear that n_date isn't a number (isnumeric(n_date) = false). That may be the problem? Will have to find out how to convert the n_date (string?) into a number.
n_date_ser = CDate (n_date) didn't work to convert the string 2020-06-04 to a number.

 

[Ark68]

Also tried this approach where n_date = 2020-06-04 (observations in green). I suspect maybe I was using IsNumeric wrong thinking that date should be a number.

n_date_ser = DateValue(n_date)  '2020-06-4 
n_date_ser = IsNumeric(n_date)  'FALSE
n_date_ser = DateSerial(Left(n_date, 4), Mid(n_date, 6, 2), Right(n_date, 2))  '2020-06-4
n_date_fmt = format(n_date_ser,"00000")  ' "43987"
n_date_num = IsNumeric(n_date_ser) 'FALSE
dRow = Application.WorksheetFunction.Match(n_date_ser, ws_roster.Columns(1), 0) 'Unable to get the Match property ...

 

[RoryA]

How is n_date declared? If it's a string, use:

Application.WorksheetFunction.Match(CDbl(CDate(n_date)), ws_roster.Columns(1), 0)

 

[Ark68]

Hi Rory, I was working on a post as you were answering. I will give your suggestion a try, but n_date is declared as Date.

Edit: Did not work. Same error.

 

[RoryA]

If n_date is declared as a date, it would just be:

Application.WorksheetFunction.Match(CDbl(n_date), ws_roster.Columns(1), 0)

If that doesn't work, it's most likely an issue either with your data (eg column A has time parts, or is stored as text) or with how you are populating the date variable.

 

[Ark68]

Thank you Rory. You got me looking through my code more closely and noticed that although I had n_date declared publicly, I also had it locally dimmed in my module as long. I removed it and things improved. Not sure why, but it did.