Excel VBAでVLOOKUPを複数回ループ(繰り返し)処理する方法

Excel VBAなどプログラミング言語を利用するメリットの一つはループの記述により繰り返し処理を簡潔に書けることでしょう。

この記事では、ExcelのVBAでVLOOKUP関数をループ処理と組み合わせて利用する方法をご紹介します。

VBAでVLOOKUPを複数回ループする方法

検索値をループする場合、列番号をループする場合についてそれぞれ解説します。

方法1: VLOOKUPの検索値をループする

3列目～6列目の商品番号に対応する在庫をそれぞれ取得するVBAのプログラムを作成します。

Excelの関数では、縦にコピーするところですがVBAではループで表現します。For文を記述することで、VLOOKUPの繰り返し処理を実行することができます。

また、検索値として参照するセルを以下のように書くことでB3セル、B4セル・・・と検索列を1ずつ進めていきます。

Range("B" & i + 3)

検索結果を表示するセルもループ回数に伴い1ずつ下に進める必要があります。

Range("C" & i + 3)

具体的なサンプルのコードは以下のようになります。

Option Explicit

　

Sub 検索()

    Dim x As String

    Dim i As Integer

　

    ' 4回ループする

    For i = 0 To 3

　

        '商品番号を取得

        x = Range("B" & i + 3).Value

　

        With ActiveSheet

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

            Range("C" & i + 3).Value = _

                Application.WorksheetFunction.VLookup(x, Range("G3:I11"), 3, False)

        End With

　

    Next

　

End Sub

これを検索ボタンのマクロとして設定します。

「在庫検索」ボタンをクリックして3列目～6列目の商品番号に対応する在庫を取得することができました。

方法2: VLOOKUPの列番号をループする

続いて列番号をループして、1回のクリックで複数の情報を取得するプログラムを作成します。

検索ボタンを押すと、商品一覧から価格、在庫、カラーを取得できるようにします。

列番号をループ変数を用いて計算します。

Application.WorksheetFunction.VLookup(x, Range("G3:J11"), 2 + i, False)

表示セルも列を進める必要があります。Range関数だとアルファベット指定なので難しいため、Cells関数を使ってセルを指定します。使い方は「Cells（列、行）」です。

Cells(3, 3 + i)

サンプルコードは以下の通りです。VLOOKUPの行が長くなりそうなのでセルの計算部分は別途変数にしました。

Option Explicit

　

Sub 検索()

    Dim x As String

    Dim i As Integer

    Dim display_column_num As Integer

    Dim get_column_num As Integer

　

    ' 2列目～4列目をループで取得する

    For i = 0 To 2

　

        '商品番号を取得

        x = Range("B3").Value

        '表示セルを取得(i=0の場合C列を取得する)

        display_column_num = 3 + i

        '取得列を設定(i=0の場合2列目を設定する)

        get_column_num = 2 + i

　

        With ActiveSheet

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

            Cells(3, display_column_num).Value = _

                Application.WorksheetFunction.VLookup(x, Range("G3:J11"), get_column_num, False)

        End With

　

    Next

　

End Sub

列番号のループによって、価格、在庫、カラーを一度に取得することができました。

以上、Excel VBAでVLOOKUPを複数回ループ(繰り返し)処理する方法でした。