【VBA】FindNextメソッドうまくいかないなら試してみて！セル範囲を全検索する方法（簡単、高速、エラーにも強い）

このページでわかること

指定したセル範囲のうち、
特定のセルを全取得する方法がわかります。
オリジナル関数です。
3つの関数をコピペするだけで簡単に扱えます。
FindNextメソッドよりも
①扱いが簡単
②高速
③エラーにも強い
FindNextメソッドで取得する方法もコーディングしているので
比較できます。
FindNextメソッドよりも簡単にコーディングできます。

こんにちは、hokkyokunです。

特定のセル範囲内に対し、
条件にあったセル位置を取得する方法として
Findメソッドを使う方が多いと思います。

そして複数のセルを取得するためには
FindNextメソッドを使う場合が多いでしょう。

実はFindメソッドやFindNextメソッドは扱いが難しく、
エラーも発生しやすい、ちょっと困ったメソッドです。

私も最初は使っていましたが、
今は全く使っていません。

代わりにオリジナル関数を作って
処理しています。

今回はFindNextメソッドに代わる方法を
ご提案させていただきます。

ちなみに
Findメソッドの代わりはこちらです。

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

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

Contents

FindNextメソッド

FindNextメソッドとは？

FindNextメソッドは
前回使用したFindメソッドの検索条件通りに
前回検索したセルの次のセルから検索を行うことができるメソッドです。

指定したセル範囲内に対し
条件にあうセルを全て検索するために使用します。

ただし、使い方に癖があります。
単発ではFindメソッドと同様に一つのセルしか返してくれません。

続けて使用することで
検索した隣のセルから再度検索を開始し、
範囲内のセルを全検索することができます。

正確に言うと
1回目はFindメソッド、
2回目からFindNextメソッドで検索というプログラムを書いていきます。

例えば、下記の図のようなケースで
「リン」が含まれるセルを全検索したい場合

一回目の検索（Findメソッド）で「C1」を取得
二回目の検索（FindNextメソッド）で「A5」を取得
三回目の検索（FindNextメソッド）で、「A1」を取得

なぜ一回目の検索が「A1」ではないのかですが、
Findメソッドの仕様で検索範囲の先頭（この場合はA1セル）は最後に検索するためです。

FindNextメソッドで連続して検索する方法（コード）

上記の例について具体的にコードを書いてみましょう

Sub test_findnext()

Dim Rng As Range '検索範囲
Dim fndRng As Range '検索して見つかったセル位置
Dim ffndRng As Range '検索して最初に見つかったセル位置

'検索範囲に対し、FindNextで検索
'最初に検索したセル位置を記録しておき、
'再び最初のセル位置に戻ってきたら処理終了

Set Rng = ThisWorkbook.Worksheets(1).Range("A1:C5")

'一回目の検索→C1を検索
Set fndRng = Rng.Find(what:="リン", lookat:=xlPart)
Set ffndRng = fndRng

'見つからなかったら処理終了
If ffndRng Is Nothing Then
    Exit Sub
End If

'二回目以降の検索→A5,A1の順で検索
Do
    Debug.Print fndRng.Address
    Set fndRng = Rng.FindNext(fndRng)
    
Loop Until fndRng.Address = ffndRng.Address


End Sub

Findメソッド、FindNextメソッドのデメリット

一見便利そうなFindメソッドとFindNextメソッドですが、
実は上級者向きの手法であったりします。

使い方が複雑

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

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

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

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

なんだこの仕様(笑)

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

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

遅い

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

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

下記で本ページで紹介しているオリジナル関数（Search_List関数）と
スピードチェックしているので、よかったら見てください。

また、Findメソッドも遅いです。

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

【VBA】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:=xlPart, _
            MatchByte:=False, _
            SearchOrder:=xlRows)

Findメソッドで見つからない場合の対策

以上のようなデメリットがあり、
実際には値はあるのに見つからないという事態は頻繁に発生します。

こちらで詳しく解説しています。

【VBA Findメソッド】あるのに見つからない場合に試すこと４選。エラー回避と代替手段このページでわかること Findメソッドは見つからない場合、続けて処理するとエラーが生じる可能性がある。→エラー回避方法を紹介 ...

なぜ見つからないのか、その原因も詳しく解説していますので
良かったら見てください。

FindNextメソッドよりも早く、簡単、エラーにも強い！オリジナル関数

セル範囲から一致するセルを全て取得する関数です。

オリジナル関数ですが、コピペで使用できます。
ただし、必ず注意書きをご参照してから試してください。
ファイルが壊れたりはしませんが、うまく動かない可能性があります。

関数名

関数名
Search_List ( Rng , keyWord , Whole )
引数
① Rng : 検索範囲（データ型はRangeオブジェクト)
② KeyWord : 検索する値（データ型はVariant）
③ Whole : 完全一致か部分一致か選択
　完全一致⇒True 部分一致⇒False
戻り値
Rangeオブジェクト　見つからなかった場合は「Nothing」

コード

コピペして使ってみて下さい。

ただし、注意書きは必ず見てください。

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

注意書き

Search_List関数を使うためには準備が必要です。

このプログラムは複雑な仕様となっているため
二つのサブ関数を使用しています。

Is_correct_array関数
set_add_Elm関数

これらの関数はオリジナルで作っています。
これらのコードもコピーしないと動きませんので、
下記に紹介しているコードを合わせてコピーしてください。

Is_correct_array関数

Is_correct_array関数は
配列がエラーを発生させる空の状態かどうかを判断する関数です。

コードはこちらです。

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】配列が空の状態のバリエーションと空の状態を判定する関数わかること VBAの配列が空の状態になる、そのバリエーションを解説動的配列で宣言すると配列が空という扱い。エラーになる静...

set_add_Elm関数

set_add_Elm関数は
配列にオブジェクトを追加する関数です。

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

基本的には配列に要素を追加する
add_Elm関数と同様ですが、オブジェクトに対応するよう
少し変えています。

add_Elm関数については
こちらに詳しく書いていますので見てください。

【VBA】配列に要素を追加する関数（任意の位置に対応可能）わかること配列に簡単に要素を追加できる関数のコードとその使い方こんにちはhokkyokunです。配列の関数をいくつ...

使い方

Search関数と同じ例でいきますが、
下記のように入力されているシートに対し
KeyWord=「リン」、部分一致で全検索します。

FindNextメソッドを使用した場合よりも
かなりコードがすっきりしていてわかりやすいと思います。

Sub test_search_list()
    Dim Rng As Range
    Dim results As Variant
    
    Set Rng = ThisWorkbook.Worksheets(1).Range("A1:Z5000")
    
    results = search_List(Rng, "リン", False)
    
    Dim result As Variant
    For Each result In results
        Debug.Print result.Address & " " & result.Value
    Next
    
End Sub

スピードチェック（FindNextメソッドVSオリジナル関数）

ルール

最後にオリジナル関数のスピードチェックをしてみます。

同じ表ですが、
以下のルールでFindNextメソッドとオリジナル関数で処理し、
その時間を計測してみます。

A1～C6の範囲内で「リン」が含まれるセル位置を取得
取得したセルの背景を黄色に塗り
色をクリアする
これを5000回繰り返す

FindNextメソッド

上記ルールを実行するためのプログラムを下記のように書きました。
スピード計測は5反復行います。

Sub speed_test_findnext()

Dim start, end_ As Date
Dim i, j As Long
Dim Rng As Range
Dim fndRng As Range
Dim ffndRng As Range

Debug.Print "これはFindNextです"

Set Rng = ThisWorkbook.Worksheets(1).Range("A1:C6")

For j = 1 To 5
    start = Now
    For i = 1 To 5000
        Set fndRng = Rng.Find(what:="リン", lookat:=xlPart)
        Set ffndRng = fndRng
        
        fndRng.Interior.Color = vbYellow
        fndRng.Interior.Color = xlNone
        
        Do
            Set fndRng = Rng.FindNext(fndRng)
            fndRng.Interior.Color = vbYellow
            fndRng.Interior.Color = xlNone
            
        Loop Until fndRng.Address = ffndRng.Address
    Next i
    end_ = Now
    Debug.Print Format(end_ - start, "hh:nn:ss")
Next j


End Sub

オリジナル関数（Search_List関数）

こちらもルールに則った処理をするプログラムを書きました。
スピードもそうですが、書いててめちゃくちゃ楽でした。

やっぱり、配列で返してもらえると後が楽(笑)

Sub speed_test_Search_List()

Dim Rng As Range
Dim arrs, arr As Variant
Dim start, end_ As Date
Dim i, j As Long

Debug.Print "これはSearch_List関数です"


Set Rng = ThisWorkbook.Worksheets(1).Range("A1:C6")

For j = 1 To 5
    start = Now
    For i = 1 To 5000
        arrs = search_List(Rng, "リン", False)
        
        For Each arr In arrs
            arr.Interior.Color = vbYellow
            arr.Interior.Color = xlNone
        Next
    Next i
    end_ = Now
    Debug.Print Format(end_ - start, "hh:nn:ss")
Next j


End Sub