Match Not working Excel: Error 1004 Unable to get

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

UPD:

Is it possible to get it to return the cell reference like C1 and then use that cell reference in other functions

Sub Sales_Summary_Macro()
    Dim strMake As String, strModel  As String, strCount As String
    Dim makeLoc, modelLoc As Integer, countLoc As Integer
    Dim res As Range
    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
        On Error Resume Next
        'Set res = Range("A1:Z1").Find(What:=strMake, LookAt:=xlWhole, MatchCase:=False)
        Set res = Application.Index(Range("A1:A10"), Application.Match(strMake, Range("A1:A10"), 0))
        On Error GoTo 0
        If res Is Nothing Then
            MsgBox "Nothing found!"
            Exit Sub
        End If
        'Print address of result
        Debug.Print res.Address

        makeLoc = res.Value
        Debug.Print makeLoc
    End If
End Sub

BTW,

when you are using Dim strMake, strModel, strCount As String, only strCount has type String, but strMake, strModel are Variant.

The same thing with Dim makeLoc, modelLoc, countLoc As Integer - only countLoc has Integer type.

Answer 2

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!

Answer 3

This is not a direct answer to the OP, but people (like me) may find this question helpful when trying to TRAP an error with vba Match. Typically I would use this to test if a value exists in an array.

It's quite maddening when using Application.Worksheetfunction.Match and being unable to capture a True with IsError when a value doesn't exist. Even the WorksheetFunction error handlers (iserr, isNA, etc) will not capture this as True and instead throws the VBA error of 1004 Unable to get the Match Property.

This is resolved by using Application.Match instead of Application.WorksheetFunction.Match. This is most counterintuitive as Match doesn't appear in the intellisense after typing Application. nor does Application.Match( display prompts for what fields to enter.

Meanwhile using Application.WorksheetFunction.Match does auto-populate with prompts which understandably can inspire users to take this approach and then be confused why they can't successfully trap an error.