Excel VBAでVLOOKUP関数の参照を別ブックにする方法

この記事では、Excel VBAでVLOOKUP関数の参照を別ブックにする方法をご紹介します。

別ブックを参照する場合、間違えた数式を修正したりするのは手間がかかるため、VBAで自動化することでミスを防ぐこともできます。

以下では、VLOOKUP関数の範囲で別ブックを参照するVBAコードの紹介と使い方をご説明します。

開発タブを表示する

今回は、VBAを使って別ブックを参照するので上記の画像の赤矢印で示した「開発タブ」を表示させる必要があります。

「開発タブ」を表示させる方法は、以下の記事でご紹介しています。

エクセルの開発タブを表示する方法

Excel VBAでVLOOKUP関数の参照を別ブックにする方法

上記の画像は、VLOOKUP関数を使って別ブックにある「商品リスト」シートから単価を抽出して「注文」シートに表示しています。

例えばマクロの実行を押すと、「注文」シートのA2セルに入力した「紅茶」を「商品リスト」ブック内にある「商品リスト」シートのA列で検索し、該当した場合は「紅茶」に紐づいた3列目の「300」を「注文」シートのC2セルに自動で返します。

手動で別ブックを参照する場合は、ミスの原因にもなるのでVBAで自動化すると便利です。

以下では、Excel VBAでVLOOKUP関数の参照を別ブックにするコードと手順をご紹介します。

①開発タブ、②Visual Basicの順に選択します。

「Microsoft Visual Basic for Applications」が開きました。

①挿入タブ、②標準モジュールの順に選択します。

「標準モジュール」が開きました。

以下のコードをコピーしてモジュールに貼り付けてください。

Sub 注文票単価自動入力()
    Dim TargetWorkbook As Workbook
    Dim TargetSheet As Worksheet
    Dim SourceWorkbook As Workbook
    Dim LastRow As Long
    Dim i As Long
    
    ' ターゲットブック
    Set TargetWorkbook = ThisWorkbook ' このマクロが実行されているブック
    ' ターゲットブックのシートを参照
    Set TargetSheet = TargetWorkbook.Sheets("注文") ' ターゲットシートの名前を適宜変更
    
    ' 別ブックを開く
    Set SourceWorkbook = Workbooks.Open("x:\xxxx\xxxx\xxxx\xxxx\商品リスト.xlsx")
    
    ' ターゲットシートのA列の最終行を取得
    LastRow = TargetSheet.Cells(TargetSheet.Rows.Count, "A").End(xlUp).Row
    
    ' A2セルからA列をループ
    For i = 2 To LastRow
        ' VLOOKUP関数の数式を設定
        TargetSheet.Cells(i, "C").Formula = "=VLOOKUP(A" & i & ", " & SourceWorkbook.Name & "!$A:$C, 3, FALSE)"
    Next i
    
    ' 別ブックを閉じる
    SourceWorkbook.Close SaveChanges:=False
End Sub

現在開いているマクロを実行するシート（例：注文）に、別ブック（例：("x:\xxxx\xxxx\xxxx\xxxx\商品リスト.xlsx")を参照します。

別ブックの参照はローカルパスを入力しています。シート名や、ローカルパスは適宜変更してください。

上記のコードは、A2セル以降のA列に入力された値を別ブックのA列からC列を検索し、検索値に紐づく値を3列目から抽出して、同じ行のC列に結果を返します。

1行目は見出し行に指定しているので、マクロが実行される範囲は2行目以降です。

上記のコードは自動で参照ファイルを開く仕様になっているので、事前に別ブックを開いておく必要はありません。

ただし、参照ファイルを編集している場合は保存されないままファイルが閉じてしまうのでご注意ください。

作成したマクロを保存します。

①ファイルタブ、②上書き保存の順に選択します。

「Microsoft Excel」ダイアログボックスが表示されました。

マクロを保存しようとしているExcelがすでに.xlsx拡張子で保存されている場合は、「次の機能はマクロなしのブックに保存できません」というダイアログボックスが表示されます。

マクロ有効ブックとして保存するのでいいえボタンを押します。

「名前を付けて保存」ダイアログボックスが表示されました。

①保存先（例：デスクトップ）を選択し、②ファイル名（例：注文票）を入力します。③ファイルの種類でExcelマクロ有効ブックを選択し、④保存ボタンを押します。

Excelシートに戻ります。

①開発タブ、②マクロの順に選択します。

「マクロ」ダイアログボックスが表示されました。

参照ファイルを編集している場合は、必ず上書き保存してから実行ボタンを押してください。

①作成したマクロ（例：注文票単価自動入力）を選択し、②実行ボタンを押します。

別ブックの「商品リスト」から値を参照して結果が表示することができました。

以下で、検索値（例：商品名）を変更した場合、シートに反映されるのか試してみます。

A5セルにコーヒーを入力します。

商品名を入れ替えると、単価の値も変わりました。

シートの値が反映されていることを確認できました。