Vba using Match with a date value

[MrBD]

I have a sheet with sequential dates in row 3, columns A to BJ. I've tried to match a date but I can only get it work via a helper cell.

Can anyone explain why in the vba

Range("B6").Value = DateValue("1/9/22")
Range("G9").Value = Application.WorksheetFunction.Match(Range("B6"), Range("A3:BJ3"), 0)

works perfectly, but

Range("G6").Value = Application.WorksheetFunction.Match(DateValue("1/9/22"), Range("A3:BJ3"), 0)

fails with a 1004 "Unable to match property..."

 

[RoryA]

Try converting to a number value:

Range("G6").Value = Application.WorksheetFunction.Match(CLng(DateValue("1/9/22")), Range("A3:BJ3"), 0)

 

[Akuini]

Deleted.

 

[MrBD]

RoryA - you're a star - it worked perfectly! I tried everything I could think of but not the Clng. Many thanks.