VLOOKUP函数在VBA中的使用

weima938 2019-03-07

展开全文

关键词：EXCEL VBA 函数 RANGE用法 END用法 CELLS用法

阅读本文大概需要：8分钟

VLOOKUP函数在VBA中已经有内置，因此我们就不需要重新构造，直接使用就可以了。本篇文章将使用几个小例子来说明VLOOKUP函数在VBA中的使用。

首先是VLOOKUP函数在VBA中的语法规则：

Application.WorksheetFunction.VLOOKUP(lookup_value, table_array, column_index, range_lookup)

-lookup_value：查找的值是什么

-table_array：查找的值在哪个表中寻找，对应值需要在第一列

-column_index：需要取得的值在第几列，列号

-range_lookup： false为精确匹配，true为模糊匹配。这里只讨论精确匹配。

使用范例①：使用VLOOKUP函数，查找供应商名为刘备有限公司的货款，并使用msgbox方法输出到立即窗口。

1-1

以下是针对使范例①编写的代码：

Sub get_amout()
 Dim target_name As String
 Dim amout As String
 
 target_name = "刘备有限公司"
 amout = Application.WorksheetFunction.VLookup(target_name, Sheet1.Range("B3:D13"), 3, False)
 Debug.Print ("amount is : " & amout & "元")
End Sub

以下为结果：

当我们使用VBA来写一个查询1个值对1个值的查询系统时，这将是一个可用的方法。因此我们需要用户来输入自己需要查询的信息。

使用范例②：使用VLOOKUP函数，根据用户的输入，查找供应商名为刘备有限公司的货款，查询如果有结果需使用msgbox方法输出到立即窗口，如果用户输入空值则输出"不合法的空值！"，如果用户输入不存在的供应商则输出"错误的供应商，请重新输入"

以下是针对使范例②编写的代码：

Sub get_amout()
 On Error GoTo my_error_handler:
 Dim target_name As String
 Dim amount As String
 
 target_name = InputBox("请输入供应商名称")
 
 If Len(target_name) > 0 Then
 amount = Application.WorksheetFunction.VLookup(target_name, Sheet1.Range("B3:D13"), 3, False)
 MsgBox ("amount is : " & amount & "元")
 Else
 MsgBox ("不合法的空值！")
 End If
my_error_handler:
If Err.Number = 1004 Then
 MsgBox "错误的供应商，请重新输入"
End If
End Sub

使用范例③：使用VLOOKUP函数，在表中的联系人一列填入正确的信息。

以下为针对范例③所编写的代码：

Sub get_amout()
 On Error Resume Next
 Dim target_name As String
 
 Dim lookup_value_table As Range
 Dim target_table_array As Range
 
 Dim contact_row As Long
 Dim contact_clm As Long
 
 
 Set lookup_value_table = Sheet1.Range("B3:B13")
 Set target_table_array = Sheet1.Range("H3:I13")
 
 contact_row = Sheet1.Range("E3").Row
 contact_clm = Sheet1.Range("E3").Column
 
 
 For Each contact In lookup_value_table
 Sheet1.Cells(contact_row, contact_clm) = _
 Application.WorksheetFunction.VLookup(contact, target_table_array, 2, False)
 contact_row = contact_row + 1
 Next contact
 MsgBox "已完成"
End Sub

请大家一定要添加以下代码：

On Error Resume Next

因为他将保证程序在找不到对应值的时候仍然会继续执行程序。

接下来我来看一下结果，令人遗憾结果又一部分没有显示。

这个问题出在这两行代码上

Set lookup_value_table = Sheet1.Range("B3:B13")
Set target_table_array = Sheet1.Range("H3:I13")

我想实际的程序中如果把范围写得死死的，那简直就是一种灾难，因为这意味着，如果数据稍有增加，那我们就得改代码。这违背了我们编写VBA代码简化工作的初衷。因此我们希望，无论是查找的值，还是索引的表，在数据增加的时候，仍然可以得出正确的结果，因此我们需要动态选取一整个需要的区域。

我们将这两行代码改写成如下的样式：

Sub get_amout()
 On Error Resume Next
 Dim target_name As String
 
 Dim lookup_value_table As Range
 Dim target_table_array As Range
 
 Dim contact_row As Long
 Dim contact_clm As Long
 Dim contact_rng As Range
 
 Dim lookup_value_table_row As Integer
 Dim target_table_array_row As Integer
 Dim lookup_value_table_clm As Integer
 Dim target_table_array_clm As Integer
 
 lookup_value_table_row = 3
 lookup_value_table_clm = 2
 target_table_array_row = 3
 target_table_array_clm = 8
 
 Set contact_rng = Sheet1.Range("E3")
 
 Set lookup_value_table = Sheet1.Range(Cells(lookup_value_table_row, lookup_value_table_clm), _
 Cells(lookup_value_table_row, lookup_value_table_clm).End(xlDown))
 
 Set target_table_array = Sheet1.Range(Cells(target_table_array_row, target_table_array_clm), _
 Cells(target_table_array_row, target_table_array_clm).End(xlDown).End(xlToRight))
 
 contact_row = contact_rng.Row
 contact_clm = contact_rng.Column
 
 
 For Each contact In lookup_value_table
 Sheet1.Cells(contact_row, contact_clm) = _
 Application.WorksheetFunction.VLookup(contact, target_table_array, 2, False)
 contact_row = contact_row + 1
 Next contact
 MsgBox "已完成"
End Sub