VBAのVLOOKUPを最終行まで繰り返し検索する方法

ExcelのVBAで必要な行数だけ繰り返しVLOOKUPで検索を行いたい場合は下記コードのように最終行を取得して、FOR文で繰り返し処理をします。ここではそのサンプルコードとコードの解説を行っていきます。後半では別シートのデータを参照して、最終行まで繰り返し検索する方法についても解説します。

検索値の列の最終行まで繰り返しVLOOKUPを行うためのコード

    For i = 2 To Cells(Rows.Count, "A").End(xlUp).Row 
        With Cells(i, "A")
            .Offset(0, 1) = WorksheetFunction.vlookup(.Value, Range("D2:G51"), 2, False)
        End With
    Next

関連リンク

同じシートのデータを参照し、検索値の列の最終行まで繰り返しVLOOKUPを行う例

この例ではA列の検索値のリストの最終行を取得し、最終行まで繰り返しVLOOKUPを実行して、A列の「商品ID」に対応する「商品名」を取得するコードを紹介します。VLOOKUPを「WorksheetFunction」で使用する場合は検索値が見つからないときにエラーになってしまうので、「On Error Resume Next」でエラー回避する機能もつけています。

サンプルコード

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

VLOOKUPで検索値が見つからなくてもエラーで止まらないように「On Error Resume Next」ステートメントを記載し、以降のエラーを無視するよう指定します。この記述をすると「On Error GoTo 0」でエラー回避を解除しない限りエラーを無視し続けます。

On Error Resume Next

最終行までVLOOKUP検索を繰り返すためのFOR文を記載します。下記の「2」の部分は検索値のデータの始まりの行数を表しており、「Cells(Rows.Count, “A”).End(xlUp).Row」はA列にあるデータの最終行を取得しています。例えばB列のデータの最終行を取得したい場合は「”A”」の部分は「”B”」と記載してください。

For i = 2 To Cells(Rows.Count, "A").End(xlUp).Row

エラーナンバーをリセットします。Errオブジェクトはエラーが出ない限り上書きされないため、繰り返し処理ごとにリセットする必要があります。

Err.Number = 0

処理の高速化のため、Withでセルをまとめておきます。

With Cells(i, "A")

VLOOKUPをWorksheetFunctionで実行します。下記の「.Offset(0, 1)」は上記のWithで指定したセルの1つ右のセルを指します。「.Value」も上記のWithで指定したセルの値を指します。

.Offset(0, 1) = WorksheetFunction.vlookup(.Value, Range("D2:G51"), 2, False)

VLOOKUPで検索値が見つからなくてエラーが出た場合の処理を記載します。この例ではエラーが出た場合は結果に「該当なし」と記載するよう設定しています。

If Err.Number <> 0 Then  'エラーナンバーが０以外の場合
                .Offset(0, 1).Value = "該当なし"  '結果のセルに「該当なし」と表示
            End If

「On Error GoTo 」を記載し、エラー回避状態を解除します。

On Error GoTo 0  'エラー回避を解除

別シートのデータを参照し、検索値の列の最終行まで繰り返しVLOOKUPを行う例

この例ではA列の検索値のリストの最終行を取得し、別シートのデータ範囲を参照しながら繰り返しVLOOKUPを実行し、A列の「商品ID」に対応する「商品名」を取得するコードを紹介します。上記例と同じくVLOOKUPを「WorksheetFunction」で使用する場合は検索値が見つからないときにエラーになってしまうので、「On Error Resume Next」でエラー回避する機能もつけています。

サンプルコード

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

VLOOKUPで検索値が見つからなくてもエラーで止まらないように「On Error Resume Next」ステートメントを記載し、以降のエラーを無視するよう指定します。この記述をすると「On Error GoTo 0」でエラー回避を解除しない限りエラーを無視し続けます。

On Error Resume Next

別シートのデータ範囲を変数「myRange」に代入します。別シートのシート名はWorksheetsの引数に指定します。下記の例では「マスタデータ」がシート名にあたります。

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

最終行までVLOOKUP検索を繰り返すためのFOR文を記載します。下記の「2」の部分は検索値のデータの始まりの行数を表しており、「Cells(Rows.Count, “A”).End(xlUp).Row」はA列にあるデータの最終行を取得しています。例えばB列のデータの最終行を取得したい場合は「”A”」の部分は「”B”」と記載してください。

For i = 2 To Cells(Rows.Count, "A").End(xlUp).Row

エラーナンバーをリセットします。Errオブジェクトはエラーが出ない限り上書きされないため、繰り返し処理ごとにリセットする必要があります。

Err.Number = 0

処理の高速化のため、Withでセルをまとめておきます。

With Cells(i, "A")

VLOOKUPをWorksheetFunctionで実行します。下記の「.Offset(0, 1)」は上記のWithで指定したセルの1つ右のセルを指します。「.Value」も上記のWithで指定したセルの値を指します。

.Offset(0, 1) = WorksheetFunction.vlookup(.Value, myRange, 2, False)

VLOOKUPで検索値が見つからなくてエラーが出た場合の処理を記載します。この例ではエラーが出た場合は結果に「該当なし」と記載するよう設定しています。

If Err.Number <> 0 Then  'エラーナンバーが０以外の場合
                .Offset(0, 1).Value = "該当なし"  '結果のセルに「該当なし」と表示
            End If

「On Error GoTo 」を記載し、エラー回避状態を解除します。

On Error GoTo 0  'エラー回避を解除

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

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