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.

 

[wadevcamp]

That much I understood already. It was the Variant piece and Int pieces that did it.

 

[DanteAmor]

I'm sorry, you had sent message #19 and I didn't see it.
I hope it works for what you need. Let me know any questions.

 

[Fluff]

@wadevcamp
For future reference
While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

Application.Match returns 'Type Mismatch' only on descending date values with match type = -1 - OzGrid Free Excel/VBA Help Forum

In both 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…

www.ozgrid.com

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

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...

www.vbaexpress.com

 

[wadevcamp]

Apologies for the breach of protocol re: cross-posting -- now I know.

The answers on another forum added that one must also use '.value2' as the property (instead of '.value') of the range being searched in the MATCH function -- THIS additional modification makes it work. Thanks to all -- too bad this issue isn't more widely understood (or documented!).

 

[DanteAmor]

Unfortunately, not all excel options are documentary, for that there are forums, to leave traces and examples of what you find on the long road to Excel.