VLOOKUP関数の設定を「マクロの記録」をして自動化する

見積書 Excel VBA VLookup

今回はVLOOKUP関数の「検索値～検索範囲～列番号～検索の型～オートフィル」までの設定を自動化する方法を考えたいと思います。

先ずは上のような見積書で右側の「商品名と単価」の表から値を参照してC14～C23のセルに商品の単価を入力するVLOOKUP関数の設定の「マクロの記録」を行います。

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

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

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

Sub Macro1()
'
' Macro1 Macro
'

'
    Range("C14").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],R14C6:R25C7,2,FALSE)"
    Range("C14").Select
    Selection.AutoFill Destination:=Range("C14:C23"), Type:=xlFillDefault
    Range("C14:C23").Select
End Sub

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

Sub Macro1()
'
' Macro1 Macro
'

'
    Range("C14").Select
    ActiveCell.Formula = "=VLOOKUP(A14,$F$14:$G$25,2,FALSE)"
    Range("C14").Select
    Selection.AutoFill Destination:=Range("C14:C23"), Type:=xlFillDefault
    Range("C14:C23").Select
End Sub

次に必要のないところを整理したのが次のコードです。

Sub Macro1()

    Range("C14").Select
    ActiveCell.Formula = "=VLOOKUP(A14,$F$14:$G$25,2,FALSE)"
    Selection.AutoFill Destination:=Range("C14:C23"), Type:=xlFillDefault
    
End Sub

上のコードではA14からA23のセルに入力がないときにC14からC23のセルに「#N/A」のエラー表示が出てしまいます。「#N/A」とは参照しているセルに値がない場合にでるエラー表示です。

そこで「#N/A」を表示させないために「VLOOKUP(A14,$F$14:$G$25,2,FALSE」の前にA14のセルに値がない場合はC14のセルも空白にする「IF(A14 =””””,””””,」のコードを入れます。

Sub Macro1()

    Range("C14").Select
    ActiveCell.Formula = "=IF(A14 ="""","""",VLOOKUP(A14,$F$14:$G$25,2,FALSE))"
    Selection.AutoFill Destination:=Range("C14:C23"), Type:=xlFillDefault
    
End Sub

これでプログラムが出来上がりました、プログラムの実行手順はまずエクセルのシート1を表示して「開発タブ」 →「マクロの表示」ボタン → マクロ名(Macro1)を選択 → [実行] ボタンを押すとプログラムを実行できます。

このようにVLOOKUP関数の設定を自動でできるVBAのプログラムを作成しておくと、間違ってセルの関数を消してしまったときなどに直ぐにVLOOKUP関数の再設定できるようになります。

今回は「商品名と単価」の表をテーブルの設定をしないで「マクロの記録」をしてプログラムを組みましたがテーブルの設定をして「マクロの記録」をすると更にわかりやすいコードになるのでそれは次回のブログで行いたいと思います。

それでは、最後までお読みいただきありがとうございました！(^^♪

コメント