Home » エクセルマクロ・Excel VBAの使い方 » WorksheetFunction » VBAでVLookup時にデータを取得できないエラーを回避

VBAでVLookup時にデータを取得できないエラーを回避

対象：Excel2007, Excel2010, Excel2013, Windows版Excel2016

VBAからVLookupを使う際にエラーを回避する

「エクセル vba vlookup エラー処理」
「エクセルマクロ vlookupのエラーを出さない」
といった検索で、このサイト・インストラクターのネタ帳へのアクセスが時折あります。

Excelマクロの中で「WorksheetFunction.VLookup(...」というコードを書いて、ワークシート関数の一つVLOOKUP関数を利用するときに、実行時エラーが発生しないようにするには、どうすればいいのかを探している方による検索でしょうか。

［スポンサードリンク］

VBAからVLOOKUP関数を利用するサンプル

まずVBAからVOOKUP関数を利用する基本を、確認しておきましょう。

Sub VLookupのサンプル_基本()
　Dim tbl As Range
　Set tbl = Range("D1:E5")
　
　Dim key As Long
　key = Range("A1").Value

　Dim ret As String
　ret = WorksheetFunction.VLookup(key, tbl, 2, False)
　Range("B1").Value = ret

End Sub

下図のように同じシートのD1:E5セルに、VLOOKUP関数からデータを取得するマスターテーブルが存在するとき、

VBAからVLookupを使う際にエラーを回避する

上記のマクロを実行すると、A1セルに入力された値から、マスターテーブル内のデータを検索して、B1セルに結果が入力されます。

VBAでVlookupしてデータが存在しないとき実行時エラーが発生する

検索するデータが存在しているときには上記のコードで問題ありませんが、マスターテーブル内にデータが存在していない場合に、実行時エラーが発生してしまいます。

VBAからVLookupを使う際にエラーを回避する

「エクセル vba vlookup エラー処理」
「エクセルマクロ vlookupのエラーを出さない」
という検索をなさった方は、この回避方法を探していたのかな、と推測しています。

WorksheetFunction.VLookupで実行時エラーの回避

以下のようなSubプロシージャにすれば、該当データが存在しない場合、B1セルに「該当なし」という文字列が入力されるようになります。

Sub VLookupのサンプル_エラー回避()
　Dim tbl As Range
　Set tbl = Range("D1:E5")
　
　Dim key As Long
　key = Range("A1").Value

On Error GoTo ErrHandl
　Dim ret As String
　ret = WorksheetFunction.VLookup(key, tbl, 2, False)
　Range("B1").Value = ret

Exit Sub
ErrHandl:
　ret = "該当なし"
　Err.Clear
　Resume Next

End Sub

エラーが発生したときにラベルErrHandlに飛ぶようにしておいて、
　On Error GoTo ErrHandl

WorksheetFunction.VLookupでエラーが発生したら、「該当なし」という文字列を変数retに代入して、
　ErrHandl:
　　ret = "該当なし"

処理を継続しています。
　　Err.Clear
　　Resume Next

WorksheetFunction.VLookupで実行時エラーを無視する

該当データがないときに、B1セルへ空白文字列を入力するのであれば、以下のようなSubプロシージャでもOKです。

Sub VLookupのサンプル_エラー無視()
　Dim tbl As Range
　Set tbl = Range("D1:E5")
　
　Dim key As Long
　key = Range("A1").Value
　
　On Error Resume Next
　Dim ret As String: ret = ""
　ret = WorksheetFunction.VLookup(key, tbl, 2, False)
　On Error GoTo 0

　Range("B1").Value = ret

End Sub

WorksheetFunction.VLookupを利用する行を、On Error Resume Next文で、エラー発生時にも処理を継続するようにしておけば、
　On Error Resume Next
　ret = WorksheetFunction.VLookup(key, tbl, 2, False)
　On Error GoTo 0

エラー発生時には空白文字列がB1セルに入力されることになります。
　Range("B1").Value = ret

String型変数の初期値はVBAの場合そもそも空白文字列ですけれど、上記のマクロでは明示的に変数宣言の直後に空白文字列の代入を行っています。
　Dim ret As String: ret = ""

最終更新日時：2019-01-01 09:12

［スポンサードリンク］