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.

I've run into this issue before and Application.Match seemed to fix it. Can you double check that Application.Match doesn't work? — Tmdean, Feb 17 '14 at 21:52

score 5 · Answer 1 · answered Feb 17 '14 at 22:06

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:

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.
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.

Application.Match returning an error value

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

Hope this helps!

Dmitry Pavliv · Accepted Answer · 2014-02-18T15:09:14.720

2

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.

edited Feb 18 '14 at 15:09

answered Feb 17 '14 at 21:56

Dmitry Pavliv

35,333
13
79
80

So what you wrote does work I realized I had made a mistake and was searching the wrong section of the worksheet. Also when I was running it match was returning an integer. Is it possible to get it to return the cell reference like C1 and then use that cell reference in other functions. – user3055889 Feb 18 '14 at 14:26
@user3055889, yup! it's possible. Let me update my answer in few minutes – Dmitry Pavliv Feb 18 '14 at 14:27
I tried using the update version and res still seems to have a value of nothing when the macro attempts to print its value. Also for the [If Not res...] Wouldn't you want that to be [If res Is Nothing Then] so it shows the MsgBox when res is nothing? It's Runtime Error 91 if that helps. – user3055889 Feb 18 '14 at 14:45
@user3055889, you are right. I've already updated answer. You should refresh your page to see it – Dmitry Pavliv Feb 18 '14 at 14:47
I'm still confused why res stays nothing. [Set res = Application.Index(ActiveWorksheet.Range("A1:Z1"), Application.Match(strMake, ActiveWorksheet.Range("A1:Z1"), 0))] All my worksheet has is abc in cell C1 and I input abc as strMake. – user3055889 Feb 18 '14 at 14:54
it's because you are seeking in row. Change to `Set res = Application.Index(Range("A1:Z1"), 1, Application.Match(strMake, Range("A1:Z1"), 0))` – Dmitry Pavliv Feb 18 '14 at 14:57
That was one of the changes I had tested to see if I could get it working and res was still set as nothing. – user3055889 Feb 18 '14 at 15:00
if it still doesn't work, try to use `Set res = Range("A1:Z1").Find(What:=strMake, LookAt:=xlWhole, MatchCase:=False)` instead – Dmitry Pavliv Feb 18 '14 at 15:01
1

That works! Thanks for all of the help!! This was the first step in a large macro I'm working on do you mind if I continue to ask questions if I get stuck again? – user3055889 Feb 18 '14 at 15:09
Kindly accept his answer if you found it helpful. :) – WGS Feb 18 '14 at 15:09
@user3055889, sure, no problem! I'm almost always here:) – Dmitry Pavliv Feb 18 '14 at 15:11
**Dim toDeleteArray(10) As Integer toDeleteArray(0) = 0 ..................................... If arryTracker = arrayWatcher Then [ReDim Perserve toDeleteArray(10)] toDeleteArray (arrayTracker)** Brackets represent where the issue is. The dots represent the code between that does not matter. I can't seem to get it to compile I just need to Redim the array once I it hits its max size. – user3055889 Feb 18 '14 at 17:55
I recommend you to use [Collection](http://www.java2s.com/Code/VBA-Excel-Access-Word/Data-Type/Createcollection.htm) or [Dictionary](http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/A_3391-Using-the-Dictionary-Class-in-VBA.html) object. It's really easy:) – Dmitry Pavliv Feb 18 '14 at 18:03
I'll still have to look at those two but I found a shortcut around that problem for now. I keep getting a Compile error. **currentSearch = mySheet.Index(anArray(), i, modelLoc)** CurrentSearch: **Dim currentSearch As Variant** MySheet: **Dim mySheet As Worksheet Set mySheet = Sheets("Page 113") MsgBox TypeName(mySheet) mySheet.Activate** i = 2 modelLoc = 3 – user3055889 Feb 18 '14 at 19:10
change `mySheet.Index` to `Application.Index` – Dmitry Pavliv Feb 18 '14 at 19:14

score 0 · Answer 3 · answered Jun 06 '19 at 16:12

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.

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

3 Answers3

Linked