【エクセルVBA】FindNextメソッドで指定範囲内の検索条件にヒットする値をすべて取得する方法

皆様こんにちは、ノグチです。

前回の記事では、指定した範囲内で値を検索するFindメソッドをご紹介しました。

【エクセルVBA】指定した範囲内から値を検索するFindメソッドとその使い方

エクセルシートに入力した値から、VBAのFindメソッドを使ってシート内を検索し、結果を返す方法をご紹介しています。Findメソッドは部分一致or全体一致、列方向or行方向など検索の条件を色々指定できて使い勝手が良いのでオススメですよ。

Findメソッドは、指定した条件で指定範囲内を検索してくれる、とっても便利なメソッド。

でも、このFindメソッド、範囲の中で1件でも検索にヒットするとそこで検索を終えてしまうのですよね。

探したい値は1件だけじゃないかもしれない…

Findメソッドと同じ条件で指定範囲の最後まで検索したい！

そんな時に使えるのが、Findメソッドとセットで使えるFindNextメソッド。

今回は、このFindNextメソッドで、検索に複数ヒットする値の取得方法をご紹介します。

前回のおさらい
FindNextメソッドの記述方法とパラメータ
1. FindNextメソッド単体では使えない
検索にヒットした値をすべてメッセージボックスに表示してみる
最後に

前回のおさらい

前回、Findメソッドを使って指定範囲内を検索したコードがこちら。

Dim rngSearch, varSearch
Dim myRange As Range
With ThisWorkbook

    varSearch = .Worksheets("請求書").Range("B5")
    Set myRange = .Worksheets("マスタ").Range("A1:A6")
    Set rngSearch = myRange.Find(What:=varSearch, LookAt:=xlPart)

    If Not rngSearch Is Nothing Then
        .Worksheets("請求書").Range("B5").Value = rngSearch
    Else
        MsgBox "該当する得意先はありません。"
    End If
End With
End Sub

そして請求書シートと

マスタシートがこちら。

請求書シートのB5セルに入力した値から、マスタシートの得意先リストを検索し、検索にヒットした得意先名称を請求書シートのB5セルに返す…というコードでした。

コードを見てみると、指定範囲内でFindメソッドによって最初にヒットした値をB5セルに返していますね。

そして指定した範囲のうち、最初にヒットした値以降のセルは検索されずに終わっています。

「探したい値が複数あるんだけど…」という場合、これでは困ってしまいます。

そんな時に使えるのがFindNextメソッドです。

FindNextメソッドの記述方法とパラメータ

FindNextメソッドは、Findメソッドで指定したRange型範囲の検索を、Findメソッドで指定したパラメータで、指定したセルから検索を続行してくれるメソッドです。

記述方法はこちら。

Range.FindNext(検索対象範囲内のセル)

戻り値はFindメソッドと同様、検索に値がヒットした場合はそのセルのRangeオブジェクトを、ヒットしなかった場合はNothingを返してくれます。

パラメータに指定したセルの次のセルから検索を続行するので、Findメソッドでヒットしたセルを指定しておけば、ヒットした次のセルから検索を続けてくれますよ。

検索対象範囲は、Findメソッドで指定した範囲を勝手に引き継いでくれますので、改めて指定する必要はありません。

FindNextメソッド単体では使えない

ちなみに、FindNextメソッドのパラメータは省略可能ですが、Findメソッドを記述しないでFindNextメソッドを使ってしまうと、実行時エラーにはならないものの、戻り値にNothingを返してくるだけで使い物になりませんので注意が必要です。

FindNextメソッドのパラメータは検索を続行するセルを指定できるのみで、検索したい値や検索範囲、検索の向きなどを指定できるものがありません。

よって、単体で使えないというのは頷けるのですが、「Findメソッドが無いよ」というメッセージくらい出してくれてもいいのでは…と思ってしまいます。

検索にヒットした値をすべてメッセージボックスに表示してみる

では、FindNextメソッドで指定した範囲内を全部検索して、検索にヒットした値をメッセージボックスに表示してみましょう。

コードはこちら。

Private Sub CustomerSearch()

Dim rngSearch As Range
Dim myRange As Range
Dim strMsg As String
Dim strAdr As String

With ThisWorkbook

    Set myRange = .Worksheets("マスタ").Range("A1:A6")

    Set rngSearch = myRange.Find(What:=.Worksheets("請求書").Range("B5"), LookAt:=xlPart)

    If rngSearch Is Nothing Then

        '1件もヒットしなかったらメッセージを表示する
        MsgBox "該当する得意先はありません。"

    Else
        '1件目を文字列にセット
        strMsg = strMsg & vbCrLf & rngSearch

        'ヒットした値のセルを退避
        strAdr = rngSearch.Address
        Do
            Set rngSearch = myRange.FindNext(rngSearch)
            If rngSearch Is Nothing Then
                Exit Do
            Else
                If strAdr <> rngSearch.Address Then
                    strMsg = strMsg & vbCrLf & rngSearch
                End If
            End If

        Loop While rngSearch.Address <> strAdr

        MsgBox strMsg
    End If

End With
End Sub

Do~LoopのWhileの後に、一番最初に検索にヒットしたセルのアドレスと次に検索にヒットしたセルのアドレスを比較していますね。

FindNextメソッドはマスタシートの最後のセルまで検索したら、指定した範囲の先頭に戻って検索を続けます。

先頭に戻ってから最初に検索にヒットしたセルは、Findメソッドで最初に検索にヒットしたセルと同じセルということになりますよね。

つまり、指定した範囲はFindメソッドとFindNextメソッドで検索を終えたということなので、そこで検索を終える為に、一番最初に検索にヒットした値のセルと最後に検索にヒットした値のセルを比較して、同じであれば検索のループを抜ける、という処理にしているのです。

さて、請求書シートに『株』を入力した場合、マスタシートの得意先リストには『株』の文字を持つ値が3つありますので、メッセージボックスに3件の得意先名が表示されるはずです。

早速実行してみましょう。