Excel VBA Worksheetfunction.Match doesn't work with Dates

Question

I need to apply MATCH function to a range of dates (in VBA. The standard MATCH function written as cell formula does work as intended.) looking for the largest possible index whose corresponding date is <= the looked-up date. Here's my code and a minimum working example:

Function test_debug_func(x As Date, arr As Range) As Integer
Debug.Print "entered function"
test_debug_func = WorksheetFunction.Match(x, arr, 1)

End Function

And I checked that the function is indeed entered by printing the debug statement. However, it cannot proceed with the WorksheetFunction.Match function. I also checked that all dates are indeed Date and not other formats.

It simply doesn't make any sense to me why this wouldn't work. Could anyone kindly help? Thanks!

BigBen · Accepted Answer · 2020-09-14 15:41:13Z

3

Since dates are just numbers, you can use CLng:

test_debug_func = WorksheetFunction.Match(CLng(x), arr, 1)

answered Sep 14, 2020 at 15:41

BigBen

50.4k7 gold badges29 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Vim Over a year ago

Wow, thanks it works! But it's strange that I also tried Cint and it didn't work. Also, I would expect Excel to be smart enough to implicitly convert Date to Integer when comparing them, but it didn't...

BigBen Over a year ago

The maximum value of an Integer is 32767... your dates correspond to larger numbers, e.g. 2020/1/1 is 43831, so you need CLng instead of CInt.

Collectives™ on Stack Overflow

Excel VBA Worksheetfunction.Match doesn't work with Dates

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related