別のファイルのテーブルを参照してVLOOKUP関数のマクロの記録をする

Excel VBA VLookup

前回は同じファイル内のテーブルを参照してVLOOKUP関数のマクロの記録をしてプログラムを作成しました、今回は別のファイルのテーブルを参照してVLOOKUP関数のマクロの記録をしてプログラムを作成したいと思います。

VLOOKUP関数で別ファイルを参照すると関数式が複雑になって嫌だなと思う方も多いと思いますがテーブルを参照とすることでわかりやすいコードになりますので参考にしていただければと思います。

先ず同じフォルダの中にマクロの記録をする「見積書.xlsm」と参照先のファイル「参照先テーブル.xlsx」を入れます。

「見積書.xlsm」「参照先テーブル.xlsx」の2つのファイルを開いておいて「見積書.xlsm」のC13のセルを選択してマクロの記録を開始します。

1.見積書.xlsmのC14のセルを選択して=VLと入力しTabキーを押します。
2.セルA14を選択して「,」カンマを入力します。
3.参照先テーブル.xlsxに移動してC2～D13のセルを範囲選択します。
4.見積書.xlsmに戻り関数式に[参照先テーブル.xlsx]Sheet1!文房具テーブルと入力されたら「,」
  カンマを入力します。
5.2を入力して「,」カンマを入力します。
6.下向き矢印でFALSEを選択しTabキー押してENTERキーで確定します。
7.C14のセルにVLOOKUP関数が設定できました。
【=VLOOKUP(A14,参照先テーブル.xlsx!文房具テーブル[#データ],2,FALSE)】
8.C14のセルの右下の十字のポインタをC23までドラッグしてオートフィルを行います。
9.記録終了をクリックします。

マクロの記録をして生成されたものが下のコードになります。

Sub Macro1() 'R1C1形式 他のブックのテーブルをVLOOKUPで参照
'
' Macro1 Macro
'

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

実際に使うときわかりやすいようにA1形式に直します。

Sub Macro1() 'A1形式 他のブックのテーブルをVLOOKUPで参照
'
' Macro1 Macro
'

'
    Range("C14").Select
    ActiveCell.Formula = "=VLOOKUP(A14,参照先テーブル.xlsx!文房具テーブル[#Data],2,FALSE)"
    Range("C14").Select
    Selection.AutoFill Destination:=Range("C14:C23"), Type:=xlFillDefault
    Range("C14:C23").Select
End Sub

必要のないコードを整理してVLOOKUPの前に「IF(A14 =””””,””””」を追加すればプログラムの完成です。

Sub Macro1() 'A1形式 他のブックのテーブルをVLOOKUPで参照

    Range("C14").Select
    ActiveCell.Formula = "=IF(A14 ="""","""",VLOOKUP(A14,参照先テーブル.xlsx!文房具テーブル[#Data],2,FALSE))"
    Selection.AutoFill Destination:=Range("C14:C23"), Type:=xlFillDefault
    
End Sub

同じファイル内のテーブルを参照してVLOOKUP関数の設定を行った場合との違いは「参照先テーブル.xlsx!」と文房具テーブルの後に[#Data]が入ったところです。

見積書.xlsmを表示してC14からC23の関数式を消してから「開発タブ」 →「コード」グループ →「マクロの表示」ボタン → マクロ名を選択 → [実行] ボタンを押すとプログラムを実行できますのでぜひお試し下さい。(^^♪

コメント