如何VLOOKUP在VBA中获得#N / A值? [英] How to VLOOKUP get #N/A value in VBA?
问题描述
AB
----- --------
1. aaa 11
2. bbb 22
3. ccc#N / A
4. ddd 44
我已经写了一个VBA函数来获取值(col B)按键(col A)
Ex: = getValue(A1)
在此示例中,如果我键入 = getValue(A3)
,函数抛出#VALUE!错误。我正在调试,看到VLOOKUP功能的错误。这是我的代码:
公共函数getValue(ByVal key As Variant)
'列B处的单元格在同一行的列A上具有值key
column2GetValue = 2
useClosestMatch = False
'如果colum2GetValue包含#N / A
found = Application.WorksheetFunction.VLookup(_
key,_
工作表(SHEET_CACHE_NAME).Range(A:B),_
column2GetValue,_
useClosestMatch _
)
getValue = found
结束功能
如何VLOOKUP在VBA中获得#N / A值?
感谢您的帮助!
您可以处理以下错误。
虽然我建议您考虑使用更通用的查找
代替 Application.VLOOKUP
Sub TestMe()
Dim vTest As Variant
vTest = App lication.VLookup(TesT,Range(A1:B10),2,False)
如果IsError(vTest)然后
MsgBoxNot found,vbCritical
Else
MsgBoxvalue is& vTest
End If
End Sub
I have a data table in Excel, that is same as:
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)
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:
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!
You can handle the error as below.
Although I suggest you look consider using a more versatile Find
in place of 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
这篇关于如何VLOOKUP在VBA中获得#N / A值?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!