如何使用 Excel VBA 自動化 Vlookup

Vlookup是Excel中必備的功能，已成為資料處理的重要組成部分。它提供了許多可以與綜合資料庫結合使用的功能。

但是，如果您想自動執行此功能，則可以，特別是如果您知道如何使用 Excel 和 VBA 中提供的自動化語言。以下是如何在 Excel VBA 中自動執行 vlookup 函數。

Excel中的Vlookup函數公式

=vlookup(lookup_value, lookup_array, column_index, exact_match/partial_match)

公式中參數的意義：

• lookup_value：您要在陣列中尋找的基礎值。
• lookup_array：來源數據，lookup_value將作為參考。
• column_index：列返回值。
• exact_match/partial_match：使用0或1指定組合類型。

Excel中的VBA Vlookup函數

當您直接在 Excel 中或透過 VBA 使用 Vlookup 函數時，它的用法沒有太大差異。無論是資料分析師處理大量訊息，還是倉庫經理不斷處理新產品，vlookup 功能都很有用。

使用動態尋找範圍時，最好自動執行公式，以避免在 Excel 中多次操作相同公式。透過在VBA中自動化Vlookup函數，您可以一鍵執行多列計算。

插畫

此資料集有兩個部分：查找表和目標資料點。查找表包含 3 列，資料位於子類別和產品名稱兩個欄位：

目標表具有適當的列，沒有子資料夾或產品名稱資料。請注意，訂單 ID列中的值也出現在查找表的同一列中：

1. 進行必要的設置

首先在新的 Excel 工作簿中開啟程式碼編輯器（按Alt + F11或導航至「開發人員」標籤）並新增模組以開始編碼。在程式碼編輯器中，將以下行新增至程式設計視窗的頂部以建立子程式：

Sub vlookup_fn1()End Sub

子例程是 VBA 程式碼的容器，需要成功運行它。另一種選擇是建立 VBA 使用者窗體以使 UI 更具互動性。

2.宣告變數並建立引用範圍

首先，您需要使用 Dim 命令聲明變數的資料類型：

Dim i as integer, lastrow as long

接下來，建立一個變數lastrow，以儲存尋找範圍中最後填滿的行的值。lastrow函數使用end(xldown)函數來計算特定範圍內的最後一行參考。

在此範例中，lastrow變數包含尋找範圍中最後一個填充行的值，具體為 17。

lastrow = Sheets("Sheet2").Range("F3").End(xlDown).Row

在上面的範例中，目標表從 A2:C17 延伸，來源表從 F2:H17 延伸。vlookup 公式將循環遍歷 A 列中的每個值，在來源表中尋找它，並將組合條目貼到 B 列和 C 列。

但是，在進入循環之前，必須將範圍值儲存在新變數（my_range）中，如下所示：

my_range = Sheets("Sheet2").Range("F3:H" & lastrow)

您必須明確指定起始儲存格參考 (F3) 和結束列參考 (H)。VBA自動新增行值以完成尋找陣列。

3. 寫一個循環來循環遍歷每個查找值

在編寫循環之前，定義起始行值，2。處理動態元件時，必須為循環定義起始值。在目標表中，第 2 行是第一行資料。如果資料在不同的行中開始，請變更此值。

i = 2

您可以使用do-while循環來處理每一行，從第 2 行開始到第 17 行結束。與程式碼的其餘部分一樣，這方面很靈活；循環將一直運行，直到條件為假為止。使用條件測試目前行的第一個儲存格中的非空值。

Do While len(Sheets("Sheet2").Cells(i, 1).value) <> 0

在循環內部，您可以呼叫 Vlookup 函數來填充單元格：

Sheets("Sheet2").Cells(i, 2).Value = _ Application.WorksheetFunction.VLookup(Sheets("Sheet2") _ .Cells(i, 1).Value, my_range, 2, False)Sheets("Sheet2").Cells(i, 3).Value = _ Application.WorksheetFunction.VLookup(Sheets("Sheet2") _ .Cells(i, 1).Value, my_range, 3, False)

這些指令分別設定目前行第2列和第3列單元格的值，它們使用WorksheetFunction物件呼叫Vlookup函數，將第1列中的對應值傳遞給搜尋。它們還傳遞您最初定義的範圍和相關列的索引來獲取最終值。

vlookup 程式碼已準備就緒，但您仍需要在每次循環中遞增行變數：

i = i + 1

每次循環運行時，它都會將值i增加1。請記住，起始行值為 2，每次循環運行時都會發生增量。使用loop關鍵字設定該程式碼區塊的結尾。

當整個程式碼運行時，它將根據來源表中的值用結果填入兩個欄位。

下面是完整的參考程式碼：

 Sub vlookup_fn1() Dim i As Integer lastrow = Sheets("Sheet2").Range("F3").End(xlDown).Row my_range = Sheets("Sheet2").Range("F3:H" & lastrow) i = 2 Do While Len(Sheets("Sheet2").Cells(i, 1).Value) <> 0 On Error Resume Next Sheets("Sheet2").Cells(i, 2).Value = _ Application.WorksheetFunction.VLookup(Sheets("Sheet2") _ .Cells(i, 1).Value, my_range, 2, False) Sheets("Sheet2").Cells(i, 3).Value = _ Application.WorksheetFunction.VLookup(Sheets("Sheet2") _ .Cells(i, 1).Value, my_range, 3, False) i = i + 1 LoopEnd Sub

4. 建立一個按鈕來運行程式碼

您可以在 Excel 表格中建立一個按鈕來一鍵運行它。在空白 Excel 工作表上插入所需的形狀，右鍵單擊它，然後按一下「指派巨集」選項。

在對話方塊中，選擇子例程的名稱，然後按一下「確定」。

當你想運行程式碼時，點擊按鈕即可立即看到資料是如何填寫的。

以上是如何使用Excel VBA實現自動化查找功能。希望這篇文章對您有用。