VLOOKUP関数の参照先をテーブル化してマクロの記録をする

前回のブログではVLOOKUP関数の設定を自動でできるVBAのプログラムの作成を行いました。

今回は上の見積書の右側にある「商品名と単価の表」をテーブルとして設定してから「マクロの記録」を行いたいと思います。

参照先としてテーブルを指定する「構造化参照」にすることで参照先データの増減に柔軟に対応できるようになります。

先ずテーブルの作成をして名前を付けます、「商品名、単価」のデータ内のセルを1カ所選択して「Ctrl」キー＋「T」キーを押します。

「テーブルの作成」ダイアログボックスが表示されるので、「OK」をクリックします。

これでテーブルの作成がされました、次にテーブルの表全体が選択された状態で「テーブル名」を設定します。

エクセルシート左上にある【テーブル名:】の「名前ボックス」に「文房具テーブル」と入力して「Enter」キーを押し入力を確定します。

テーブルが設定できたので「マクロの記録」を行います。

C13のセルを選択してから[開発] タブの [コード]グループで、[マクロの記録] をクリックするとマクロの記録ダイアログボックスが表示されるので[OK] をクリックして記録を開始します。

1.C14のセルを選択して=VLと入力しTabキーを押します。
2.セルA14を選択して「,」カンマを入力します。
3.F14～G25を範囲選択して関数式に文房具テーブルと入力されたら「,」カンマを入力します。
4.2を入力して「,」カンマを入力します。
5.下向き矢印でFALSEを選択しTabキー押してENTERキーで確定します。
6.C14のセルにVLOOKUP関数が設定できました。【=VLOOKUP(A14,文房具テーブル,2,FALSE)】
7.C14のセルの右下の十字のポインタをC23までドラッグしてオートフィルを行います。
8.記録終了をクリックします。

「マクロの記録」して生成されたコードは下のようになります。

Sub Macro1()
'
' Macro1 Macro
'

'
    Range("C14").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],文房具テーブル,2,FALSE)"
    Range("C14").Select
    Selection.AutoFill Destination:=Range("C14:C23"), Type:=xlFillDefault
    Range("C14:C23").Select
End Sub

マクロの記録ではActiveCell.FormulaR1C1 = “=VLOOKUP(RC[-2],文房具テーブル,2,FALSE)”の部分がR1C1形式で記録されるため実際使う場合には少し分かりづらいので標準的なExcelのセル参照形式のA1形式に直すと次のようになります。

Sub Macro1()
'
' Macro1 Macro
'

'
    Range("C14").Select
    ActiveCell.Formula = "=VLOOKUP(A14,文房具テーブル,2,FALSE)"
    Range("C14").Select
    Selection.AutoFill Destination:=Range("C14:C23"), Type:=xlFillDefault
    Range("C14:C23").Select
End Sub

必要のないコードを整理してからA14のセルに値がない場合はC14のセルも空白にする「IF(A14 =””””,””””,」をVLOOKUP(A14,文房具テーブル,2,FALSE)の前に入れるとプログラムの完成です。

Sub Macro1()

    Range("C14").Select
    ActiveCell.Formula = "=IF(A14 ="""","""",VLOOKUP(A14,文房具テーブル,2,FALSE))"
    Selection.AutoFill Destination:=Range("C14:C23"), Type:=xlFillDefault
    
End Sub

プログラムの実行手順はまずエクセルのシート1を表示して「開発タブ」 →「マクロの表示」ボタン → マクロ名を選択 → [実行] ボタンを押すとプログラムを実行できます。