VLOOKUP函数在VBA中的使用

关键词：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 Ifmy_error_handler:If Err.Number = 1004 Then MsgBox "错误的供应商，请重新输入"End IfEnd 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

虽然写得极为啰嗦，但是不管数据如何增加，我们都可以得出正确的结果。

本篇文章参考VLOOKUP In VBA – With Examples 整理而来。原文有更详细的介绍。由于头条不好放链接。请大家输入自行查看。