Application.Match gives 'Type Mismatch' only on descending date values, match type=-1

[WADEVCAMP]

In all attempts to assign 'i' the value in the code below, a type mismatch error occurs. If I change the match type to '0', it works as expected. If I resort the range wrng2 to be ascending values and use a match type of either 0 or 1, it works as expected. Only when using a match type of -1 with a descending range of values does the error occur. Help! (wrng2 is depicted in the attachment below)


Sub tyu()Dim i As Integer
Dim d As Date
d = #8/4/2019#
i = Application.Match(d, Range("wrng2").Value, -1)
d = Range("wrng2").Cells(7, 1).Value
i = Application.Match(d, Range("wrng2").Value, -1)

i = WorksheetFunction.IfNa( _
Application.Match(d, Range("wrng2").Value, -1), _
0)

d = WorksheetFunction.Index(Range("wrng2"), 6)
End Sub
Sub test()
Dim i As Variant, d As Date
d = #8/24/2019# 'mm/dd/yyy
'with Int(CDbl(d)) We get the excel number for a date
i = Application.Match(Int(CDbl(d)), Range("wrng2"), -1)
MsgBox i

End Sub

25629

[Jan Karel Pieterse]

Sounds like a bug to me!

[Jan Karel Pieterse]

I've asked around and according to Charles Williams (https://decisionmodels.com) this is due to the fact that the WorksheetFunction class does not correctly understand a couple of data types, including dates. To make it work, use the Value2 property, like so:

Sub MatchErrorDemo()
Dim i As Integer
Dim d As Variant
d = Range("D2").Value2
'Next line causes a Type mismatch error, whereas cell D3 yields the expected result
i = Application.Match(d, Range("wrng2").Value2, -1)
End Sub

[WADEVCAMP]

Thanks Jan! Value2 was the missing link!