vba - Match Not working Excel: Error 1004 Unable to get the Match Property

Question

Sub Sales_Summary_Macro()

    Dim strMake, strModel, strCount As String
    Dim makeLoc, modelLoc, countLoc As Integer

    strMake = Application.InputBox("Make")
    strModel = Application.InputBox("Model")
    strCount = Application.InputBox("Count")

    If strMake <> False Then
        Debug.Print strMake
        Debug.Print strModel
        Debug.Print strCount
        makeLoc = WorksheetFunction.Match(strMake, Range("A1:A10"), 0)
        Debug.Print makeLoc
    End If

End Sub

I just want to take the string input of the user on three different variables and find the column that contains each variable. I have tried Application.Match() and Match() alone and neither seem to work.

Answer 1

Not going full technical and will not post code. However, three things:

One, make sure your ranges are always fully qualified. For example, Range("A1:A10") is not nearly enough. You should specify on which sheet this should be located. If you are calling this macro from another sheet, it will give you a wrong result or throw an error.

Two, without going to too much details:

1. Application.Match returns an error value if there's no match found. This can be handled using IsError, which is what simoco did in his answer.
2. WorksheetFunction.Match throws a 1004 error when it doesn't find an error. This is not the same as returning a value. As such, this is (slightly) harder to handle.

Best practice is to always use the first one.

Three, the immediate window in VBE is your best friend. A simple ?Application.Match("FindMe", [A1:A10], 0) in the window can help you check if your formula is netting a similarly intended result.

As shown in the screenshot above, no string is found and an error value is returned.

Hope this helps!