【Excel VBA】VLOOKUP関数を最終行まで一括挿入する3つの方法！

Excel VBAでVLOOKUP関数を最終行まで一括挿入して実行したいときはないでしょうか。

けど、そんな中で悩むことは、

ですよね。

今回はそんなお悩みを解決する

についてまとめます！

Excel VBAでVLOOKUP関数を最終行まで一括挿入して実行する完成イメージ

Excel VBAでVLOOKUP関数を最終行まで一括挿入して実行する完成イメージについて説明します。

まず、VLOOKUP関数の参照先となる商品マスタ表を用意します。A列に商品名、B列に単価の値を入力していきます。
次に売上表を用意し、E列に商品名、G列に商品マスタの商品名と合致した単価をVLOOKUP関数で取得していきます。

VLOOKUPの挿入は手作業ではなくVBAを使い、売上表の最終行まで一気に処理していきます。

それではさっそくやってみましょう。

VLOOKUP関数を最終行まで一括挿入して実行する基本操作

実は、VBAを使わなくても最終行までVLOOKUP関数を一括挿入することは可能です。

Excelのオートフィルを使った技となります。

D2にVLOOKUP関数を手入力します。

右下の矢印をダブルクリックします。

はい、あっという間にできました。

タカヒロ

この方法は隣列の値の最終行を、挿入時の最終行として判定するため、隣の列に挿入したい最終行まで値が入っている必要があります。VBAであれば最終行判定したい列を指定できますのでより柔軟に対応できるというわけです。

VLOOKUP関数を最終行まで一括挿入するVBA（AutoFillメソッド）

次にVBAで複数のセルに対して一括で関数を挿入してみます。
G1セルへ入力されたVLOOKUP関数を最終行目までコピーしていきます。

まず、連続データを作成するAutoFillメソッドを利用する方法について説明をします。

サンプルコードは以下の通りです。

Sub 複数セルに一括でVLOOKUP関数を入れる_AutoFillメソッド()
    Dim intLastRow As Integer
    Dim strInsertCol As String
    Dim strInsertRow As Integer
    
    '関数挿入の列をアルファベットで指定してください。
    strInsertCol = "G"
    
    '関数挿入の行を数値で指定してください。
    strInsertRow = "2"
    
    '最終行の位置番号を取得します。
    intLastRow = Cells(Rows.Count, 6).End(xlUp).Row

    '開始セルからの処理です。
    With Range(strInsertCol & strInsertRow)
        '開始セルへ挿入するVLOOKUP関数を入力します。
        '数式として開始セルへ挿入します。
        .Formula = "=VLOOKUP(E2,A$2:B$8,2,FALSE)"
        '最終行までオートフィルします。
        .AutoFill Destination:=Range(strInsertCol & strInsertRow & ":" & strInsertCol & intLastRow), Type:=xlFillCopy
    End With
End Sub

VBAの設定

VBAの設定をしましょう。

関数挿入の列をアルファベットで指定してください。
サンプルではG列指定のため”G”と入れています。

関数挿入の行を数値で指定してください。
サンプルではG2セルから挿入していきますので、”2″を入れています。

開始セルへ挿入するVLOOKUP関数を入力します。
G2セルに入れるVLOOKUP関数をそのまま入れてください。

VBAの実行

では実行してみましょう。

はい、VLOOKUP関数が挿入されましたね。

確認のためG列以の列を指定してみます。
サンプルではJ2セルを開始セルとしていますので、”J”に変更します。

実行してみましょう。

はい、J列にVLOOKUP関数が挿入されましたね。

VBAの説明

セルに関数を入れるためには、この値は数式/関数である旨指定をする必要ありますので、Formulaプロパティを使います。

開始セルの関数を最終行までオートフィルしコピーしていきます。

AutoFillメソッドの構文は以下の通りです。
セルを指定するRangeオブジェクトにAutoFillメソッドを適用する使い方になります。
Typeは標準のオートフィル「xlFillDefault」を指定します。

タカヒロ

なお、連続データを示す「xlFillSeries」、コピーを示す「xlFillCopy」 を指定しても同様の結果を得ることができます。

VLOOKUP関数を最終行まで一括挿入するVBA（Rangeオブジェクト）

Rangeオブジェクトを利用してVBAで複数のセルに対して一括で関数を挿入してみます。

セル範囲指定をしたRangeオブジェクトに頭のVlookup関数を指定すると、
AutoFillメソッドと同じくVLOOKUP関数が最終行目までセル位置も変更しながらコピーされていきます。

サンプルコードは以下の通りです。

"Sub 最終行取得の上複数セルに一括でVLOOKUP関数を入れる_Rangeオブジェクト()

    Dim intEndrow As Integer
    
    "最終行を判定します｡
    intEndrow = Cells(Rows.Count, 6).End(xlUp).Row

    "Rrangeオブジェクトに数式を一括代入します。
    Range("G2:G" & intEndrow).Formula = "=VLOOKUP(E2,A$2:B$8,2,FALSE) "

End Sub

VBAの設定

VBAの設定をしましょう。

最終行数を取得したい列を左から数えた番号で指定します。
サンプルではF列指定なので6番目の6を指定しています。

サンプルではG2セルから挿入していきますので、”G2″を入れています。

開始セルへ挿入するVLOOKUP関数を入力します。
G2セルに入れるVLOOKUP関数をそのまま入れてください。

VBAの実行

では実行してみましょう。

はい、VLOOKUP関数が挿入されましたね。

VBAの説明

Rrangeオブジェクトに数式を一括代入します。
開始セルの数式をRangeオブジェクトに指定すると、最終行までオートフィルしコピーしたように数式内にあるセルの位置も変更されます。

タカヒロ

Rrangeオブジェクトを使用する方法はコードが少なくてよいですね。

VLOOKUP関数を最終行まで一括挿入するVBA（For Nextステートメント）

次はFor Nextステートメントで処理する方法について説明します。

サンプルコードは以下の通りです。

Sub 最終行取得の上複数セルに一括でVLOOKUP関数を入れる_ForNextステートメント()
    Dim strInsertRow As Integer
    
    '開始セルから最終行まで繰り返し処理をします。
    '関数挿入の行を数値で指定してください。
    For strInsertRow = 2 _
    To Cells(Rows.Count, 6).End(xlUp).Row
    
        '開始セルへ挿入するVLOOKUP関数を入力します。
        'Cellsプロパティにより数式として開始セルへ挿入します。
        Cells(strInsertRow, 7).Formula = _
        "=VLOOKUP(E" & strInsertRow & ",A$2:B$8,2,FALSE) "
        
    Next strInsertRow
End Sub

VBAの設定

VBAの設定をしましょう。

関数挿入の行を数値で指定してください。
サンプルではG2セルから挿入していきますので、”2″を入れています。

最終行数を取得したい列を左から数えた番号で指定します。
サンプルではF列指定なので6番目の6を指定しています。

開始セルへ挿入するVLOOKUP関数を入力します。
※文字結合をしていますので、検索値セルのアルファベット部分だけを入れてください。

VBAの実行

では実行してみましょう。

はい、前と同様にVLOOKUP関数が挿入されましたね。

VBAの説明

こちらのVBAは単純に開始セルの位置番号と最終行数を設定し、ForNextステートメントでその数分繰り返しているといった内容となります。

Cellsプロパティにより数式として開始セルへ挿入します。

VBAの実装手順

実装手順は以下の通りです。

今回はExcel側にこのVBAを実装します。

①Excelを新規に開き、「開発」タブをクリックし、「VisualBasic」をクリックします。
もしくはショートカットキー「Alt」＋「F11」でもOKです。

②標準モジュールを追加します。
左ペインのVBAProjectを右クリックし、「挿入」、「標準モジュール」を選択します。

③右ペインのウインドウに上記のVBAを入力します。

こちらで完了です。

VBAを実行する

では早速VBAの実行をしてみましょう。

①「開発」タブの「VBA」をクリックし実行したいマクロを選択し、「実行」をクリックします。

②処理がされたことが確認できれば完了です。
※完了メッセージやステータス管理など必要に応じて実装してもらえばと思います。

さいごに

いかがでしょうか。

今回は、

についてまとめました。

また、他にも便利な方法がありますので、よろしければご参照頂ければと思います。