Excel VBA:无法获取匹配项，错误“无法获取 WorksheetFunction 类的匹配属性" [英] Excel VBA: Can't get a match, error "Unable to get the Match property of the WorksheetFunction class"

问题描述

出于对一切美好事物的热爱，我似乎无法让它发挥作用.我不断收到上面提到的错误.

For the love of all that is good, I cannot seem to get this to work. I keep getting the error mentioned above.

我有这个表，我正在尝试找出代码是否与另一列中某个地方的自己的子代码匹配，但是它出错了.非常感谢您的帮助.

I have this table, and I'm trying to find out whether the code matches it's own sub-code somewhere within the other column, however it's erroring out. Your help is greatly appreciated.

Sub testing()

    Dim m1 As long
    Dim myrange As Range

    Set myrange = Worksheets("Sheet1").Range("B2:B23")

    For e = 2 To 23
        m1= Application.WorksheetFunction.Match(Cells(e, 1).Value, myrange, 0)

        If m1 > 0 Then
            Cells(e, 3).Value = "Yes"
        Else
            Cells(e, 3).Value = "No"
        End If
    Next e

MsgBox "Complete!"

End Sub

推荐答案

使用 Application.Match 函数可以更好地捕获错误.使用 WorksheetFunction.Match 时，如果找不到匹配项，它会返回一个错误，这就是您遇到的情况.

Use the Application.Match function which allows for better ability to trap errors. When using the WorksheetFunction.Match, when a match is not found, it returns an error, which is what you're experiencing.

If Not IsError(Application.Match(Cells(e, 1).Value, myrange, 0)) Then
    'Do stuff when the match is found
    Cells(e, 3).Value = "Yes"
Else:
    Cells(e, 3).Value = "No"
End If

您还可以使用 CountIf 函数:

You could also potentially use the CountIf function:

If Application.WorksheetFunction.CountIf(myRange, Cells(e,1).Value) > 0 Then
    Cells(e,3).Value = "Yes"
Else:
    Cells(e,3).Value = "No"
End If

这些方法都不需要您使用 m1 变量，您可以在 If/Then 的 True 部分分配此变量语句，如果您需要确定在哪里找到匹配项.

Neither of these approaches requires you to use the m1 variable, you can assign this variable within the True part of the If/Then statement, if you need to identify where the match is found.

这篇关于Excel VBA:无法获取匹配项，错误“无法获取 WorksheetFunction 类的匹配属性"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！