VBA Excel-IFERROR& VLOOKUP错误 [英] VBA Excel - IFERROR & VLOOKUP error

问题描述

我正在尝试在excel VBA中创建等价于= IFERROR(VLOOKUP()，-1)的公式，其中该函数将在我的数据表中查找文本并返回第5列中的数字(如果文本为在表中，如果不是则返回-1.我已经在excel中测试了上述公式，它给了我想要的结果，问题是当我在VBA中对其进行编码时，我不断得到#VALUE！如果文本不在表中，则错误.如果文本在表中，则代码将生成所需的数字.我的代码如下:

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:

Function CCC(A As Variant)
Dim B As Variant
B = Application.WorksheetFunction.IfError(Application.WorksheetFunction.VLookup(A, Sheets("DAP").Range("$B$4:$X$7"), 5, False), -1)
CCC = B
End Function

考虑到这一点，是否有一种方法可以修复代码，以使IFERROR(VLOOKUP)工作，如果不能，那么在VBA中实现相同结果的最佳替代方法是什么?

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?

推荐答案

如果找不到该值，则使用Application.WorksheetFunction.VLookup(将破坏代码.

Using Application.WorksheetFunction.VLookup( will break the code if the value is not found.

将其包装在vba错误控制中.

Wrap it in vba error control.

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$4:$X$7"), 5, False)
On error goto 0
CCC = B

End Function

---

另一种方法是通过删除.WorksheetFormula然后测试结果来进行后期绑定.

---

Another method is to Late Bind by removing the .WorksheetFormula and then testing the result.

Function CCC(A As Range)
Dim B As Variant
B = Application.VLookup(A.Value, Sheets("DAP").Range("$B$4:$X$7"), 5, False)
If IsError(B) Then
    CCC = -1
Else
    CCC = B
End If
End Function

这篇关于VBA Excel-IFERROR& VLOOKUP错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！