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

End Sub

常規操作是使用兩個循環來查找值，即在第一個For Each循環中再使用一個For Each循環遍歷列G中的內容來查找，但使用工作表公式使得程式程式碼更簡潔，效率更高。

說明：本文的例子只是演示公式在VBA中的運用。其實，本例在工作表中使用VLOOKUP函數也很容易。