Excel VBAでVLOOKUP関数を実行し、エラー（#N/A）を回避する安全な書き方

Excel VBAでVLOOKUP関数の使い方をお探しですね。

広告

Excel VBAでVLOOKUPのエラー(#N/A)を回避する方法【初心者向け】

Excel VBAでVLOOKUP関数を使うと、ワークシート上では見慣れた「#N/A」が、VBAでは実行時エラーになったり、セルにエラー値として出力されたりすることがあります。

特に、検索値が見つからないケースを想定せずにコードを書くと、処理が途中で止まってしまい、集計や転記の自動化が不安定になってしまいます。

この記事では、Excel VBAでVLOOKUP関数を使うときに、エラー（#N/A）を回避する安全な書き方を、初心者の方にも分かりやすく解説します。

単にエラーを消すだけでなく、なぜエラーが起きるのか、どの書き方が実務で安定しやすいのかまで、しっかり整理していきますね。

1. VBAでVLOOKUPを使うときに#N/Aが起きる理由

ExcelのVLOOKUP関数で「#N/A」が出るのは、基本的には検索値が検索範囲の左端列に見つからなかったときです。

ワークシート上なら「#N/A」と表示されるだけですが、VBAから呼び出す場合は書き方によって動きが変わります。

この違いを知っておかないと、「同じVLOOKUPなのに、セルではエラー表示、VBAでは処理停止」という困った状況になりやすいんです。

VBAでVLOOKUPを実行する方法には、主に2つあります。

`WorksheetFunction.VLookup`を使う方法と、`Application.VLookup`を使う方法です。

`WorksheetFunction.VLookup`は、VLOOKUPの結果が#N/Aになると実行時エラーとして扱われるため、何も対策しないとマクロが止まってしまいます。

一方、`Application.VLookup`は#N/AをVBAのエラー値として返すので、`IsError`関数で判定できます。

安全に書くという点では、この違いがとても重要なんですね。

また、#N/Aは「本当にデータが存在しない」場合だけでなく、見た目は同じでも値の型が違う場合にも発生します。

たとえば、検索値が数値の「1001」なのに、検索範囲側では文字列の「1001」として保存されているケースです。

他にも、前後に余分なスペースが入っている、全角と半角が混在している、検索範囲の左端列を間違えている、VLOOKUPの第4引数を省略して近似一致になっている、といった原因もよくあります。

特にVBAでは、処理対象が1件だけでなく数百件、数千件になることが多いので、1つの検索値が見つからないだけでマクロ全体が止まるのは避けたいところです。

そのため、VLOOKUPを使う前提であっても「見つからない場合がある」ことを普通の分岐として設計する必要があります。

エラーが出たら場当たり的に`On Error Resume Next`で無視するのではなく、戻り値を確認してからセルに書き込む形にすると、後から見ても意図が分かりやすいコードになりますよ。

2. Application.VLookupとIsErrorを使う安全な基本形

Excel VBAでVLOOKUP関数を実行し、エラー（#N/A）を回避する安全な書き方として、まず覚えておきたいのが`Application.VLookup`と`IsError`を組み合わせる方法です。

`Application.VLookup`は、検索値が見つからなかった場合でもすぐにマクロを停止させず、結果としてエラー値を返してくれます。

そのため、返ってきた値がエラーかどうかを`IsError`で判定してから、セルへ出力したり、別の値に置き換えたりできるんです。

基本の形は次のようになります。

検索値が見つかった場合は結果を出力し、見つからなかった場合は空白や「該当なし」など好きな値を入れます。

実務では空白にするか、メッセージを入れるかは後続処理によって選ぶといいですよ。

空白にすると見た目はきれいですが、未検索なのか該当なしなのかが分かりにくくなる場合もあります。

“`vb
Sub SafeVLookupSample()

Dim ws As Worksheet
Dim lookupValue As Variant
Dim tableRange As Range
Dim result As Variant

Set ws = ThisWorkbook.Worksheets(“Sheet1”)
Set tableRange = ws.Range(“A:D”)

lookupValue = ws.Range(“F2”).Value

result = Application.VLookup(lookupValue, tableRange, 3, False)

If IsError(result) Then
ws.Range(“G2”).Value = “該当なし”
Else
ws.Range(“G2”).Value = result
End If

End Sub
“`

このコードで大事なのは、`result`を`Variant`型で受けている点です。

VLOOKUPの結果は文字列、数値、日付、エラー値など色々な可能性があるので、最初から`String`型や`Long`型で受けると、想定外の型変換エラーにつながることがあります。

VLOOKUPの戻り値をいったん`Variant`で受け取って、エラーでないことを確認してから必要に応じて型を変換するほうが安全なんです。

また、第4引数には基本的に`False`を指定します。

これは完全一致を意味します。

省略したり`True`にしたりすると近似一致になって、検索範囲が昇順に並んでいない場合に間違った値を返す可能性があります。

#N/Aを避けたいからといって近似一致にすると、エラーは減ったように見えても、別人のデータや別商品の金額を取得してしまう危険があります。

安全なVLOOKUPでは、「見つからない場合は見つからないと判定する」ことが大切です。

`WorksheetFunction.VLookup`を使っても、`On Error`で実行時エラーを捕まえることはできます。

ただ、単純な検索失敗を例外処理として扱うより、`Application.VLookup`で結果を受けて`IsError`で分岐するほうがコードの意図が明確です。

エラーを無視するための`On Error Resume Next`を広い範囲に書くと、本来気づくべき別のエラーまで見逃す可能性があるので、VLOOKUPの#N/A対策としては慎重に使うべきですね。

3. 複数行を処理するときの実務向けコード例

実務でVLOOKUPをVBA化する場合、1つのセルだけを検索するよりも、一覧表の各行に対して検索結果を埋めていくケースが多いです。

このときも考え方は同じで、各行の検索値を取得して、`Application.VLookup`で検索して、`IsError`で判定してから結果を書き込みます。

1行でも見つからないデータがあるだけで処理が止まるようなコードでは、日次作業や月次集計の自動化には向きません。

次の例では、F列にある検索値を使って、A:Dの表から3列目の値を取得し、G列へ出力しています。

検索値が空白の場合は処理しないようにして、見つからない場合は「該当なし」と表示します。

最終行はF列を基準に取得しているので、検索したいデータが並んでいる列に合わせて調整してくださいね。

“`vb
Sub SafeVLookupLoop()

Dim ws As Worksheet
Dim tableRange As Range
Dim lastRow As Long
Dim i As Long
Dim lookupValue As Variant
Dim result As Variant

Set ws = ThisWorkbook.Worksheets(“Sheet1”)
Set tableRange = ws.Range(“A:D”)

lastRow = ws.Cells(ws.Rows.Count, “F”).End(xlUp).Row

For i = 2 To lastRow

lookupValue = ws.Cells(i, “F”).Value

If Len(Trim(CStr(lookupValue))) = 0 Then
ws.Cells(i, “G”).Value = “”
Else
result = Application.VLookup(lookupValue, tableRange, 3, False)

If IsError(result) Then
ws.Cells(i, “G”).Value = “該当なし”
Else
ws.Cells(i, “G”).Value = result
End If
End If

Next i

End Sub
“`

このコードでは、空白の検索値を先に除外しています。

空白のままVLOOKUPを実行しても検索はできますが、意図しない空白行に一致したり、#N/Aになったりするので、先に判定しておくほうが結果を読み取りやすくなります。

また、`Trim(CStr(lookupValue))`によって前後の半角スペースを除いたうえで空白判定をしています。

ただし、検索そのものに使っている値は元の`lookupValue`なので、表側に余分なスペースがある場合は別途データ整形が必要です。

複数行処理では、処理速度にも注意が必要です。

数百行程度なら上記の書き方で十分ですが、数万行規模になると、セルへ1つずつ読み書きする処理が遅くなることがあります。

その場合は、配列に読み込んで処理する、辞書オブジェクトを使って検索する、またはワークシート関数ではなく別の検索方法に切り替える選択肢もあります。

ただし、まずは安全に止まらないコードを書くことが優先です。

速度改善は、正しい結果が得られる状態を確認してから行うほうが失敗が少なくなりますよ。

VLOOKUPの検索範囲を指定するときは、ブック名やシート名を明示することも大事です。

`Range(“A:D”)`のようにシートを省略すると、アクティブシートが変わったときに別シートを参照してしまう可能性があります。

サンプルのように`ws.Range(“A:D”)`と書けば、どのシートを参照しているのかがはっきりします。

マクロを他の人が使う場合や、将来メンテナンスする場合にも、参照先が明示されたコードのほうが安全ですね。

4. #N/Aを回避するための注意点と関数化のすすめ

VLOOKUPの#N/A対策は、単に`IsError`を書けば終わりではありません。

実務で安定させるには、検索値、検索範囲、列番号、完全一致の指定をセットで確認する必要があります。

特に列番号の指定ミスは見落としやすく、`tableRange`がA:Dなのに5列目を指定すると、#N/Aではなく別のエラーになります。

#N/Aだけを想定していると、こうした範囲外参照やシート指定ミスに気づきにくくなるんです。

安全性を高めるには、VLOOKUP処理を関数化しておくのも効果的です。

毎回同じような`IsError`判定を書くより、共通関数としてまとめておくと、コード量が減って、置き換え値のルールも統一できます。

たとえば、見つからない場合は空白を返す、または「該当なし」を返すといった仕様を1か所で管理できるんです。

“`vb
Function SafeVLookup( _
ByVal lookupValue As Variant, _
ByVal tableRange As Range, _
ByVal colIndex As Long, _
Optional ByVal notFoundValue As Variant = “” _
) As Variant

Dim result As Variant

If colIndex < 1 Or colIndex > tableRange.Columns.Count Then
SafeVLookup = CVErr(xlErrRef)
Exit Function
End If

result = Application.VLookup(lookupValue, tableRange, colIndex, False)

If IsError(result) Then
SafeVLookup = notFoundValue
Else
SafeVLookup = result
End If

End Function
“`

この関数を使うと、普通のマクロ内では次のように短く書けます。

見つからなかった場合に何を返すかを呼び出し側で指定できるので、処理の目的に合わせて使い分けられます。

“`vb
Sub UseSafeVLookup()

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets(“Sheet1”)

ws.Range(“G2”).Value = SafeVLookup( _
ws.Range(“F2”).Value, _
ws.Range(“A:D”), _
3, _
“該当なし” _
)

End Sub
“`

この関数では、列番号が検索範囲の列数を超えている場合に`CVErr(xlErrRef)`を返しています。

これは「参照が不正である」という意味のエラー値です。

見つからない場合は指定した代替値を返して、コードの指定自体が間違っている場合はエラー値として残すことで、データ上の未一致とコード上のミスを分けて考えられます。

すべてのエラーを空白にしてしまうと、マクロの不具合まで隠れてしまうので注意が必要ですね。

さらに安定させるなら、検索前にデータの整形ルールを決めておくといいですよ。

たとえば、社員番号や商品コードは文字列として統一する、前後の空白を削除する、全角半角をそろえる、検索範囲の左端列にキーを置く、といった基本を守るだけで#N/Aの発生は大きく減ります。

VLOOKUPは便利な関数ですが、データの状態に強く影響されるので、VBA側のエラー処理と表側のデータ整備を組み合わせることが大切です。

まとめ

Excel VBAでVLOOKUP関数を安全に実行するなら、`WorksheetFunction.VLookup`でいきなり呼び出すより、`Application.VLookup`の戻り値を`Variant`で受け取って、`IsError`で判定する書き方が扱いやすいです。

第4引数には`False`を指定して、完全一致で検索することも忘れないでくださいね。

検索値が見つからないことを異常終了ではなく普通の分岐として扱えば、#N/Aによる停止を防ぎながら、実務で使いやすい安定したVBAコードになります。

ぜひこの記事を参考に、エラーに強いVLOOKUPマクロを作ってみてください!

広告