在 vba 中编写 VLOOKUP 函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5567513/
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
Writing a VLOOKUP function in vba
提问by Mike
I'm trying to lookup a value on a spreadsheet within a table array using the VLOOKUP function in my vba code. I don't know how to write it correctly.
我正在尝试使用 vba 代码中的 VLOOKUP 函数在表数组中的电子表格上查找值。我不知道如何正确地写。
Here is the normal VLOOKUP formula with all the references:
这是包含所有参考的普通 VLOOKUP 公式：
=VLOOKUP(DATA!AN2,DATA!AA9:AF20,5,FALSE)
回答by Ben Hoffstein
Have you tried:
你有没有尝试过：
Dim result As String
Dim sheet As Worksheet
Set sheet = ActiveWorkbook.Sheets("Data")
result = Application.WorksheetFunction.VLookup(sheet.Range("AN2"), sheet.Range("AA9:AF20"), 5, False)
回答by Oneide
How about just using:
如何只使用：
result = [VLOOKUP(DATA!AN2, DATA!AA9:AF20, 5, FALSE)]
Note the [and ].
注意[和]。
回答by Alejandro Sardi
Please find the code below for Vlookup:
请找到以下代码Vlookup：
Function vlookupVBA(lookupValue, rangeString, colOffset)
vlookupVBA = "#N/A"
On Error Resume Next
Dim table_lookup As range
Set table_lookup = range(rangeString)
vlookupVBA = Application.WorksheetFunction.vlookup(lookupValue, table_lookup, colOffset, False)
End Function
回答by Nickolay
As Tim Williams suggested, using Application.VLookupwill not throw an error if the lookup value is not found (unlike Application.WorksheetFunction.VLookup).
正如蒂姆·威廉姆斯所建议的那样，Application.VLookup如果未找到查找值（与 不同Application.WorksheetFunction.VLookup），则 using不会引发错误。
If you want the lookup to return a default value when it fails to find a match, and to avoid hard-coding the column number -- an equivalent of IFERROR(VLOOKUP(what, where, COLUMNS(where), FALSE), default)in formulas, you could use the following function:
如果您希望查找在找不到匹配项时返回默认值，并避免对列号进行硬编码 - 相当于IFERROR(VLOOKUP(what, where, COLUMNS(where), FALSE), default)in 公式，您可以使用以下函数：
Private Function VLookupVBA(what As Variant, lookupRng As Range, defaultValue As Variant) As Variant
Dim rv As Variant: rv = Application.VLookup(what, lookupRng, lookupRng.Columns.Count, False)
If IsError(rv) Then
VLookupVBA = defaultValue
Else
VLookupVBA = rv
End If
End Function
Public Sub UsageExample()
MsgBox VLookupVBA("ValueToFind", ThisWorkbook.Sheets("ReferenceSheet").Range("A:D"), "Not found!")
End Sub
回答by Vahid Dastitash
Public Function VLOOKUP1(ByVal lookup_value As String, ByVal table_array As Range, ByVal col_index_num As Integer) As String
Dim i As Long
For i = 1 To table_array.Rows.Count
If lookup_value = table_array.Cells(table_array.Row + i - 1, 1) Then
VLOOKUP1 = table_array.Cells(table_array.Row + i - 1, col_index_num)
Exit For
End If
Next i
End Function
回答by Avin
Dim found As Integer
found = 0
Dim vTest As Variant
vTest = Application.VLookup(TextBox1.Value, _
Worksheets("Sheet3").Range("A2:A55"), 1, False)
If IsError(vTest) Then
found = 0
MsgBox ("Type Mismatch")
TextBox1.SetFocus
Cancel = True
Exit Sub
Else
TextBox2.Value = Application.VLookup(TextBox1.Value, _
Worksheets("Sheet3").Range("A2:B55"), 2, False)
found = 1
End If
