VBAのVLOOKUP関数で別シートを参照する方法

VBAで別シートのセルを参照する場合は「Worksheets」を使います。
例えば「Worksheets(“Sheet1”).Range(“A1:B10”)」というようにWorksheetsでシート名を指定し、Rangeオブジェクトに「.（ドット）」でつなげて使います。下記は実際に別シートを参照するVLOOKUPの記述例です。検索値には「入力シート」の「A2」を指定し、範囲には「マスタデータ」シートの「A2:D51」を指定しています。後半の「 On Error Resume Next」以降はエラー回避のための記述となっています。検索値が見つからない場合などは「該当なし」と結果を表示するようにしています。

別シートのセルの取得方法

シートを指定してセルを取得するには下記のようにWorksheetsの引数にシート名を「”」で囲って指定し、Rangeオブジェクトと「.（ドット）」でつなげて記載します。

Worksheets("シート名").Range(セル番号) 

実際に別シートを参照するVLOOKUPの記述例

Sub vlookupAnotherSheet()
    Set Name = Worksheets("入力").Range("A2")  '検索値を指定
    Set myrange = Worksheets("マスタデータ").Range("A2:D51")  '範囲を指定
    Set result = Worksheets("入力").Range("B2")  '結果を出力するセルを定義

    On Error Resume Next 'エラー回避のステートメント
        result.Value = Application.WorksheetFunction.VLookup(Name, myrange, 2, False)
        If Err.Number <> 0 Then  'エラーナンバーが０以外の場合
            result.Value = "該当なし"  '結果のセルに「該当なし」と表示
            On Error GoTo 0  'エラー回避を解除
        End If
End Sub

コード解説

「入力」シート内のセル「A2」の値を変数Nameに代入します。

Set Name = Worksheets("入力").Range("A2")

「マスタデータ」シート内のセル範囲「A2:D51」を変数myrangeに代入します。

Set myrange = Worksheets("マスタデータ").Range("A2:D51")

「入力」シート内のセル「B2」を結果を出力するためのセルとして、変数resultに代入します。

Set result = Worksheets("入力").Range("B2")

エラー回避用に「On Error Resume Next」ステートメントを定義します。この文以降は「On Error GoTo 0」によってエラー回避の解除を行わない限り、エラーを無視して実行することができるため、VLOOKUP実行時に検索値が見つからなくても、エラーを出さずに処理を継続することができます。
「If Err.Number <> 0 Then」より下の文はエラーが出た場合の処理を記述しており、結果のセルに「該当なし」と表示するよう設定しています。
また「On Error GoTo 0」によって、エラー回避状態の解除を行っています。

On Error Resume Next 'エラー回避のステートメント
    result.Value = Application.WorksheetFunction.VLookup(Name, myrange, 2, False)
    If Err.Number <> 0 Then  'エラーナンバーが０以外の場合
        result.Value = "該当なし"  '結果のセルに「該当なし」と表示
        On Error GoTo 0  'エラー回避を解除
    End If

応用テクニック

VBAで別シートを参照して使う上での応用テクニックについて紹介します。

最終行まで繰り返しVLOOKUPを使う方法

複数の検索値に対して、最終行まで繰り返しVLOOKUPを実行し対応値を取得するコードは下記です。この例では検索値が見つからない場合にエラーが出ないようにエラー回避用の記述もしています。詳しい解説は下記のリンクを参照してください。

Sub vlookuplastline()
  On Error Resume Next 'エラー回避
　　　　Set myRange = Worksheets("マスタデータ").Range("A2:D51") '範囲を定義
    For i = 2 To Cells(Rows.Count, "A").End(xlUp).Row '最終行まで繰り返し処理
        Err.Number = 0 'エラーナンバーリセット
        With Cells(i, "A")
            .Offset(0, 1) = WorksheetFunction.vlookup(.Value, myRange, 2, False)
            If Err.Number <> 0 Then  'エラーナンバーが０以外の場合
                .Offset(0, 1).Value = "該当なし"  '結果のセルに「該当なし」と表示
            End If
        End With
    Next
    
    On Error GoTo 0  'エラー回避を解除
End Sub

ショートカットキーの練習

当サイトはショートカットキーの練習を実際の動きを確認しながら練習できる機能がついています。繰り返し指を動かして練習ができるので、ゲーム感覚で遊んでいくうちに自然とショートカットキーが使えるようになれます。ショートカットキーは暗記するよりも実際に手を動かして練習していったほうが習得が早くなるので、是非当サイトをブックマークに登録し定期的に練習してみてください。下記のSTARTキーを押下すると練習を始められます。