VBA Excel - IFERROR & VLOOKUP 错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/45508472/
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
VBA Excel - IFERROR & VLOOKUP error
提问by user8417476
I'm trying to create the equivalent of the =IFERROR(VLOOKUP(),-1) formula in excel VBA where the function would look up text in my data table and return the number in the 5th column if the text is in the table and -1 if it isn't. I've tested the above formula in excel and it gives me the desired result the problem is when I go to code it in VBA I keep getting the #VALUE! error if the text is not in the table. The code produces the desired number if the text is in the table. My code is as follows:
我正在尝试在 excel VBA 中创建等效的 =IFERROR(VLOOKUP(),-1) 公式，该函数将在我的数据表中查找文本，如果文本在表中，则返回第 5 列中的数字如果不是，则为 -1。我已经在 excel 中测试了上面的公式，它给了我想要的结果问题是当我在 VBA 中编码时，我一直得到 #VALUE！如果文本不在表格中，则会出错。如果文本在表格中，代码会生成所需的数字。我的代码如下：
Function CCC(A As Variant)
Dim B As Variant
B = Application.WorksheetFunction.IfError(Application.WorksheetFunction.VLookup(A, Sheets("DAP").Range("$B:$X"), 5, False), -1)
CCC = B
End Function
With that in mind, is there a way to fix the code so that the IFERROR(VLOOKUP) works and if not what is the best alternative to achieve the same result in VBA?
考虑到这一点，有没有办法修复代码，以便 IFERROR(VLOOKUP) 工作，如果没有，在 VBA 中实现相同结果的最佳替代方法是什么？
采纳答案by Scott Craner
Using Application.WorksheetFunction.VLookup(will break the code if the value is not found.
Application.WorksheetFunction.VLookup(如果未找到该值，则使用将破坏代码。
Wrap it in vba error control.
将其包装在 vba 错误控制中。
Function CCC(A As Range)
Dim B As Variant
B = -1
On Error Resume Next
B = Application.WorksheetFunction.VLookup(A.Value, Sheets("DAP").Range("$B:$X"), 5, False)
On error goto 0
CCC = B
End Function
Another method is to Late Bind by removing the .WorksheetFormulaand then testing the result.
另一种方法是通过删除.WorksheetFormula然后测试结果来延迟绑定。
Function CCC(A As Range)
Dim B As Variant
B = Application.VLookup(A.Value, Sheets("DAP").Range("$B:$X"), 5, False)
If IsError(B) Then
CCC = -1
Else
CCC = B
End If
End Function
