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

Capture4.JPG

[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!