ノン・プログラマー、仕事のためのマクロ・VBA(2)　Vlookup関数の代わりに、VBAで必要な回数だけ検索繰り返し

投稿日 2021年9月20日
更新日 2022年10月9日
著者 root

前回、［マクロの記録］(自動記録)ではできないことに、「繰り返し処理」や「条件分岐」、「マウス操作」などがある、という解説をしました。とはいえ、業務を効率化する上では、どうしても”単純作業の繰り返し”を自動化する必要がありますよね?

やっぱりVBAをイチから覚えなきゃ駄目かなぁ...とため息をついたアナタ。別に、イチからは覚えなくて良いですよ。

まずは、ご自身がかかえているExcel単純作業の内容を細かく分解して、細分化された作業のひとつずつを自動化できるプログラムを、「写経」すればよいのです。Vlookup関数の記事でご紹介した、経費精算シートの事例をもとに、具体的に見てみましょう。

Vlookup関数を使わず、VBAでVlookupする(縦方向に調べる)こともできます

Vlookup関数の記事では、関数を用いてマスター料金表から正確な料金(地下鉄乗車賃)を算出する方法をご説明しました。

この方法の最大の弱点は、「繰り返し処理を自動化できないこと」。そうです、経費精算のように毎日複数件ずつ発生して、それが事業を続ける限り永遠に続くような場合には、永遠にその処理を自動化したいじゃないですか。

そこで、Vlookup関数を使わずに、VBAで同じ検索を［永遠に］行い続ける方法をご紹介します(VBAの中に関数を組み込む方法もありますが、この記事では、あえて関数を使わずに、関数と同じことをします)。

ノン・プログラマー、仕事のためのマクロ・VBA(2)　Vlookup関数の代わりに、VBAで必要な回数だけ検索繰り返し

一旦、プログラムを簡略化するために、普通回数券と時差割引回数券の差額を判定する機能は省略しましたが、上図の繰り返し検索を行うVBAはこのようになります。

Sub Like_Vlookup()

'ワークシート名の定義

    Dim Ws1 As Worksheet, Ws2 As Worksheet
    Set Ws1 = Worksheets("SpentWS")
    Set Ws2 = Worksheets("FareWS")
        
'各シートの最終行の取得
    Dim Last1 As Long, Last2 As Long
    Last1 = Ws1.Cells(Rows.Count, 1).End(xlUp).Row
    Last2 = Ws2.Cells(Rows.Count, 1).End(xlUp).Row
    
'プログラム4　変数設定
    Dim Destination As String, Destination_name As String
    Dim i As Long, j As Long, Fare As Long
    
'WS1のA列を取得
    For i = 2 To Last1
       Destination = Ws1.Range("A" & i).Value
        
'WS3のA列を取得
    For j = 2 To Last2
        Destination_name = Ws2.Range("A" & j).Value


'WS1のA列とWs2のA列をマッチング
    If Destination = Destination_name Then

'W2のB列を取得
    Fare = Ws2.Range("B" & j).Value
    
    Exit For
    End If
Next
    
'WS1のC列に片道運賃を出力

Ws1.Range("C" & i).Value = Fare

Next
    
End Sub

このVBAで最初に覚えておくべきことは、以下の2構文です。

では、実際にVBAで記述した内容を、日本語に置き換えてみましょう。

VBAに書かれたことは、つまりこのようなことです

（１）このExcelブックには、精算シートの”SpentWS”と、マスター料金表である”FareWS”があるので、それぞれ、”SpentWS”=ワークシート名”Ws1”、”FareWS”=ワークシート名”Ws2”と定義します。

（２）精算シートの目的地(新宿、渋谷etc.)を検索値として、マスター料金表から該当する運賃を抽出するため、まず両シートのA列終端セルが何行目にあるかを検索します。

Ws1.Cells(Rows.Count, 1).End(xlUp).Row

End(xlUp).Rowとは、一旦最終行まで下がり、一つずつ上にさかのぼってデータ終端位置を見つけだすための構文です。精算シートのように、作業日によってデータの終端がどこまで伸びるか事前に予測できない作業の場合には、このような終端検索が必ず必要になります。このプログラムのキモは、【終端行にたどりつくまでの回数分、精算シートとマスター料金表とをマッチングさせ続ける】ことなのです。

（３）目的地(Destination)、マスター料金表の目的地名(Destination_name)を文字列型の変数、カウンタ(何回同じ処理を繰り返すかを決める数値)と運賃額を、長整数型と定義します。

（４）精算シートの「目的地」列が終端行に至るまでの間、「目的地」を検索値として、マスター料金表から、その「目的地名」に該当する料金(運賃)を抽出し、精算シートのC列(片道運賃欄)に出力し続けます。

つまり、Vlookup関数の検索機能に当たることを、精算シートでの必要回数分、繰り返し行えるプログラムになっている、というわけですね。