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

  • 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 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)


    Code
    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)
  • Dates and match are always entertaining. Try using:


    Code
    i = Application.Match(CLng(d), Range("wrng2").Value2, -1)


    I'm assuming you know your initial d date is 4th Aug 2019.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • Administrative Note:


    Welcome to the forum. :)


    We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.


    (Note: this requirement is not optional. No help to be offered until the link is provided.)

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

  • We have been advised that you have posted this question on TWO other fora - you have provided only one link. Please provide a link to the other cross-post. Thanks.

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

  • Apologies again. I cannot recall the other forum.


    (When one does not get any responses on a forum, it seems to make sense to post it in another forum. Now that I know I need to inform each forum when this is done, I will.)

  • It was Mr Excel - please provide the link.

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

  • On a tangent, what does i = Evaluate("=MATCH(" & CStr(CDbl(d)) & ",wrng2,-1)") set i to?


    Actually, that's a bit dangerous as the Evaluate call could return #NA, so maybe better to use a variable of type Variant to hold the return value, then check whether it's an error value using IsError().

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!