【エクセルVBA入門】Vlookupメソッドを使ったときに発生するエラーを回避する方法


error

photo credit: Keith Allison Yasmany Tomas via photopin (license)

みなさん、こんにちは!
タカハシ(@ntakahashi0505)です。

エクセルVBAでバラバラの経費精算書を一つのデータベースにまとめるマクロの作り方についてお伝えしています。

前回の記事はコチラ。

【エクセルVBA入門】マクロでVLookupメソッドを使ってデータを検索する方法
エクセルVBAを使ってバラバラの経費精算書データを集約するシリーズの7回目です。今回は、業務で有効なテクニックとしてもう一つ、マスタシートからVLookupメソッドでデータを取得してくる方法についてお伝えしていきます。

VLookupメソッドを使って、マスタからデータを抽出する方法についてお伝えしました。

ですが、VLOOKUP関数に慣れている方はこう思うはずです。

「ヒットしなければエラーになってしまうのでは…?」

そうなんです。

ということで、今回はエクセルVBAのVLookupメソッドを使ったときに発生するエラーを回避する方法についてお伝えします。

では、行ってみましょう!

スポンサーリンク

前回までのおさらい

Sub 経費精算データ取り込み()

Dim fso As FileSystemObject
Set fso = New FileSystemObject

Dim di As Long: di = wsData.Cells(Rows.Count, 1).End(xlUp).Row + 1
Dim f As File
For Each f In fso.GetFolder(ThisWorkbook.Path & "\data").Files
    With Workbooks.Open(f.Path)
        With .Worksheets(1)
            
            Dim period As String: period = Left(f.Name, 6)
            Dim month As Date: month = DateSerial(Left(period, 4), Right(period, 2), 1)
                                    
            Dim staffId As Long: staffId = Val(Right(Replace(f.Name, ".xlsx", ""), 4))
            Dim staffName As String: staffName = WorksheetFunction.VLookup(staffId, wsStaff.Range("A:D"), 2, False)
            Dim departmentId As Long: departmentId = WorksheetFunction.VLookup(staffId, wsStaff.Range("A:D"), 3, False)
            Dim departmentName As String: departmentName = WorksheetFunction.VLookup(staffId, wsStaff.Range("A:D"), 4, False)
            
            Dim i As Long: i = 12
            Do While .Cells(i, 1).Value <> ""
                wsData.Cells(di, 1).Value = month '1 対象月
                wsData.Cells(di, 2).Value = .Cells(i, 1).Value '2 日付
                wsData.Cells(di, 3).Value = ""  '3 部署No
                wsData.Cells(di, 4).Value = departmentName '4 部署
                wsData.Cells(di, 5).Value = staffId '5 社員No
                wsData.Cells(di, 6).Value = staffName '6 氏名
                wsData.Cells(di, 7).Value = .Cells(i, 2).Value '7 科目
                wsData.Cells(di, 8).Value = .Cells(i, 5).Value '8 摘要
                wsData.Cells(di, 9).Value = .Cells(i, 6).Value '9 金額
                wsData.Cells(di, 10).Value = .Cells(i, 7).Value '10 備考
                i = i + 1: di = di + 1
            Loop
        End With
        .Close
    End With
Next f

End Sub

15行目でファイル名からスタッフIDを抜き出しています。

また、16~18行目にVLookupメソッドを使って、社員マスタのシートwsStaffからスタッフIDをキーとして社員名、部署ID、部署名を取得しています。

ですが、以下のような場合はVLookupメソッドが成功しません。

  • ファイル名が間違っていてスタッフIDが正しく取得できなかった場合
  • 社員マスタに不備がありスタッフIDがヒットしなかった場合

この場合、どうなっちゃうんでしょうか?

VLookupメソッドを失敗させてみる

試しに、ファイル名を以下のように変更してみました。

ファイル名のスタッフIDを誤ったものにしてみる

本来は4桁ないとダメなんですが、3桁になっちゃってます。

これで実行しますと、以下のように「実行時エラー ‘1004’: WorksheetFunction クラスの VLookup プロパティを取得できません。」というエラーが発生します。

VLookupメソッドで実行時エラーが発生

「デバッグ」ボタンを押してみましょう。

VLookupメソッドでエラーが発生した場所とスタッフIDの値

変数staffIdの内容を見てみると、スタッフIDの値は「0」、マスタに「0」は存在しないので、VLookupメソッドでエラーが発生したということになります。

これを回避する方法についてお伝えしなければなりません。

On Errorステートメント

さて、VBAでは、一般的に実行時エラーの発生はエラーメッセージの表示と、処理の停止を意味します。

しかし、その実行時エラー発生時の処理をコントロールするためのOn Errorステートメントという構文が用意されています。

その使い方は以下の通り、3種類があります。

ステートメント 内容
On Error Goto 行数(またはラベル) エラー発生時に行数で指定した行番号またはラベルに処理を分岐する
On Error Resume Next 以降エラーが発生しても続行し、次のステートメントに処理を移す
On Error Goto 0 以降のエラーハンドラーを無効にする

On Error Resume Nextでエラーを無視する

今回は、そのうちOn Error Resume Nextを使ってみましょう。

エラーが発生しても、それを無視する魔法の言葉です。

On Error Resume Next

この一文を入れた以降は、エラーを無視してエラーが発生した次の行から処理を続行します。

これを先ほどのVLookup命令の前に入れてあげて、実行してみましょう。

すると、プログラムは最後まで実行され、以下のようにデータ収集がされます。

On Error Resume Nextで実行した結果

プログラム自体は中断せずに実行されましたが、本来太郎さんのデータが入るべき箇所に、花子さんのデータが入ってしまいました。

これはこれで問題ですね…

まとめ

エクセルVBAのVLookupメソッドによるエラーを回避する方法についてお伝えしました。

また、実行時エラー発生時の処理をコントロールするOn Errorステートメントについても紹介しました。

ですが、お伝えした通り、これだけでは解決になっていないので、次回に解決をしていきたいと思います。

【エクセルVBA入門】エラーが発生したときに分岐処理を追加する方法
エクセルVBAでバラバラの経費精算書ファイルのデータを収集するマクロの作り方をお伝えしています。今回はエクセルVBAでエラーが発生したときにメッセージを表示する処理を追加する方法をお伝えしていきます。

どうぞお楽しみに!

連載目次:エクセルVBAで経費データをデータベースに集約する

請求書シリーズと逆のパターンですが、バラバラの帳票からデータ一覧つまりデータベースに情報を集めて蓄積していく、というお仕事も多いと思います。ここでは各担当者から提出された経費精算書をデータベースに蓄積するプログラムを目標にして進めていきます。
  1. 【エクセルVBA入門】バラバラの経費精算書をデータにまとめる
  2. 【エクセルVBA入門】Do While~Loop文で条件を満たす間繰り返し
  3. 【エクセルVBA入門】繰り返しを使ってデータの転記をするときの2つのポイント
  4. 【エクセルVBA入門】With文でプログラムをスッキリわかりやすく書く
  5. 【エクセルVBA入門】他のワークブックをWithで開く&保存せずに閉じる
  6. 【エクセルVBA入門】フォルダやファイルを操作するFileSystemオブジェクトとその使い方
  7. 【エクセルVBA入門】For Each~Next文でフォルダ内のブック全てを開く方法
  8. 【エクセルVBA入門】シートのデータがある最終行番号を求めるステートメントを徹底解説
  9. 【エクセルVBA入門】開いたブック名から文字列を抽出して人為的なミスを回避する方法
  10. 【エクセルVBA入門】マクロを作るときに知っておきたいマスタデータのこと
  11. 【エクセルVBA入門】開いたブックのファイル名から番号を取り出して数値に変換する
  12. 【エクセルVBA入門】マクロでVlookupを使ってデータを検索する方法
  13. 【エクセルVBA入門】Vlookupメソッドを使ったときに発生するエラーを回避する方法
  14. 【エクセルVBA入門】エラーが発生したときに分岐処理を追加する方法
  15. 【エクセルVBA入門】オートフィルタや行の非表示で隠れている行を全て表示する

タイトルとURLをコピーしました