Excel实战技巧53： 在VBA代码中使用工作表公式更有效地实现查找

excelperfect

在工作表中查找值是很常见的操作，我们可以使用VLOOKUP函数、MATCH函数、INDEX函数等来查找值。当使用VBA代码在大量的数据中进行查找操作时，灵活运用工作表公式，往往能够提高效率。

下图1所示的工作表，要在列G中查找列A中的值，如果找到则将G列中相应行对应的列H中的值复制到列A中相应行的列B中。

图1

例如，单元格A2中的“砖基础”与单元格G3中的值相同，则将单元格H3中的值复制到单元格B2中，如下图2所示。

图2

首先，定义一个动态名称，以便列G中添加项目时能够自动更新。

名称：LookupRange

引用位置：=OFFSET(Sheet1!$G$2,0,0,COUNTA(Sheet1!$G:$G)-1)

接着，在VBE的标准模块中输入代码：

Sub CopyData()

Dim lLastRowA As Long

Dim rngA As Range

Dim rngValueA As Range

Dim lRow As Long

'列A中有数据的最后一行的行号

lLastRowA = Cells(Rows.Count,1).End(xlUp).Row

'设置需要处理的数据区域

Set rngA = Range("A2:" &"A" & lLastRowA)

'遍历需要处理的数据区域

For Each rngValueA In rngA

'使用工作表函数查找数据所在的行并返回行号

lRow = Application.WorksheetFunction. _

Match(rngValueA, [LookupRange], 0) +1

'如果找到则进行相应的操作

If lRow > 0 Then

Range("B" &rngValueA.Row) = Range("H" & lRow)

lRow = 0

End If

Next

End Sub

常规操作是使用两个循环来查找值，即在第一个For Each循环中再使用一个For Each循环遍历列G中的内容来查找，但使用工作表公式使得程序代码更简洁，效率更高。

说明：本文的例子只是演示公式在VBA中的运用。其实，本例在工作表中使用VLOOKUP函数也很容易。