【VBA Findメソッド】あるのに見つからない場合に試すこと４選。エラー回避と代替手段

このページでわかること

Findメソッドは見つからない場合、
続けて処理するとエラーが生じる可能性がある。
→エラー回避方法を紹介
そもそもなぜ見つからないのか
①そもそも値がない
②検索条件を正しく設定していない
③前回の検索条件の設定を引っ張てしまっている
④検索する範囲がセル非表示
そもそもFindメソッドは上級者向き。デメリット3選
①使い方が複雑
②遅い
③設定が前回の処理に引っ張られる
代替手段
①Findメソッドの代替手段（検索結果を一つだけ返す関数）
②FindNextメソッドの代替手段（指定範囲から該当のセル全て返す関数）

こんにちは、hokkyokunです。
Findメソッドは便利なんですが、クセが強く、うまく検索してくれないってことがよくあります。

私が経験した原因をご紹介しますので、
もし検索できなくて困っていらっしゃいましたら参考にしてみてください。

Findメソッドに関していくつか記事を書いています。
よかったら見てやってください。

【Findメソッド】あわせて読みたい

Contents

Findメソッドは見つからないと実行時エラー91が発生する
- エラー回避方法（関数）
- エラー回避方法（コード）
そもそもなぜ見つからない？
（実は上級者向き）Findメソッドのデメリット3選
Findメソッドに変わる手法
- Findメソッドの代替手段（検索結果を一つだけ返す関数）
- FindNextメソッドの代替手段（指定範囲から該当のセル全て返す関数）
まとめ

Findメソッドは見つからないと実行時エラー91が発生する

FindメソッドはRangeオブジェクトを取得するメソッドです。
よって、何かの値が見つからない場合、戻り値として「Nothing」を取得するようになります。

これを気づかず、値を確認などすれば

エクセルさん

Nothingの値は取得できないよ！！

とエクセルさんに怒られます＝エラー発生

エラーを回避する方法は以下の通りです。

Findメソッドの戻り値が「Nothing」なのかそうでないのか
を判定

具体的には以下の関数もしくはコードを仕込んでおくと
エラー回避できます。

エラー回避方法（関数）

関数化させました。

Findメソッドで検索した結果
見つかった場合はTrue
見つからなかった場合はFalse
を返します。

Function Is_Find(ByVal rng As Range)
    If Not rng Is Nothing Then
        Is_Find = True
    Else
        Is_Find = False
    End If
End Function

使ってみます
A列をFindメソッドで「メロン」を検索かけてみます。
A列には「メロン」がありません。

Sub test1()

Dim rng As Range
Set rng = ThisWorkbook.Worksheets(1).Columns("A").Find(what:="メロン")

If Is_Find(rng) Then
    Debug.Print rng.Address
Else
    Debug.Print "見つかりません"
End If

End Sub

エラー回避方法（コード）

関数を使いたくない場合は
以下のようにコードを入れて
Findメソッドを使うと良いと思われます

If Not 〇〇 Is Nothing Then
　　見つかった時の処理
Else
　　見つからなかった時の処理
End If

Sub test1()

Dim rng As Range
Set rng = ThisWorkbook.Worksheets(1).Columns("A").Find(what:="メロン")


'以下のように　「 If Not 〇〇 Is Nothing Then 」を入れて条件分岐させる
If Not rng Is Nothing Then
    Debug.Print rng.Address
Else
    Debug.Print "見つかりません"
End If

End Sub

やっていることは同じなのでどちらでもいいですが、
これくらいであればコードを差し込んでもいいかもしれないですね。

そもそもなぜ見つからない？

見つからないとエラーが発生することはわかりました。
また、エラーを回避する方法も分かったかと思います。

ただ、我々はエラーを回避したいのではなく、
Findメソッドで値を見つけたいのだと思います。

なぜ見つからないのでしょうか？
想定されるケースは次の4つと思われます。

そもそも値がない
検索条件を正しく設定していない
つまりうまくFindメソッドを扱えていない
前回の検索条件の設定を引っ張てしまっている
検索する範囲がセル非表示

個人的には③が凶悪です(笑)
私は初心者のころ、このせいでかなり苦しみました。

そもそも値がない（エラー発生）

状況

元も子もないですが、
値がそもそもないこともよくあります。

繰り返しの図ですが、
下記の図で「メロン」を検索しても当たり前ですが見つかりません。

解決策

意外にこれを解決するのは面倒だったりします。
なぜなら、

本当に値がないのか
Findメソッドを上手く使えていないのか
セルが隠れているのか

これらを判定しなければいけませんが
②と③は確認する範囲が絞られているのに対し、
本当にセル範囲にないかどうかはぱっと見でわかりません。

私のおすすめ方法は以下のプログラムで調べることです。

Function Search(ByVal Rng As Range, ByVal keyWord As Variant)
    Dim r As Range
    
    For Each r In Rng
        If InStr(r.Value, keyWord) > 0 Then
            Search = True
            Exit Function
        End If
    Next
    Search = False
End Function

少し図を変えて「10」「9.99」など整数や小数点ありの数字も入力してみました。

「ミカン」「10」「9.99」は入力されているのでTrue
「メロン」「8.5」はないのでFalseとなりました。

Sub test2()
    Dim Rng As Range
    
    Set Rng = ThisWorkbook.Worksheets(1).Range("A1:Z5000")
    Debug.Print Search(Rng, "メロン")
    '>>False
    
    Debug.Print Search(Rng, "ミカン")
    '>>True
    
    Debug.Print Search(Rng, 10)
    '>>True
    
    Debug.Print Search(Rng, 9.99)
    '>>True
    
    Debug.Print Search(Rng, 8.5)
    '>>False
    
End Sub

検索条件を正しく設定していない

状況

Findメソッドには引数がたくさんあります。

Findメソッドは
値の「完全一致」や「部分一致」の切り替えが引数だけでできます。
すごく便利ですよね。

ただその分、使い方が複雑になってしまい、
自分の意図していない方法で検索しているかもしれません。

例えば部分一致で検索したいのに、完全一致で検索していた場合

Sub test3()

Dim Rng As Range
Dim ws As Worksheet
Set Rng = ThisWorkbook.Worksheets(1).Columns("A").Find(what:="リン", LookAt:=xlWhole)

If Not Rng Is Nothing Then
    Debug.Print Rng.Address
Else
    Debug.Print "見つかりません"
End If

End Sub

わざわざこんなことしないでしょ

普通に使っている分には部分一致と完全一致を分けることは少ないかもしれません。

しかし、どこかで使っていたコードをそのままコピペしてしまうと
意図していない設定になっている可能性があります。

解決策

引数は正しく理解し、使用しましょう。

引数	役割
What	検索する値
LookIn	検索の対象を数式(xlFormulas）値(xlValues) コメント(xlComments) の中から選択
LookAt	検索対象を完全一致(xlWhole) 部分一致(xlPart) から選択
MatchCase	ブール型大文字と小文字を区別する（True) 区別しない(False)
MatchByte	ブール型半角と全角を区別する(True) 区別しない(False)
SearchFormat	ブール型書式を検索する(True) 検索しない(False)

上記の表を確認していただき、
引数を正しく指定してください。

Findメソッドの設定が過去の設定に引っ張られている

状況

これが最も初心者キラーになる要因じゃないでしょうか？
マイクロソフトさんはどうしてこの仕様にしたのでしょうか(笑)

LookIn、LookAt、SearchOrder、および MatchByte の設定は、このメソッドを使用するたびに保存されます
Microsoft公式ドキュメント

引数の軽いおさらいです。

LoolInは検索対象を数式、値、コメントの中から選択
LookAtは完全一致、部分一致か選択
MatchByteは半角と全角を区別するかしないか

SearchOrderは検索順序を横順か縦順かを選択ですが、
あまり使う機会はないでしょう。

少し実験してみます。
Findメソッドはコメントの検索もできます。

そこで下記のような図で
①「リンゴのコメント」のコメントを検索
②そのあとに「ミカン」という値を検索
してみます。

Sub test4()

Dim Rng As Range
Dim ws As Worksheet
Set Rng = ThisWorkbook.Worksheets(1).Columns("A").Find(what:="リンゴのコメント", LookIn:=xlComments)

'一回目は「リンゴのコメント」とコメントが書かれた位置を検索
If Not Rng Is Nothing Then
    Debug.Print Rng.Address
Else
    Debug.Print "リンゴ見つかりません"
End If

'二回目は「ミカン」で検索。引数指定しなければデフォルトで値を検索すると思いきや…
Set Rng = ThisWorkbook.Worksheets(1).Columns("A").Find(what:="ミカン")
If Not Rng Is Nothing Then
    Debug.Print Rng.Address
Else
    Debug.Print "ミカン見つかりません"
End If

End Sub

解決策

まさに初心者キラー(笑)

Findメソッドは引数次第でいろいろ検索できるのがいいところですが

こんないろいろ便利な機能があったら使いたくなりますよね？
でも、

Findメソッドは
使ったら、次に使用するときは
引数を意識して戻さないといけません。

以下のようなコードを入れて戻すと検索ができるようになります。

Dim Rng As Range
Set Rng = Range("A1").Find(what:="", _
            LookIn:=xlValues, _
            LookAt:=xlPart, _
            MatchByte:=False, _
            SearchOrder:=xlRows)

さっきの例で試してみましょう。

Sub test5()

Dim Rng As Range
Dim ws As Worksheet
Set Rng = ThisWorkbook.Worksheets(1).Columns("A").Find(what:="リンゴのコメント", LookIn:=xlComments)

'一回目は「リンゴのコメント」とコメントが書かれた位置を検索
If Not Rng Is Nothing Then
    Debug.Print Rng.Address
Else
    Debug.Print "リンゴ見つかりません"
End If

Set Rng = ThisWorkbook.Worksheets(1).Columns("A").Find(what:="", _
                                                    LookIn:=xlValues, _
                                                    LookAt:=xlPart, _
                                                    MatchByte:=False, _
                                                    SearchOrder:=xlRows)

'二回目は「ミカン」で検索。引数指定しなければデフォルトで値を検索すると思いきや…
Set Rng = ThisWorkbook.Worksheets(1).Columns("A").Find(what:="ミカン")
If Not Rng Is Nothing Then
    Debug.Print Rng.Address
Else
    Debug.Print "ミカン見つかりません"
End If

End Sub

ちゃんと両方とも見つかりました。

検索する範囲がセル非表示

状況

あまり知られていませんが、
非表示の部分に関してはFindメソッドは検索してくれません。

実験してみます。
この表からバナナを検索してみます。

Sub test()
Dim srcRng, fndRng As Range

'検索する範囲
Set srcRng = ActiveSheet.Columns("A")

'srcRngからバナナを検索
Set fndRng = srcRng.Find(what:="バナナ", LookIn:=xlValues, lookat:=xlWhole)

Debug.Print fndRng.Address

End Sub

うまく検索できました。

次にバナナの行（3行）を非表示にしてみます。

実行してみると。。

エラーが出ます。
Findメソッドで見つからなかったようです。

解決策

対策は再表示をさせることです。

下記のプログラムでは
①いったん全てHiddenプロパティで非表示を解除し、
②該当の箇所を非表示にしています。

Sub test()
Dim srcRng, fndRng As Range

'検索する範囲
Set srcRng = ActiveSheet.Columns("A")

'行全部非表示解除
Rows.Hidden = False

'srcRngからバナナを検索
Set fndRng = srcRng.Find(what:="バナナ", LookIn:=xlValues, lookat:=xlWhole)

'バナナの行だけ非表示
fndRng.Rows.Hidden = True


Debug.Print fndRng.Address

End Sub

一応、バナナの行をもう一度非表示にしましたが
これを全ての行で元通りにするのは大変ですね。

出来ないわけじゃないですが、
面倒だし、時間もかかるしで
正直Findメソッドで非表示まで検索するのは現実的ではないかもしれません。

（実は上級者向き）Findメソッドのデメリット3選

FindメソッドはVBA初心者にとっては理解しやすく
使用しがちなのですが、実は上級者向きの使用となっています。

上記のような見つからないケースを加味して、
改めて整理すると以下のような理由となります。

そもそもメソッドの引数が多く、難しい
遅い
検索条件が前回の条件に引っ張られる

③はなかでも厳しいですね。
エクセルの状態によって動作が不安定になるのは
プログラム的に脆弱です。

①使い方が複雑

これはFindメソッドの仕様の方ですが、
下記のような特徴があります。

指定されたセル範囲の検索は
検索範囲の先頭のセルを最後に検索します。

めちゃくちゃ非直感的仕様ですね。

メカニズムとしては
検索範囲の最初のセルの次から検索を開始する
ようになっていますが、正気の沙汰ではない(笑)

なんだこの仕様(笑)

また、引数もわかりにくく、
lookInとlookAtという引数があるのですが、

どっちがどっちかすぐ忘れます(笑)
こんなことに貴重な頭のリソースは割けません(笑)

②遅い

Findメソッドは遅いことで有名です。

業務に支障をきたすレベルでめちゃくちゃ遅いわけではないですが
プログラムによっては数秒かかる場合もあります。

Findメソッドの代替手段をご紹介していますが、
スピードチェックしています。

【VBA】Findメソッドよりも簡単、早い！高速でセル位置を検索、取得する方法。完全一致、部分一致対応。複数セル取得対応。わかること Findメソッドよりも高速で操作が簡単、エラーにも強い方法を紹介します。オリジナル関数ですが、コピペで使えます。...

これを見ていただければFindメソッドが遅いことが実感できると思います。

③設定が前回の処理に引っ張られる

謎仕様パート２です。
Findメソッドは前回の検索条件を踏襲します。

例えば、部分一致処理で検索した後、
自分はそのつもりがなくても、次の検索も部分一致処理で検索します。

別にいいじゃん

セル範囲全て同じ条件で検索は普通でしょ

プロシージャ内での処理ならいいんですが、
プロシージャ終了(=プログラム終了)しても次回にも設定が反映されます。

下記の図のような表で試してみます。

①まず「リン」を部分一致検索します。

Sub test__()

Debug.Print ThisWorkbook.Worksheets(1).Range("A1:A7").Find("リン", LookAt:=xlPart).Address

End Sub

範囲（A1:A7）の先頭のセルは最後に検索されるので
ヒットするのはA3（リンゴ）です。

②いったんこのプロシージャを終わらせて
次に「リン」で完全一致（A7セルを狙いたい）をかけたいと思います。
全検索はデフォルトでは特に引数指定しなくても大丈夫なはずですが…

Sub test_()

Debug.Print ThisWorkbook.Worksheets(1).Range("A1:A7").Find("リン").Address

End Sub

ヒットしたのはA3セル（リンゴ）でした。
完全一致で検索かけたつもりが部分一致となっていました。

一回目に部分一致で検索をかけると
二回目はしっかり指定をしないと
部分一致（一回目と同じ条件）で検索を書けてしまう

対策は下記のようなコードを検索終了後に差し込むことです。
デフォルトの仕様で一回検索をかけてあげます。

Dim Rng As Range
Set Rng = Range("A1").Find(what:="", _
            LookIn:=xlValues, _
            LookAt:=xlWhole, _
            MatchByte:=False, _
            SearchOrder:=xlRows)

最初は使っていたが、今は全く使っていない

偉そうなことを言っておきながら
私もFindメソッドをよく使っていました。

ですが、今は全く使っていません。

そもそもFindメソッドを使う理由は何か特定の値を検索し、
取得するために使います。

それであればVBAの基本的な文法や簡単な関数を組み合わせることで
高速かつエラーの発生しない方法をとることができます。

Findメソッドに変わる手法

Findメソッドの最大のネックはデフォルトの設定が外部環境で変わることです。

一方、Findメソッドの目的は「一致する値の位置取得」です。

それであれば、Findメソッドに頼るのではなく、
「一致する値に位置を取得する」関数を作って対応してしまいます。

長くなるので、別記事にしましたが
コードだけ紹介いたします。

Findメソッド以外にもFindNextメソッドの代替手段も開発しています。

FindNextメソッドは
セル範囲内の特定の条件のセルを全て取得するのに使いますが
使い方は複雑です。
これも簡単な方法で取得できるように代替手段をご紹介します。

使い方は各ページを見て頂ければと思います。
（クリックすると各ページに飛びます）

Findメソッドの代替手段（検索結果を一つだけ返す関数）

Findメソッドと同様に検索範囲の最初のセル範囲を返す関数です。

Function Search(ByVal Rng As Range, ByVal keyWord As Variant, ByVal Whole As Boolean)
'   引数：
'       Rng:検索範囲 (例) ActiveSheet.Range ("A1:Z500")
'       KeyWord:検索する値 (例) "リンゴ",10,7.85
'       Whole:完全一致→True 部分一致→False (例)True
'   戻り値
'       Rangeオブジェクト 見つからなかった場合は「Nothing」

'   検索範囲内の「KeyWord」を検索する関数です。
'   検索範囲を一つずつ判定し、最初に一致したセル範囲を返します。
'   完全一致と部分一致対応できます。
'   非表示のセルも検索かけます
'   検索結果はセル範囲一つだけです。複数の場合が良ければ「search_List」関数を使ってください


    Dim r As Range
    
    '完全一致
    If Whole Then
        For Each r In Rng
            If r.Value = keyWord Then
                Set Search = r
                Exit Function
            End If
        Next
        
    '部分一致
    Else
        For Each r In Rng
            If InStr(r.Value, keyWord) > 0 Then
                Set Search = r
                Exit Function
            End If
        Next
    End If
    
    '見つからなかった時の処理
    '適宜変更してもらって構いません
    Set Search = Nothing
            
End Function

使い方はこちら

FindNextメソッドの代替手段（指定範囲から該当のセル全て返す関数）

FindNextメソッドのように
指定範囲で一致したセルを全て返す関数です。

この関数は一回の処理で該当するすべてのセル位置を
配列で返してくれます。

プログラムが複雑になるので
一つのメイン関数（Search_List関数）
二つのサブ関数（set_add_Elm関数、Is_correct_array関数）
を利用しています。

使用する場合は全てコピペして使用してください。

使用方法はこちらを確認してください。

Function search_List(ByVal Rng As Range, ByVal keyWord As Variant, ByVal Whole As Boolean)
'   引数：
'       Rng:検索範囲 (例) ActiveSheet.Range ("A1:Z500")
'       KeyWord:検索する値 (例) "リンゴ",10,7.85
'       Whole:完全一致→True 部分一致→False (例)True
'   戻り値
'       配列 見つからなかった場合は「空の配列」

'   検索範囲内の「KeyWord」を検索する関数です。
'   検索範囲を一つずつ判定し、一致したセル範囲を全て配列に含めて返します。
'   完全一致と部分一致対応できます。
'   非表示のセルも検索かけます
'   配列に追加する関数「set_add_Elm」関数、「Is_correct_array」関数を使っています。
'   ↑の関数は当ブログで解説していますので良かった見てください。


    Dim r As Range
    
    '完全一致
    If Whole Then
        For Each r In Rng
            If r.Value = keyWord Then
                Call set_add_Elm(search_List, r)
            End If
        Next
        
    '部分一致
    Else
        For Each r In Rng
            If InStr(r.Value, keyWord) > 0 Then
                Call set_add_Elm(search_List, r)
            End If
        Next
    End If
    
    '見つからなかった時の処理
    '適宜変更してもらって構いません
    If Not Is_correct_array(search_List) Then
        search_List = Array()
    End If
End Function

Function set_add_Elm(ByRef arrs As Variant, ByVal elm As Object)
    Dim num As Long
    
    'Is_correct_array関数で配列がエラーを起こす空の状態かどうか判定
    
    'エラーを起こす空の状態
    If Not Is_correct_array(arrs) Then
    
        '要素数が一つ＝「0」で宣言
        ReDim arrs(0)
        Set arrs(0) = elm
    
    'エラーを起こす空の状態ではない
    Else
        num = UBound(arrs)
        ReDim Preserve arrs(num + 1)
        Set arrs(num + 1) = elm
    End If
End Function

Function Is_correct_array(ByVal arrs As Variant)
    Dim a As Long
    
    'なんでもいいが、エラーを生じさせる
    On Error GoTo err
    a = UBound(arrs)

    'エラーが生じたときエラー番号で9か13の場合はFalse
err:
    If err.Number = 9 Or err.Number = 13 Then
        Is_correct_array = False
    Else
        Is_correct_array = True
    End If
    
End Function

使い方はこちら

【VBA】FindNextメソッドうまくいかないなら試してみて！セル範囲を全検索する方法（簡単、高速、エラーにも強い）このページでわかること指定したセル範囲のうち、特定のセルを全取得する方法がわかります。オリジナル関数です。3つの関数をコピ...

まとめ

いかがでしたでしょうか。

まとめ

Findメソッドは見つからない場合、
続けて処理するとエラーが生じる可能性がある。
→エラー回避をしっかりしましょう。
　戻り値が「Nothing」かどうかを判定させることがコツ
そもそもなぜ見つからないのか
①そもそも値がない
②検索条件を正しく設定していない
③前回の検索条件の設定を引っ張てしまっている
④検索する範囲がセル非表示
そもそもFindメソッド（or FindNextメソッド）を
使う必要ある？
代替手段（オリジナル関数）
①Search関数
②Search_Elm関数