Excel でデータ検索といえばおなじみの "=VLOOKUP" 関数。
VBA でもこの関数をセルに書き込んだり、別シートを参照して VLOOKUP を使う ことができます。
本記事では、Excel VBA を使って別シートのデータを VLOOKUP で検索する方法とその書き方のポイント を解説します。
目次
VBAでVLOOKUPをセルに書き込む基本
まずは、通常のVLOOKUPをVBAから書き込む方法です。
✅ 例：A2 の値を使って B2 に VLOOKUP を設定する
Sub SetVlookupFormula()
Range("B2").Formula = "=VLOOKUP(A2, Sheet2!A:B, 2, FALSE)"
End Sub
📌 このコードでは、Sheet2 の A列～B列から A2 の値を検索し、B列の値を取得しています。
📌 ここでは Formula プロパティを使用して、数式として VLOOKUP を書き込んでいます。
【VBA】FormulaR1C1 プロパティで絶対参照を行う方法
VBAで別シートを参照するVLOOKUPを設定する
✅ 例："Sheet1" に "Sheet2" のデータを参照する VLOOKUP を設定
Sub VlookupAcrossSheets()
Worksheets("Sheet1").Range("B2").Formula = _
"=VLOOKUP(A2, Sheet2!A:B, 2, FALSE)"
End Sub
📌 Worksheets("Sheet1") を指定して、別シートのセルに数式を設定できます。
📌 数式内で参照される Sheet2!A:B によって、別シートを検索対象にしています。
【VBA】文字列（cells・range・value）として取得する方法
VBAでR1C1形式を使ってVLOOKUPを設定
R1C1形式は、行番号と列番号で参照する記述法です。より動的な数式の生成に向いています。
Sub VlookupWithR1C1()
With Worksheets("Sheet1").Range("B2")
.FormulaR1C1 = "=VLOOKUP(RC[-1], Sheet2!C1:C2, 2, FALSE)"
End With
End Sub
📌 RC[-1] は「左隣のセル」、つまり A2 の値を参照します。
📌 このように R1C1 形式を使えば、列位置がずれても正確に参照できます。
シート名にスペースがある場合の注意点
シート名にスペースがある場合、シート名を '（シングルクォート）で囲む必要 があります。
Sub VlookupWithSpaceInSheetName()
Worksheets("Sheet1").Range("B2").Formula = _
"=VLOOKUP(A2, '売上 データ'!A:B, 2, FALSE)"
End Sub
📌 "売上 データ" のようにスペースを含む場合、'売上 データ'! と囲って指定します。
【VBA】シート全体のスペースを削除する方法：Trim 関数・Replace 関数
複数行にVLOOKUPを設定する（ループ）
✅ 例：Sheet1 の A2:A10 に対し、Sheet2 を検索して B列に結果を入れる
Sub VlookupMultipleRows()
Dim i As Long
For i = 2 To 10
Worksheets("Sheet1").Cells(i, 2).Formula = _
"=VLOOKUP(A" & i & ", Sheet2!A:B, 2, FALSE)"
Next i
End Sub
📌 A列にあるキーを使って、B列にVLOOKUPの結果を自動入力するループ処理です。
📌 データ量に応じて 10 を Cells(Rows.Count, 1).End(xlUp).Row に置き換えるとより汎用的になります。
VBAでVLOOKUPの値を直接取得する（関数を使わず）
数式ではなく、VBA の関数として VLOOKUP 相当の動きをしたい場合は WorksheetFunction.VLookup を使います。
Sub VlookupFunctionExample()
Dim result As Variant
result = Application.WorksheetFunction.VLookup("商品A", Sheets("Sheet2").Range("A:B"), 2, False)
MsgBox "検索結果：" & result
End Sub
📌 "商品A" をキーとして、Sheet2 の A:B 範囲から値を取得し、メッセージで表示します。
📌 見つからないとエラーになるため、On Error Resume Next で対策することもあります。
・よくあるエラーと対策
| エラー内容 | 原因 | 解決策 |
|---|---|---|
#N/A | 該当する値が見つからない | 検索範囲・値の正確性を確認 |
実行時エラー 1004 | シート名や範囲が誤っている | シート名・セル参照をダブルチェック |
"WorksheetFunction.VLookup" エラー | 見つからないとクラッシュ | On Error Resume Next で事前対応する |
・まとめ
| 内容 | 方法 |
|---|---|
| 数式として別シート参照 | .Formula = "=VLOOKUP(…)" |
| R1C1形式で動的に指定 | .FormulaR1C1 = "=VLOOKUP(RC[-1], 'Sheet名'!C1:C2, 2, FALSE)" |
| シート名にスペースあり | =VLOOKUP(A2, 'Sheet 名'!A:B, 2, FALSE) |
| 複数行に適用 | For ループで .Formula を連続指定 |
| 関数として直接値取得 | Application.WorksheetFunction.VLookup |