vba 运行时错误“1004”无法获取 WorksheetFunction 类的 Match 属性
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19030786/
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
Run time error '1004' Unable to get the Match propertyof the WorksheetFunction class
提问by user1147697
In my macro, I have the following code :
在我的宏中，我有以下代码：
i = Application.WorksheetFunction.Match(str_accrual, Range(Selection, Selection.End(xlToRight)), 0)
where 'str_accrual' is a string captured earlier to this line and the Range selected is in a single row say from "A1" to "BH1" and the result will be a number which is the position of that string in that range selected.
其中 'str_accrual' 是在此行之前捕获的字符串，所选范围位于单行中，例如从“A1”到“BH1”，结果将是一个数字，该数字是该字符串在所选范围内的位置。
When I run the macro, I get the error:
当我运行宏时，出现错误：
Run time error '1004' Unable to get the Match propertyof the WorksheetFunction class
运行时错误“1004”无法获取 WorksheetFunction 类的 Match 属性
But when I run the macro line by line using (F8) key, I don't get this error but when I run the macro continuously I get the error. Again, if the abort the macro and run it again the error doesn't appear.
但是当我使用 (F8) 键逐行运行宏时，我没有收到此错误，但是当我连续运行宏时，我收到了错误。同样，如果中止宏并再次运行，则不会出现错误。
回答by sam092
I tried several times. It seems that if there is no match, the expression will prompt this error
我试了几次。好像没有匹配的话，表达式会提示这个错误
if you want to catch the error, use Application.Matchinstead
如果要捕获错误，请Application.Match改用
Then you can wrap it with isError
然后你可以用 isError
回答by www.MSOffice-deluxe.com
tons of posts on this error but no solution as far as I read the posts. It seems that for various worksheet functions to work, the worksheet must be active/visible. (That's at least my latest finding after my Match() was working randomly for spurious reasons.)
关于此错误的大量帖子，但就我阅读的帖子而言没有解决方案。 似乎要使各种工作表功能正常工作，工作表必须是 active/visible。（这至少是我的 Match() 由于虚假原因随机工作后的最新发现。）
I hoped the mystery was solved, though activating worksheets for this kind of lookup action was a pain and costs a few CPU cycles.
我希望这个谜团得到解开，尽管为这种查找操作激活工作表是一件痛苦的事情，并且会花费几个 CPU 周期。
So I played around with syntax variations and it turned out that the code started to work after I removed the underscore line breaks, regardless of the worksheet being displayed. <- well, for some reason I still had to activate the worksheet :-(
因此，我尝试了语法变体，结果发现在删除下划线换行符后代码开始工作，而不管显示的工作表如何。<- 好吧，出于某种原因，我仍然必须激活工作表 :-(
'does not work
'不起作用
'Set oCllHeader = ActiveWorkbook.Worksheets("Auswertung").Cells(oCllSpielID.Row, _
Application.Match( _
strValue, _
ActiveWorkbook.Worksheets("Auswertung").Range( _
oCllSpielID, _
ActiveWorkbook.Worksheets("Auswertung").Cells(oCllSpielID.Row, lastUsedCellInRow(oCllSpielID).Column)), _
0))
'does work (removed the line breaks with underscore for readibility) <- this syntax stopped working later, no way around activating the worksheet :-(
'确实有效（删除了带有下划线的换行符以提高可读性）<-此语法稍后停止工作，无法激活工作表:-(
Set oCllHeader = ActiveWorkbook.Worksheets("Auswertung").Cells(oCllSpielID.Row, Application.Match(strValue, ActiveWorkbook.Worksheets("Auswertung").Range(oCllSpielID, ActiveWorkbook.Worksheets("Auswertung").Cells(oCllSpielID.Row, lastUsedCellInRow(oCllSpielID).Column)), 0))
In the end I am fretting running into more realizations of this mystery and spending lots of time again.
最后，我担心会遇到更多关于这个谜团的认识，并再次花费大量时间。
cheers
干杯
回答by Mihail Kostira
I was getting this error intermittently. Turns out, it happened when I had a different worksheet active.
我间歇性地收到此错误。事实证明，它发生在我有一个不同的工作表处于活动状态时。
As the docs for Range say,
正如 Range 的文档所说，
When it's used without an object qualifier (an object to the left of the period), the Range property returns a range on the active sheet.
如果在没有对象限定符（句点左侧的对象）的情况下使用它，Range 属性将返回活动工作表上的一个范围。
So, to fix the error you add a qualifier:
因此，要修复错误，请添加限定符：
Sheet1.Range
回答by Edgar Walkowsky
I used "If Not IsError" and the error kept showing. To prevent the error, add the following line as well:
我使用了“If Not IsError”并且错误一直显示。为防止出现错误，还请添加以下行：
On Local Error Resume Next
在本地错误上继续下一步
回答by Gary's Student
That is what you get if MATCH fails to find the value.
如果 MATCH 无法找到值，这就是您得到的结果。
回答by David Zemens
Try this instead:
试试这个：
If Not IsError(Application.Match(str_accrual, Range(Selection, Selection.End(xlToRight)), 0)) Then
i = Application.Match(str_accrual, Range(Selection, Selection.End(xlToRight)), 0)
Else
'do something if no match is found
End If
Update
更新
Here is better code that does not rely on Selectionexcept as a means of user-input for defining the range to be searched.
这是更好的代码，Selection除了作为用户输入的方式来定义要搜索的范围外，它不依赖于。
Sub Test()
Dim str_accrual As String
Dim rngToSearch As Range
str_accrual = InputBox("Search for?")
Set rngToSearch = Range(Selection, Selection.End(xlToRight))
If Not IsError(Application.Match(str_accrual, rngToSearch, 0)) Then
i = Application.Match(str_accrual, rngToSearch, 0)
MsgBox i
Else
MsgBox "no match is found in range(" & rngToSearch.Address & ")."
End If
End Sub
