オートフィル（Autofill）とVlookUp関数、値貼り付けのマクロ（VBA）

Excelの初心者だった頃、オートフィル（Autofill）を知った時の感動を今でも覚えています。連番の入力や日付などの入力に威力を発揮します。最初はドラッグしてやる方法をマスターし、次にダブルクリックして一気に張り付ける技を身につ行けた方は多いと思います。数式をオートフィルで入れる便利さを知った時、高校数学の行列を想起して、もう少し勉強しておくんだったなと後悔した方も多いことでしょう。

マクロだと、for文などの繰り返し処理でも代用できないこともないのですが、変数を設定したり、「＆」でつなぐコードを書くのも面倒な感じがします。

実務で私はExcelの「vlookUp関数」を多用しており、これをオートフィルで伸ばして、最後に値に変換してを貼り付ける処理によく使っています。 

こんな感じです。

要は、上の図にある数式「=VLOOKUP(B2,Sheet2!A:B,FALSE)」を数式としてセルのC2に入れ、それをオートフィルで伸ばします。そして、数式だとExcelが重くなったり、再計算を思わぬ場面で行ってしまうので、値に変換します。これをコードにします。

Sub オートフィルとVlookUp()

    '最終行の取得
    LstRow = Cells(Rows.Count, 1).End(xlUp).Row
    'C列の表示形式を標準とする（なくてもよい）
    Range("C2:C" & LstRow).NumberFormatLocal = "G/標準"
        
    'オートフィル処理

    With Range("C2")
        .Formula = "=VLOOKUP(B2,Sheet2!A:B,2,FALSE)"
        .AutoFill Destination:=Range("C2:C" & LstRow)
    End With
    
    '処理速度に応じて待機するコードを挿入する場合あり
    
    Range("C2:C" & LstRow) = Range("C2:C" & LstRow).Value

End Sub

End Withの後のコメント（’処理速度に応じて、待機するコードを挿入する場合あり）とは、何十万行にわたるような処理の場合、VlookUp関数の処理が終わ絵う前にVlookUp関数そのもの（文字列）を貼り付けてしまう場合があります。必要に応じて、処理を待つコードを入れてください。