如何 VLOOKUP 在 VBA 中获取 #N/A 值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11555728/
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 VLOOKUP get #N/A value in VBA?
提问by Davuz
I have a data table in Excel, that is same as:
我在 Excel 中有一个数据表，与以下内容相同：
A B
-------------
1. aaa 11
2. bbb 22
3. ccc #N/A
4. ddd 44
I've wrote a VBA function to get value(col B) by key(in col A)
Ex: =getValue(A1)
我已经编写了一个 VBA 函数来通过键（在 col A 中）获取值（col B）例如： =getValue(A1)
In this example, if I type =getValue(A3), function is throw #VALUE! error. I was debug and see error at VLOOKUP function. Here is my code:
在这个例子中，如果我输入=getValue(A3)，函数是 throw #VALUE! 错误。我正在调试并在 VLOOKUP 函数中看到错误。这是我的代码：
Public Function getValue(ByVal key As Variant)
'get value of the cell at column B which has value 'key' at column A on same row
column2GetValue = 2
useClosestMatch = False
'error here if colum2GetValue contain #N/A
found = Application.WorksheetFunction.VLookup( _
key, _
Worksheets(SHEET_CACHE_NAME).Range("A:B"), _
column2GetValue, _
useClosestMatch _
)
getValue = found
End Function
How to VLOOKUP get #N/A value in VBA?Thank for your help!
如何 VLOOKUP 在 VBA 中获取 #N/A 值？感谢您的帮助！
回答by brettdj
You can handle the error as below.
您可以按如下方式处理错误。
Although I suggest you look consider using a more versatile Findin place of Application.VLOOKUP
虽然我建议你考虑使用更通用Find的代替Application.VLOOKUP
Sub TestMe()
Dim vTest As Variant
vTest = Application.VLookup("TesT", Range("A1:B10"), 2, False)
If IsError(vTest) Then
MsgBox "Not found", vbCritical
Else
MsgBox "value is " & vTest
End If
End Sub
