Excel VBAでVLOOKUP関数を使う方法【別シートブックの参照も】

Excelの関数の中でもよく使われるVLOOKUP関数ですが、実はExcel VBAでも使用することができます。

この記事では、Excel VBAでVLOOKUP関数を使う方法をサンプルを交えて解説します。参照範囲が別シートやブックに存在する場合でも検索することができます。

VBAでVLOOKUP関数を使う方法

以下のシートで「在庫検索」ボタンをクリックするとB3セルの商品番号を一覧表から検索し「在庫」列の値を返すプログラムを作ってみます。

VBAでVLOOKUP関数を使うには以下の構文で記述します。式で利用する場合と同様ですね。

WorksheetFunction.VLookup(検索値,範囲,列番号,検索の型)

使用例は次の通りです。

Application.WorksheetFunction.VLookup(x, Range("F3:H11"), 3, False)

範囲には商品一覧であるF3～H11を設定し、在庫列は範囲の3列目にあるので列番号を「3」と設定しています。

コードのサンプルです。

Option Explicit

　

Sub 検索()

    Dim x As String

　

    '商品番号を取得

    x = Range("B3").Value

　

    With ActiveSheet

        '在庫（3列目）を表示

        Range("C3").Value = _

            Application.WorksheetFunction.VLookup(x, Range("F3:H11"), 3, False)

    End With

End Sub

在庫取得ボタンにマクロとして設定します。

これで「在庫検索」ボタンをクリックすると該当の商品の在庫が取得できるようになりました。

別シートを参照する場合

続いて商品一覧が別シートにある場合の検索プログラムを作成してみます。

別シートの参照範囲から検索する場合は、Rangeの前に「Worksheets(シート名)」を記述します。

Application.WorksheetFunction.VLookup(x, Worksheets("一覧").Range("B3:D11"), 3, False)

別シートを参照するコードのサンプルです。

Option Explicit

　

Sub 検索()

    Dim x As String

　

    '商品番号を取得

    x = Range("B3").Value

　

    With ActiveSheet

        '在庫（3列目）の値を表示

        Range("C3").Value = _

            Application.WorksheetFunction.VLookup(x, Worksheets("一覧").Range("B3:D11"), 3, False)

    End With

End Sub

これで「在庫検索」ボタンをクリックすると、別シートにある一覧表を参照し該当の商品の在庫が取得できるようになりました。

別ブックを参照する場合

最後に商品一覧表が別ブック「list.xlsx」にある場合の検索プログラムを作成してみます。

別ブックの参照範囲から検索する場合は、以下のようにWorkbooksで指定します。

Application.WorksheetFunction.VLookup(x, Workbooks("list.xlsx").Worksheets("一覧").Range("B3:D11"), 3, False)

ただし、これは別ブックが開かれている場合にしか参照されません。

より実用的に使えるように、以下では開いていないブックを参照するプログラムとしています。

Option Explicit

　

Sub 検索()

    Dim x As String

    Dim ex As New Excel.Application

    Dim sPath As String

    Dim wb As Workbook

　

    '商品番号を取得

    x = Range("B3").Value

　

    '開くブックを指定

    sPath = "C:\Users\sk\Documents\list.xlsx"

　

    '読み取り専用で開く

    Set wb = ex.Workbooks.Open(Filename:=sPath, UpdateLinks:=0, ReadOnly:=True, IgnoreReadOnlyRecommended:=True)

　

    With ActiveSheet

        '在庫（3列目）の値を表示

        Range("C3").Value = _

            Application.WorksheetFunction.VLookup(x, wb.Worksheets("一覧").Range("B3:D11"), 3, False)

    End With

End Sub

このようにパスを記述してOpen関数を使うと開いていないブックも参照することができます。

誤って保存されないように読取専用オプションを付与しておきましょう。

ex.Workbooks.Open(Filename:=sPath, UpdateLinks:=0, ReadOnly:=True, IgnoreReadOnlyRecommended:=True)

別ブックにある一覧表から在庫が取得できるようになりました。

別ブックを参照する方法はやや難しかったかもしれませんね。以上、Excel VBAでVLOOKUP関数を使う方法でした。