経理業務周りでよく使うExcel関数の一つである VLOOKUP 関数ですが Excel マクロで使う場合にはどのような記述になるか考えましたので備忘録として残しておこうと思います。
Excel関数のVLOOKUPとExcelマクロのVLOOKUP関数で大きく違うところはエラー処理（検索する値が見つからなかった時の処理）だと個人的に思っています。
Excelのシートに記載された勘定科目に対応する科目コードをvlookup関数で検索するマクロ
今回 ExcelマクロでVLOOKUP関数を使うパターンとして二つのパターンを考えました。
ひとつはFor Next ステートメントによる書き方。
もう一つはDo While ステートメントによる書き方の二つを考えました。
書き方の違いはありますが動作としてはどちらも同じ動作になります。（当たり前。）
具体的にはシート１のB列に記載された勘定科目に対応する科目コードを別シート（科目マスタ）に作成した勘定科目のマスタから検索してきます。
検索の結果、科目マスタに登録されていない勘定科目がある場合にはその部分には科目コードではなく「科目未登録」とセルに表示させ、セルを黄色にします。
動作の感じは下の動画で参考にしてもらえるとイメージがつかめると思います。
ExcelマクロでVLOOKUP関数を使う。for~nextステートメントの場合
for~nextステートメントで今回の処理を作成するとこんな感じになりました↓
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 | Sub test_vlookup()'■シート１にある勘定科目に対応する科目コードをシート２「科目マスタ」から'■VLOOKUP関数を使って参照するマクロ(for nextステートメント)'■変数宣言Dim i As LongDim n As LongDim lastrow As Long'最終行の取得lastrow = Sheets(1).Cells(Rows.Count, 2).End(xlUp).Row'■処理実行部分With Sheets(1) For i = 2 To lastrow 'エラー処理 もし対応する勘定科目コードが未登録の場合には「myerror」へ進む On Error GoTo myerror .Range("c" & i).Value = WorksheetFunction.VLookup(.Range("b" & i), Sheets(2).Range("a1").CurrentRegion, 2, False) Next i '未登録の科目がある場合にはメッセージを表示する If n > 0 Then MsgBox n & "個の未登録科目があります。" End If Exit Sub'エラーになった場合の処理myerror: .Range("c" & i).Value = "科目未登録" .Range("c" & i).Interior.ColorIndex = 6 n = n + 1 '未登録の科目があったらカウントする Resume NextEnd WithEnd Sub |
Excel関数のVLOOKUP関数の使い方はネットに腐るほど優良な記事がありますのでそちらを参考にすればいいと思いますが、こんな感じで使うと思います↓
一方、Excel マクロで VLOOKUP 関数を使うには下記のように記載する必要があります↓
マクロでVLOOKUP 関数の使う場合、書くコードの量が増えるので難しく感じますが、記載の方法、考え方自体はエクセル関数の場合と全く同じになるので慣れれば大丈夫です。
少し難しいのが検索範囲を指定するところでしょうか。
今回はsheets(2)のA1セルの周辺地域を検索範囲に指定したいので
Sheets(2).Range(“a1”).CurrentRegion
という表現で指定しています。
このあたりの検索範囲の指定方法はいろいろやり方があると思いますので好きな方法を採用すればいいと思います。
Excel マクロで VLOOKUP 関数を使う時の注意点として検索する値が見つからなかった場合にエクセルマクロの場合は下記のエラーメッセージが表示され、そこで処理が中断されてしまうという点があります。
エクセル関数であれば検索する値が見つからなかった場合には 「N＃A」 などという表示がされるので分かりやすいですし、特に何か処理をする必要がありません。
一方、 Excel マクロで VLOOKUP 関数を使おうとするとエラーが発生して動作がストップするのを回避するために下記の部分のコード（on error ステートメント）を追加していく必要があります。
この部分です↓
今回は On Error Goto ステートメントを使って VLOOKUP 関数で検索値が見つからなかった場合にはマイエラー（myerror）という所に処理を飛ばす（スキップさせる）ように記載しています。
この記載をすることで VLOOKUP 関数で検索値が見つからなかった場合はマイエラー（myerror）と記載されているところに飛んでいき、その箇所より下の処理を実行することになります。
具体的にはセルに「科目未登録」と表示して、セルの色を黄色にします。
また「Resume Next」 と記載することによってエラーが起こった箇所に戻ることができます。
なのでこうすることで、引き続きFor~ next ステートメントの処理に戻って残っている繰りかえしの処理を実行することができます。
これで最後まで検索 VLOOKUP による検索を行うことができます。
ExcelマクロでVLOOKUP関数を使う。do while~loopステートメントの場合
Do while ～loopステートメントで今回の処理を作成するとこんな感じになりました↓
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 | Sub test_vlookup2()'■シート１にある勘定科目に対応する科目コードをシート２「科目マスタ」から'■VLOOKUP関数を使って参照するマクロ(do while loopステートメント)'■変数宣言Dim i As LongDim n As Longi = 2'■処理実行部分With Sheets(1) Do While .Range("b" & i) <> "" 'エラー処理 もし対応する勘定科目コードが未登録の場合には「myerror」へ進む On Error GoTo myerror .Range("c" & i).Value = WorksheetFunction.VLookup(.Range("b" & i), Sheets(2).Range("a1:b8"), 2, False) i = i + 1 Loop '未登録の科目がある場合にはメッセージを表示する If n > 0 Then MsgBox n & "未登録科目があります。" End If Exit Sub'エラーになった場合の処理myerror: .Range("c" & i).Value = "科目未登録" .Range("c" & i).Interior.ColorIndex = 6 n = n + 1 '未登録の科目があったらカウントする Resume NextEnd WithEnd Sub |
ハイライト表示している行がfor~nextステートメントとの記述の違いになるでしょうか。
変数「i」に対して最初に「２」を格納しておいて、処理の都度変数「i」に１を足していく感じです。
この１を足す部分の記述を忘れると多分処理が無限に実行されていつまでたってもの終わらない無限ループに突入します。
この無限ループのリスクがあるので個人的にはDo while ～loopステートメントには苦手意識があります。。。
for~nextステートメントは最終行の取得をして処理を実行する回数を設定する必要がありますが、無限ループになる可能性が低いので個人的にはよく使います。
まとめ
Excel マクロで VLOOKUP 関数を使うにはどのように考えればいいかを整理してみました。
何でもかんでも Excel マクロを使う必要はないと個人的に思っているのでどうしてもマクロを使わなければいけない時以外はExcel関数による VLOOKUP を使用したほうがはるかに簡単でメンテナンスもしやすいと思っています。
どうしてもエクセルマクロで VLOOKUP 関数を使わないといけない場合には今回の記事を参考にしていけばいいかなと思います。
【本日の近況報告】
6月は税理士試験の免除合格者の官報掲載の時期になので過去の記事をリライト。
記事全体の文字数が少ない。。。
【本日の１曲】
DYGL / Half of Me
DYGL（デイグロ）の新曲がいい感じです。
このバンドを初めて知ったのは「A Paper Dream」という曲でした。その後、新曲やアルバムをこまめにチェックしていましたがこの新曲はイイですね。
こういう感じの曲は好みです。ライブで聴きたい。
ジャケットがポータブルCDプレイヤーってところがまた個人的に〇です。
