none
Using the MATCH function in Excel VBA RRS feed

  • Question

  • According to the online documentation for Excel 2010, the Excel MATCH function can be used from VBA as a member of the WorksheetFunction object.

    However, when I write     x = WorksheetFunction.Match(0, Range(Cells(1, 1), Cells(10, 1)), 0)   my VBA sub stops with the message "Unable to get the Match property of the WorksheetFunction class".

    Is the documentation wrong or did I do something wrong?

    For what it's worth, I typed "MATCH" and the VBA editor changed it to "Match".

    Thank,

    Norm


    Norm

    Wednesday, November 28, 2012 9:52 PM

Answers

  • that error just means that a match wasn't found.

    Sub match_function()
        On Error Resume Next
        x = WorksheetFunction.Match(0, Range(Cells(1, 1), Cells(10, 1)), 0)
        If x = "" Then
            MsgBox "No matching value found"
        End If
    End Sub
    

    • Proposed as answer by bnasu3 Wednesday, November 28, 2012 10:11 PM
    • Marked as answer by Norm Bullen Wednesday, December 5, 2012 12:36 AM
    Wednesday, November 28, 2012 10:10 PM