Problem with Match

[stemar]

Column A contains a list of dates, starting at row 21. I don't know without looking how many there will be, but it won't be more than a few hundred.

lRow = WorksheetFunction.Match(dDate, .Range("A21:A1000"), 0)

is supposed to give me the row of dDate if it exists in the column or an error if it isn't there, but it's setting lRow to 0

Can anyone tell me what's going on and how to fix it, please? If not it's back to a Do ... Loop, and Match is so much quicker.

Thanks in advance

 

[wideboydixon]

I suspect that Excel stores dates as numbers but the data type of dDate (assuming Hungarian notation) is date. Try wrapping dDate in a CLng:

lRow = WorksheetFunction.Match(CLng(dDate), .Range("A21:A1000"), 0)

WBD

 

[stemar]

Illogical, but it works.

Thank you