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

midSun31midSun31
IT・デジタル

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関数を使う方法でした。

IT・デジタル
Howpon[ハウポン]

コメント