Application.Match yielding "Type Mismatch"

[wadevcamp]

The following code results in a 'type mismatch" when trying to calculate 'i' in both cases. The range wrng2 is depicted below, and the values are Date type.

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)

Any help would be much appreciated.

 

[QuietRiot]

Application.WorksheetFunction property (Excel)

Office VBA reference topic

docs.microsoft.com

 

[DanteAmor]

Change the declaration i as variant.

Dim i As Variant

Because that can receive a value or an error if it does not exist.

 

[wadevcamp]

The following code results in a 'type mismatch" when trying to calculate 'i' in both cases. The range wrng2 is depicted below, and the values are Date type.

View attachment 1942

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)

Any help would be much appreciated.

Application.WorksheetFunction property (Excel)

Office VBA reference topic

docs.microsoft.com

I

Change the declaration i as variant.

Dim i As Variant

Because that can receive a value or an error if it does not exist.

Ok, but the value DOES exist. It works if I change the last MATCH argument to '0', and if I make the wrng2 into an ascending range and use a match type of '0' or '1'. The only case that does not work is when the range is descending and the match type is '-1'.

 

[DanteAmor]

As you commented, the range must have the dates descending for type -1 to work

Check the following, in the code I put some comments.

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)
End Sub

 

[wadevcamp]

Thanks. Have not tried it yet, but, assuming it solves the problem, can you explain why?

 

[wadevcamp]

As you commented, the range must have the dates descending for type -1 to work

Check the following, in the code I put some comments.

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)
End Sub

Still results in a Type Mismatch (Date type and Double type appear to both be 8 byte floating point values.)

 

[wadevcamp]

Still results in a Type Mismatch (Date type and Double type appear to both be 8 byte floating point values.)

...and the values in the range ARE in descending order.

 

[DanteAmor]

Excel stores a date as an integer.

On the screen you can see the date with a format, but to perform searches you must use the integer number of the date and not the formatted date.

 

[wadevcamp]

Excel stores a date as an integer.

On the screen you can see the date with a format, but to perform searches you must use the integer number of the date and not the formatted date.

your corrected code still results in 'type mismatch'