为什么 isError( ) 不能在 excel VBA 中使用 vlookup 语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17177709/
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
Why doesn't isError( ) work with a vlookup statement in excel VBA
提问by Mutuelinvestor
I'm using excel 2007 and have created a UDF that includes three vlookup() statements. The function is supposed to return the sum of all three vlookup statments. In the majority of cases, only two the vlookup() statements will return a valid value the third statement will result in an NA because the lookup value is not included in the lookup range.
我正在使用 excel 2007 并创建了一个包含三个 vlookup() 语句的 UDF。该函数应该返回所有三个 vlookup 语句的总和。在大多数情况下，只有两个 vlookup() 语句将返回有效值，第三个语句将导致 NA，因为查找值不包括在查找范围内。
I have tried to trap the error and return a zero by using:
我尝试使用以下方法捕获错误并返回零：
Application.WorksheetFunction.iferror(vlookup(...) ,0)
A conditional that uses If iserror(vlookup()) then ...
Application.WorksheetFunction.iferror(vlookup(...),0)
使用 If iserror(vlookup()) then ...
but I can't seem to get either approach to work. If I comment out the vlookup that I know is creating the error everything works as expected.
但我似乎无法获得任何一种工作方法。如果我注释掉我知道正在创建错误的 vlookup，一切都会按预期进行。
Does anyone know why iserror(0 and iserror() don't seem to be working or perhaps an alternative approach that will work.
有谁知道为什么 iserror(0 和 iserror() 似乎不起作用，或者可能是一种可行的替代方法。
Update:
更新：
Here are the three vlookup function:
下面是三个vlookup函数：
product2 = Application.WorksheetFunction.IfError(Application.WorksheetFunction.VLookup(productA, lookuprng, offset, False), 0)
product3 = Application.WorksheetFunction.IfError(Application.WorksheetFunction.VLookup(productB, lookuprng, offset, False), 0)
product4 = Application.WorksheetFunction.IfError(Application.WorksheetFunction.VLookup(productC, lookuprng, offset, False), 0)
回答by Jon Crowell
You can trap the error with the following:
您可以使用以下方法捕获错误：
Sub HandleVlookupErrors()
Dim result As Variant
result = Application.VLookup(productA, lookuprng, offset, False)
If IsError(result) Then result = 0
End Sub
For a full explanation, please see Error Handling Within Worksheet Functions.
有关完整说明，请参阅工作表函数中的错误处理。
回答by Andy
You may consider to write a vlookup function with error handling lines:
您可以考虑编写一个带有错误处理行的 vlookup 函数：
Public Function v_lookup(lookup_value As String, table_array As Range, col_index_num As Integer, range_lookup As Boolean) As String
Dim result As Variant
result = Application.VLookup(lookup_value, table_array, col_index_num, range_lookup)
If IsError(result) Then result = ""
v_lookup = result
End Function
回答by Roy
Dim Res as Variant
Res = Application.WorksheetFunction.VLookup(Vndr, Range("A:a"), 1, False) 'Where Vndr is some unknown
If Res = Vndr then
do xyz
Else
do 123
endif
