VBA Excel - IFERROR和VLOOKUP错误(VBA Excel - IFERROR & VLOOKUP error)

 我正在尝试在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$4:$X$7"), 5, False), -1)
CCC = B
End Function
 
 考虑到这一点，有没有办法修复代码，以便IFERROR（VLOOKUP）工作，如果不是什么是在VBA中实现相同结果的最佳替代方案？ 

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
 
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?

原文：https://stackoverflow.com/questions/45508472