Excel VBA。VLOOKUP関数をExcel VBAでつくってみるとエラーを考えないといけない
＜Excel VBA:VLOOKUP関数＞
商品コードから商品名などを商品リストから抽出することができる、VLOOKUP関数。
この関数をExcel VBAでも使うことができるのですが、問題は、#N/Aのエラーが発生した場合を考えないといけないということです。
VLOOKUP関数なら、Excel VBAでわざわざ作らないで、普通に関数を設定すれば？と考えますが、作業の流れとして、CSVファイルなどのデータを読み込んだあとに、別のリストから必要なデータをVLOOKUP関数で検索抽出したいということを一連の作業として自動化したいような場合だと思ってください。
今回は、VLOOKUP関数の説明にスポットを当てますので、CSVファイルで読み込んだあとという設定で説明してきます。
では、Excel VBAのプログラム文を確認していきましょう。
Sub vba_vlookup()
Dim i As Long
Dim S_list As Range
Dim S_code As String
Dim Vup As String
i=2
Set S_list = Range("e1").CurrentRegion
S_code = Cells(i, "a").Value
Vup = WorksheetFunction.vlookup(S_code, S_list, 2, False)
Cells(i, "b").Value = Vup
End Sub
まず、エラーがなく、動くのか確認しますので、A2だけA05と入力して実行してみます。
問題なく実行されました。
プログラム文の説明は後回しにして、A2をB05と商品リストにないコードを入力して改めて実行して確認してみましょう。
エラーが表示されてしまいます。
通常のExcel関数ならば、実行しても、#N/Aとエラーが表示されるだけなのですが、Excel VBAでは、止まってしまいます。
そのため、エラー時に対応したようにプログラム文を対応させる必要があります。
では、エラーに対応したExcel VBAのプログラム文に変更してみます。
Sub vba_vlookup_error()
Dim i As Long
Dim S_list As Range
Dim S_code As String
Dim Vup As String
Set S_list = Range("e1").CurrentRegion
For i = 2 To 6
S_code = Cells(i, "a").Value
On Error Resume Next
Vup = ""
Vup = WorksheetFunction.vlookup(S_code, S_list, 2, False)
On Error GoTo 0
Cells(i, "b").Value = Vup
Next i
End Sub
エラー対応だけでなく、複数のデータに対応させるようにしました。
では、実行してみましょう。
エラーが表示されることもなく、また、複数のデータにも対応することができました。
それでは、プログラム文を確認してみましょう。
お馴染みの変数宣言ですね。
Dim i As Long は、For To Nextのカウント用です。
Dim S_list As Range は、VLOOKUP関数の引数の範囲に該当する変数です。
Dim S_code As String は、VLOOKUP関数の引数の検索値に該当する変数です。
Dim Vup As String は、検索抽出結果を格納する変数です。
Set S_list = Range("e1").CurrentRegion
e1からの商品リストを範囲選択する行ですね。
データが増加することも考えて、範囲選択を固定しないで、CurrentRegionを使っています。
For To Nextで行数分繰り返し処理を行います。
S_code = Cells(i, "a").Value
A2のデータを検索値の変数に代入します。
On Error Resume Next
エラーが発生した場合に対応した行です。
エラーが発生するとエラーの発生した次の行から処理を続行します。
Vup = ""
検索抽出結果の変数に空白を代入します。
繰り返し処理を行っていますので、一度、空白にしておきます。
Vup = WorksheetFunction.vlookup(S_code, S_list, 2, False)
WorksheetFunction関数です。VLOOKUP関数と同じ引数を持っています。
S_codeが検索値
S_listが範囲
2が列
Falseが完全一致。
On Error GoTo 0
エラー処理ルーチンを無効にする一行です。
Cells(i, "b").Value = Vup
結果を出力セルに検索抽出結果を表示させます。
このように、Excel VBAにもVLOOKUP関数の処理を行うことができますので、用途に合わせて使ってみてはいかがでしょうか？
