vba 如何使用 WorksheetFunction.VLookup 错误处理 1004 错误？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18063214/
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
How to error handle 1004 Error with WorksheetFunction.VLookup?
提问by JBurace
I have this code:
我有这个代码：
Dim wsFunc As WorksheetFunction: Set wsFunc = Application.WorksheetFunction
Dim ws As Worksheet: Set ws = Sheets("2012")
Dim rngLook As Range: Set rngLook = ws.Range("A:M")
'within a loop
currName = "Example"
cellNum = wsFunc.VLookup(currName, rngLook, 13, False)
VLookup is not expected to always find a result; but when it does not find a result the line errors out before I can even error check it the next line.
VLookup 并不期望总能找到结果；但是当它没有找到结果时，在我什至可以在下一行错误检查之前，该行出错了。
The error:
错误：
Run-time error '1004': Unable to get the VLookup property of the WorksheetFunction class
运行时错误“1004”：无法获取 WorksheetFunction 类的 VLookup 属性
It works fine when a result is found. What's a good way to handle errors here?
找到结果后它工作正常。在这里处理错误的好方法是什么？
采纳答案by Purchawka
There is a way to skip the errors inside the code and go on with the loop anyway, hope it helps:
有一种方法可以跳过代码中的错误并继续循环，希望它有所帮助：
Sub new1()
Dim wsFunc As WorksheetFunction: Set wsFunc = Application.WorksheetFunction
Dim ws As Worksheet: Set ws = Sheets(1)
Dim rngLook As Range: Set rngLook = ws.Range("A:M")
currName = "Example"
On Error Resume Next ''if error, the code will go on anyway
cellNum = wsFunc.VLookup(currName, rngLook, 13, 0)
If Err.Number <> 0 Then
''error appeared
MsgBox "currName not found" ''optional, no need to do anything
End If
On Error GoTo 0 ''no error, coming back to default conditions
End Sub
回答by Doug Glancy
Instead of WorksheetFunction.Vlookup, you can use Application.Vlookup. If you set a Variantequal to this it returns Error 2042 if no match is found. You can then test the variant - cellNumin this case - with IsError:
相反WorksheetFunction.Vlookup，您可以使用Application.Vlookup. 如果将 a 设置为Variant等于此值，则在未找到匹配项时返回错误 2042。然后您可以测试变体 -cellNum在这种情况下 - 使用IsError：
Sub test()
Dim ws As Worksheet: Set ws = Sheets("2012")
Dim rngLook As Range: Set rngLook = ws.Range("A:M")
Dim currName As String
Dim cellNum As Variant
'within a loop
currName = "Example"
cellNum = Application.VLookup(currName, rngLook, 13, False)
If IsError(cellNum) Then
MsgBox "no match"
Else
MsgBox cellNum
End If
End Sub
The Applicationversions of the VLOOKUPand MATCHfunctions allow you to test for errors without raisingthe error. If you use the WorksheetFunctionversion, you need convoluted error handling that re-routes your code to an error handler, returns to the next statement to evaluate, etc. With the Applicationfunctions, you can avoid that mess.
和函数的Application版本允许您在不引发错误的情况下测试错误。如果您使用该版本，您需要复杂的错误处理，将您的代码重新路由到错误处理程序，返回到下一个语句进行评估等。使用这些函数，您可以避免这种混乱。VLOOKUPMATCHWorksheetFunctionApplication
The above could be further simplified using the IIFfunction. This method is not always appropriate (e.g., if you have to do more/different procedure based on the If/Then) but in the case of this where you are simply trying to determinie what prompt to display in the MsgBox, it should work:
使用该IIF函数可以进一步简化上述内容。这种方法并不总是合适的（例如，如果您必须基于 执行更多/不同的过程If/Then），但在这种情况下，您只是试图确定在 MsgBox 中显示什么提示，它应该可以工作：
cellNum = Application.VLookup(currName, rngLook, 13, False)
MsgBox IIF(IsError(cellNum),"no match", cellNum)
Consider those methods instead ofOn Error ...statements. They are both easier to read and maintain -- few things are more confusing than trying to follow a bunch of GoToand Resumestatements.
考虑这些方法而不是On Error ...语句。它们都更易于阅读和维护——没有什么比试图遵循一堆GoToandResume语句更令人困惑的了。
回答by cauchy
From my limited experience, this happens for two main reasons:
根据我有限的经验，发生这种情况的主要原因有两个：
- The lookup_value(arg1) is not present in the table_array(arg2)
- 所述Lookup_Array中（ARG1）是不存在于table_array的（ARG2）
The simple solution here is to use an error handler ending with Resume Next
这里的简单解决方案是使用一个错误处理程序结束 Resume Next
- The formats of arg1 and arg2 are not interpreted correctly
- arg1 和 arg2 的格式没有正确解释
If your lookup_valueis a variable you can enclose it with TRIM()
如果你lookup_value是一个变量，你可以用TRIM()
cellNum = wsFunc.VLookup(TRIM(currName), rngLook, 13, False)
cellNum = wsFunc.VLookup( TRIM(currName), rngLook, 13, False)
