Excel VBA:无法获得匹配,错误“无法获取WorksheetFunction类的Match属性” [英] Excel VBA: Can't get a match, error "Unable to get the Match property of the WorksheetFunction class"
问题描述
我有这个表,我试图找出代码是否与其他列中的某个地方匹配,但是它是错误的。您的帮助非常感谢。
子测试()
Dim m1 As long
Dim myrange As Range
设置myrange =工作表(Sheet1)。范围(B2:B23)
对于e = 2至23
m1 = Application.WorksheetFunction.Match (单元格(e,1).Value,myrange,0)
如果m1> 0然后
单元格(e,3).Value =是
Else
单元格(e,3).Value =否
结束如果
下一步e
MsgBoxComplete!
End Sub
使用 Application.Match
功能,可以更好地捕获错误的能力。当使用 WorksheetFunction.Match
时,如果找不到匹配,则会返回错误,这正是您遇到的问题。
如果不是IsError(Application.Match(Cells(e,1).Value,myrange,0))Then
' b $ b细胞(e,3).Value =是
否则:
细胞(e,3).Value =否
如果
您还可以使用 CountIf
函数:
如果Application.WorksheetFunction.CountIf(myRange,Cells(e,1).Value)> 0然后
单元格(e,3).Value =是
其他:
单元格(e,3).Value =否
结束如果
这两种方法都不需要使用 m1
你可以在 True
的 If / Then
语句的中分配这个变量,如果你需要识别其中找到匹配。
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
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
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
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类的Match属性”的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!