vba 匹配不工作 Excel：错误 1004 无法获取匹配属性

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

提问by user3055889

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.

我只想在三个不同的变量上获取用户的字符串输入，并找到包含每个变量的列。我单独尝试过 Application.Match() 和 Match() ，但似乎都不起作用。

采纳答案by Dmitry Pavliv

UPD:

更新：

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

是否可以让它像 C1 一样返回单元格引用，然后在其他函数中使用该单元格引用

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 strCounthas type String, but strMake, strModelare Variant.

使用时Dim strMake, strModel, strCount As String，只有strCount类型String，但strMake, strModel有Variant。

The same thing with Dim makeLoc, modelLoc, countLoc As Integer- only countLochas Integertype.

用同样的事情Dim makeLoc, modelLoc, countLoc As Integer-只是countLoc有Integer型。

回答by Jerome Montino

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.

一，确保您的范围始终是完全合格的。例如，Range("A1:A10")还远远不够。您应该指定它应该位于哪个工作表上。如果你从另一个工作表调用这个宏，它会给你一个错误的结果或抛出一个错误。

Two, without going to too much details:

二、不赘述：

1. Application.Matchreturns 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.Matchthrowsa 1004error when it doesn't find an error. This is not the same as returning a value. As such, this is (slightly) harder to handle.

1. Application.Match如果找不到匹配项，则返回错误值。这可以使用 来处理IsError，这就是 simoco 在他的回答中所做的。
2. WorksheetFunction.Match抛出一个1004错误，当它没有找到一个错误。这与返回值不同。因此，这（稍微）难以处理。

Best practice is to always use the first one.

最佳做法是始终使用第一个。

Three, the immediate windowin 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.

三、VBE中的即时窗口是你最好的朋友。?Application.Match("FindMe", [A1:A10], 0)窗口中的一个简单符号可以帮助您检查您的公式是否获得了类似的预期结果。

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

如上面的屏幕截图所示，没有找到字符串并返回错误值。

Hope this helps!

希望这可以帮助！

回答by PGSystemTester

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

这不是一个直接的答案给OP，而是试图在人（像我）可能会发现这个问题有帮助TRAP使用VBA错误Match。通常我会用它来测试数组中是否存在一个值。

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

使用时非常令人抓狂，Application.Worksheetfunction.Match并且在值不存在时无法捕获Truewith IsError。甚至WorksheetFunction错误处理程序（iserr、isNA等）也不会将其捕获为True，而是抛出1004 Unable to get the Match Property的 VBA 错误。

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

这是通过使用Application.Match而不是解决Application.WorksheetFunction.Match。这是最违反直觉的，因为Match在输入后不会出现在智能感知中，Application.也不会Application.Match(显示输入哪些字段的提示。

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

同时 usingApplication.WorksheetFunction.Match会自动填充提示，可以理解，这可以激发用户采用这种方法，然后对为什么他们无法成功捕获错误感到困惑。