Run-time 1004: Unable to get the Match property of the WorksheetFunction class

  • Code
    1. NAV.Range("B" & n).Value = WorksheetFunction.IfError(WorksheetFunction.Index(Tminus1.Range("G3:G" & LR), (WorksheetFunction.Match(NAV.Range("R" & n), Tminus1.Range("H3:H" & LR), 0))), "NO DATA")


    n is an integer, LR is long, Tminus1 is a worksheet
    I am doing a vlookup using index and match. The formula works when its in the sheet and when I transferred it to vba, it worked until it got to a lookup value thats not in tminus sheet which I am looking up against and instead of inserting no data like i thought it would, I am running across an error: Run-time 1004: Unable to get the Match property of the WorksheetFunction class. I have tried a few different solutions but so far no luck. Any suggestion welcome.


    thanks in advance.

  • Re: Run-time 1004: Unable to get the Match property of the WorksheetFunction class


    Try...



    Hope this helps!

  • Re: Run-time 1004: Unable to get the Match property of the WorksheetFunction class


    Quote from Domenic;733895

    Try...



    Hope this helps!


    It worked :)..thank you so much. I tried your code with worksheetfunction.match instead of application.match and it was throwing up the same error above. Is there a reason why it would do that?. Stuck in your code as it is and its working beautifully. Thank you so much.

  • Re: Run-time 1004: Unable to get the Match property of the WorksheetFunction class


    When the Match method refers to the Application object instead of the WorksheetFunction object and no match is found, the result is a non-breaking error for which you can test using IsError.