Excel VBA：无法获取匹配项，错误“无法获取 WorksheetFunction 类的匹配属性”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17751443/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA: Can't get a match, error "Unable to get the Match property of the WorksheetFunction class"
提问by Humble Val
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
回答by David Zemens
Use the Application.Matchfunction 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.
使用Application.Match可以更好地捕获错误的功能。使用 时WorksheetFunction.Match，当找不到匹配项时，它会返回一个错误，这就是您遇到的情况。
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
You could also potentially use the CountIffunction:
您还可以潜在地使用该CountIf功能：
If Application.WorksheetFunction.CountIf(myRange, Cells(e,1).Value) > 0 Then
Cells(e,3).Value = "Yes"
Else:
Cells(e,3).Value = "No"
End If
Neither of these approaches requires you to use the m1variable, you can assign this variable within the Truepart of the If/Thenstatement, if you need to identify wherethe match is found.
这些都不办法要求您使用的m1变量，你可以在内部分配这个变量True的部分If/Then说法，如果你需要找出其中找到匹配。
回答by Jaycal
Just as another option, this can also be done by putting the formula below in cell C2, and dragging it down to C23.
作为另一种选择，这也可以通过将下面的公式放在单元格 C2 中并将其向下拖动到 C23 来完成。
=IF(COUNTIF($A:$A,B2)>=1,"YES","NO")
