エクセルVBA｜FindメソッドやInStr関数で文字列を検索する

エクセルで作業しているときにこんなことありませんか？

• 「探しているセルがなかなか見つからない。」

• 「特定の文字が含まれるデータだけを取り出したい。」

　こんなときはエクセルの検索機能を使う人が多いと思います。
エクセルを使いなれている人からすれば当然のような話ですが、でもエクセルが不慣れだったり、苦手な人はそもそも検索ができることを知らない人もいますよね。

　今回はそんな便利な検索機能をVBAマクロで作ってみるというお話。
もしかしたら、あなたが作ったマクロで部署やチーム全体の作業効率が上がるかもしれないので、こちらの記事を参考にぜひためしてみてください。

検索マクロにはFindメソッドやInStr関数がつかえる

　VBAで文字列やセルを検索するときには、FindメソッドやInStr関数を使うと便利です。　
これらは、エクセルのシート上のセルや文字列で指定したものが含まれているかどうか、またその位置はどこかを調べることができるメソッドや関数です。

しかし、これらのメソッドや関数は複数存在しており、それぞれ特徴や使いかたが異なります。
これらを適切に使い分けることができれば、膨大なデータの中かから効率的に検索処理を行うことができます。

　この記事では、FindメソッドとInStr関数の基本的な使いかたから応用まで解説します。
また、それぞれのメソッドや関数の違いと使い分けについても説明していきますので実務や作業で、VBAを使ってすばやく正確な手段として文字列やセルを検索する方法を身につけてください。

PR

Findメソッド

　Findメソッドは、Excelシート上のあるセル範囲の中で指定したデータを含むセルを検索するメソッドです。
つまり、探したい文字列が含まれるものを探し出してくれる処理や機能を持つものです。

　Excelでは、シート上にあるデータを素早く見つけたり、置換したりするために、「検索と置換」の機能を利用ができます。この機能は、ホームタブの「編集」グループにある「検索と選択」から開くことができます。

　Findメソッドは、Rangeオブジェクトのメソッドで、ワークシート操作の「検索と置換」 の 「検索」 の機能をVBAで実装するために使うものです。

　Excelの機能は自分で操作する必要がありますが、この機能をVBAをつかったマクロで自動化したいときに使用できるものがFindメソッドです。

Findメソッドの使いかた

　Findメソッドの書きかたと基本的な使いかたを説明します。

Findメソッドの書きかた

Findメソッドは以下のように書きます。

Object.Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)

　Objectにはセル範囲のRangeオブジェクトを指定します。

　このメソッドを実行して検索したデータがみつかった場合は、見つかったセルのRangeオブジェクトを返します。
一方、検索したデータを含むセルが存在しない場合にはNothingを返します。

Findメソッドの引数

　Findメソッドは引数で検索条件を指定します。引数が多いのでそれぞれの引数の以下の表を参考にしてください。

Findメソッドの引数まとめ

※引数のWhatは必ず指定する必要があります。
※Objectで指定するセル範囲の2番目のセルから検索を始めます。

引数	省略	定数	値	説明
What	不可	–	–	検索するデータを指定
After	可	–	–	検索を開始するセルを指定します。検索範囲内の1つのセルを指定します。 指定したセルの次のセルから検索を開始します。なお、Afterを指定しない場合は範囲内の左上から検索が開始されます。
LookIn	可	xlFormulas	-4123	検索対象を数式に指定
		xlValues	-4163	検索対象を値に指定
		xlComents	-4144	検索対象をコメント文に指定
LookAt	可	xlPart	2	一部が一致するセルを検索
		xlWhole	1	全部が一致するセルを検索
SearchOrder	可	xlByRows	1	検索する方向を列で指定します。 範囲内の列を下方向に検索し、最下行までいくと次の列の検索を開始します。
		xlByColumns	2	検索する方向を行で指定します。 範囲内の行を横方向に検索し、最右列までいくと次の行の検索を開始します。
SearchDirection	可	xlNext	1	順方向で検索(デフォルトの設定)
	可	xlPrevious	2	逆方向で検索
MatchCase	可	True	–	大文字と小文字を区別
		False	–	区別しない(デフォルトの設定)
MatchByte	可	True	–	半角と全角を区別する
		False	–	区別しない(デフォルトの設定)
SearchFormat	可	True	–	範囲内のセルの書式(フォントスタイルやサイズ、背景色など)を区別します。 ただし、検索を実行するためには「FindFormatプロパティ」を別途指定しておく必要があります。
		False	–	範囲内のセルの書式を区別しません。(デフォルトの設定)

Findメソッドをつかったサンプルマクロ

　Findメソッドをつかったサンプルコードを紹介します。
具体例として、以下の表から特定の文字列が入力されたセルを検索するマクロとなります。

  Copy
Option Explicit

Sub Findメソッド1()

    Dim 検索範囲 As Range '検索する範囲を代入する変数を宣言する
    Dim 結果 As Range '結果を代入する変数を宣言する
    Dim 検索文字列 As String '検索文字列を代入する変数を宣言する
    
    With Sheet1
        '検索範囲を変数に代入する
        Set 検索範囲 = .Range("A1:E11")
        
        '検索文字列を変数に代入する
        検索文字列 = Application.InputBox("検索文字列を入力してください。", "検索文字列を入力", "東京都")
        
        '検索結果を変数に代入する
        Set 結果 = 検索範囲.Find(検索文字列)
        
        '検索ヒットしたときとしなかったときで処理を分岐する
        If 結果 Is Nothing Then
            Debug.Print 検索文字列 & " が入力されたセルはありませんでした。"
        Else
            Debug.Print 結果.Address
        End If
    
    End With

End Sub

05行目から07行目

　Findメソッドの引数を代入する変数を宣言します。変数名はそれぞれの引数としての役割どおり検索範囲、結果、検索文字列としています。

11行目

　Range型変数の検索範囲にシート1のA1セルからE11セルの範囲を代入します。

14行目

　文字列型変数の検索文字列にインプットボックスで入力された文字列を代入します。

17行目

　Findメソッドの引数として、検索範囲と検索文字列を指定し、実行結果をRange型変数の結果で受け取ります。

20行目から24行目

　結果に代入されたものがNothingであったときは、見つからなかった旨のメッセージを表示します。一方、検索文字列に一致する文字列が見つかったときは変数の結果に代入された範囲の位置を返します。

結果の位置を返すために、Address プロパティを利用しています。

　このサンプルマクロのFindメソッドでは、必ず指定する引数の”検索するデータ”のみを指定しています。
ほかの省略できる引数はなにも指定していません。

　つづいて一部の引数を指定したマクロも見てみましょう。サンプルマクロのFindメソッドを少しだけ書きかえてみます。

  Copy
Option Explicit

Sub Findメソッド2()

    Dim 検索範囲 As Range '検索する範囲を代入する変数を宣言する
    Dim 結果 As Range '結果を代入する変数を宣言する
    Dim 検索文字列 As String '検索文字列を代入する変数を宣言する
    
    With Sheet1
        '検索範囲を変数に代入する
        Set 検索範囲 = .Range("A1:E11")
        
        '検索文字列を変数に代入する
        検索文字列 = Application.InputBox("検索文字列を入力してください。", "検索文字列を入力", "東京都")
        
        '検索結果を変数に代入する(引数で逆方向で検索、大文字と小文字を区別しない)
        Set 結果 = 検索範囲.Find(検索文字列, SearchDirection:=xlPrevious, MatchCase:=False)
        
        '検索ヒットしたときとしなかったときで処理を分岐する
        If 結果 Is Nothing Then
            Debug.Print 検索文字列 & " が入力されたセルはありませんでした。"
        Else
            Debug.Print 結果.Address
        End If
    
    End With

End Sub

　最初のマクロからコードを変更したところであるFindメソッドの引数の部分だけを抜き出して紹介します。

17行目

指定した引数が正しく動作するのであれば、SearchDirection:=xlPreviousにしたことで検索の方向が逆となります。
さらに、MatchCase:=Falseを指定したことで大文字と小文字を区別しない。となります。

以下で実行結果をみてみましょう。

　サンプルマクロ「Sub Findメソッド1」の実行結果と比べて、検索文字列に”オレンジ”を入力したときのみです。

「Sub Findメソッド1」の実行結果は”$A$4“でしたが、こちらの「Sub Findメソッド2」では、SearchDirection を指定したため、末尾から検索を開始して最初にみつかったセルのアドレスの”$C$11“を実行結果として返しています。

Findメソッドの戻り値

　Findメソッドは、検索範囲のなかで検索文字列が最初に見つかったのセルを表すRangeオブジェクトが戻り値です。

サンプルコードにおいて”北海道“が入力されたセルは、検索範囲のセルの中に1つしかありませんので該当するものを特定できますが、”オレンジ“を入力した場合は、検索で1番最初にみつかったセルのアドレスが返されていることがわかります。

つまりFindメソッドで検索文字列を探す場合、範囲内に複数個の一致したセルがある場合は対応ができません。
複数セルが一致するような場面では、FindNextやFindPreviousという他のメソッドを使います。

　FindNextやFindPreviousメソッドは、Findメソッドと組み合わせて使うことで、検索文字列が複数存在したときでも順番に検索していくことができます。

　ここからは、FindNextやFindPreviousメソッドについて触れていきます。

PR

FindNextメソッド

　ExcelのVBAでセル範囲内の条件に一致するセルを検索する方法として、Findメソッドを紹介しましたが、Findメソッドは、最初に一致するセルを見つけることができるメソッドですが、条件に一致するセルが複数存在した場合は最初に見つかったセルのみしか見つけることができませんでした。

複数のセルを返す場合はFindNextメソッドや、このあと紹介するFindPreviousメソッドを使う必要があります

　FindNextメソッドは、Findメソッドによって開始された検索を継続して次の検索を実行します。

　ここでは、FindNextメソッドの使いかたや注意点について解説します。

FindNextメソッドの基本的な使いかた

Object.FindNext (After)

　FindNextメソッドを使う前に、必ずFindメソッドで最初の検索を行う必要があります。

　引数の After は、指定したセルの下方 (行のときは右、列のときは下) のセルから検索を開始します。
この引数を省略すると、前回の検索結果から続けての検索をします。

　FindNextメソッドは、一致するセル (Rangeオブジェクト) を返します。
一致するセルが見つからなかった場合はNothingを返します。

FindNextメソッドをつかったサンプルマクロ

　FindNextメソッドのサンプルコードを書いてみます。

先に紹介したFindメソッドでも利用したデータをつかって検索をしていきます。

  Copy
Option Explicit

Sub FindNextメソッド1()

    Dim 検索範囲 As Range '検索する範囲を代入する変数を宣言する
    Dim 結果 As Range '結果を代入する変数を宣言する
    Dim 検索文字列 As String '検索文字列を代入する変数を宣言する
    
    With Sheet1
        '検索範囲を変数に代入する
        Set 検索範囲 = .Range("A1:E11")
        
        '検索文字列を変数に代入する
        検索文字列 = Application.InputBox("検索文字列を入力してください。", "検索文字列を入力", "東京都")
        
        'Findメソッドで検索結果を変数に代入する
        Set 結果 = 検索範囲.Find(検索文字列, LookIn:=xlValues)
        
        '検索した文字列があるセルのアドレスを表示する
        Debug.Print 結果.Address
        
        'FindNextメソッドでFindメソッドの結果を引数に指定した戻り値を変数に代入する
        Set 結果 = 検索範囲.FindNext(結果)'Findメソッドの検索結果を引数に指定
        
        '検索した文字列があるセルのアドレスを表示する
        Debug.Print 結果.Address
        
        'FindNextメソッドでFindメソッドの結果を引数に指定した戻り値を変数に代入する
        Set 結果 = 検索範囲.FindNext(結果)'FindNextメソッドの結果を引数に指定
        
        '検索した文字列があるセルのアドレスを表示する
        Debug.Print 結果.Address
    
    End With

End Sub

23行目と29行目

　FindNextメソッドをつかって検索をします。引数の結果には直前の検索結果で返ってきたRangeオブジェクトを指定しています。具体的には23行目では17行目のFindメソッドで返ってきた結果、29行目では23行目のFindNextメソッドで返ってきた結果のRangeオブジェクトです。

　変数結果がFindNextメソッドの引数(After)に代入されたとき、検索範囲全体のうち、After以降のセルを検索するイメージです。

実行結果を確認してみましょう。

　Findメソッドで検索でセル”$A$4“の、次のFindNextメソッドの検索で見つかったセルのアドレス”$C$4“と”$C$11“返すことができました。

　しかし、これでは検索文字列に一致するセルの数だけ、FindNextメソッドの処理を繰り返し書いておく必要があります。

通常、なにかしらの文字列やデータを検索をするときに、条件に一致するセルの個数を知った上で機能を利用することは稀だと考えますので、このままでは使い勝手が良いとは言えません。

　それでは、このサンプルマクロを改良して実務的に使い勝手の良いものにしてみましょう。

　それでは、くり返し文をつかって、検索文字列に該当するセルのアドレスをすべて表示できるようサンプルマクロを書きかえてみます。

このサンプルマクロのくり返し文では、Do～Loop を使って条件を指定して、検索文字列に一致するセルのアドレスをすべてを取り出します。

FindNextメソッドで複数のセルを検索するサンプルマクロ

  Copy
Option Explicit

Sub FindNextメソッド2()

    Dim 検索範囲 As Range '検索する範囲を代入する変数を宣言する
    Dim 結果 As Range '結果を代入する変数を宣言する
    Dim 初回結果 As String
    Dim 検索文字列 As String '検索文字列を代入する変数を宣言する
    
    With Sheet1
        '検索範囲を変数に代入する
        Set 検索範囲 = .Range("A1:E11")
        
        '検索文字列を変数に代入する
        検索文字列 = Application.InputBox("検索文字列を入力してください。", "検索文字列を入力", "東京都")
        
        '検索結果を変数に代入する
        Set 結果 = 検索範囲.Find(検索文字列, LookIn:=xlValues)
        
        '検索ヒットしたときとしなかったときで処理を分岐する
        If 結果 Is Nothing Then '検索にヒットしない場合
            Debug.Print 検索文字列 & " が入力されたセルはありませんでした。"
        Else '検索にヒットした場合
            初回結果 = 結果.Address '最初にヒットしたセルのアドレスを変数に代入する
            Do
                Debug.Print 検索文字列 & " は" & 結果.Address & " です。" '結果を表示する
                Set 結果 = 検索範囲.FindNext(結果) 'FindNextメソッドによる次の検索
            Loop While 結果.Address <> 初回結果 '繰り返し条件：最初にヒットしたセルのアドレスと現在のセルアドレスが異なる間は繰り返す
        End If
    
    End With

End Sub

24行目

　7行目で宣言した変数初回結果にFindメソッドで見つかったセルのアドレスを代入します。
これは、後のDo~Loop文の条件式に利用して無限ループを回避するための処理です。

25行目から28行目

　Do~Loop文でくり返し処理にします。
くり返す条件はFindNextメソッドで見つかったセルのアドレスが、変数初回結果と同じになるまでです。これにより2回目以降の検索で見つかったセルのアドレスが、最初に見つかったセルのアドレスと同じになった時点で処理は終了することになります。

繰り返し処理の詳しい解説は、こちらの記事をあわせて参考にしてください。

　Findメソッドによる検索の結果は”$A$4“を変数の初回結果に代入しているため、FindNextメソッドで”$A$4“が返ってくるとくり返しが終了します。

これで検索文字列が含まれているセルのアドレスをすべて抽出することができました。

FindPreviousメソッド

　FindNextメソッドと同じようなものとして、FindPreviousメソッドも存在します。

　FindPreviousメソッドは、Findメソッドで開始された検索を続行します。
つまり、FindPreviousメソッドもまたFindメソッドと組み合わせて使うメソッドです。

　FindPreviousメソッドは、一致するセル (Rangeオブジェクト) を返します。
一致するセルが見つからなかった場合はNothingを返します。

FindPreviousメソッドの基本的な使いかた

Object.FindPrevious (Before)

　FindPreviousメソッドを使う前に、必ずFindメソッドで最初の検索を行う必要があります。

　引数 Before に指定したセルの上方 (行のときは左、列のときは上) のセルから検索を開始します。 この引数を省略すると、前回の検索結果から続きで検索します。

　メソッドがこのセルに折り返されるまで、指定したセルは検索されません。 引数が指定されていない場合、検索は範囲内の左上のセルの前に開始されます。

FindPreviousメソッドをつかったサンプルマクロ

FindPreviousメソッドのサンプルコードを書いてみます。

こちらのコードでもFindメソッドでつかったデータをつかって検索をしていきます。

  Copy
Option Explicit

Sub FindPreviousメソッド1()

    Dim 検索範囲 As Range '検索する範囲を代入する変数を宣言する
    Dim 結果 As Range '結果を代入する変数を宣言する

    Dim 検索文字列 As String '検索文字列を代入する変数を宣言する
    
    With Sheet1
        '検索範囲を変数に代入する
        Set 検索範囲 = .Range("A1:E11")
        
        '検索文字列を変数に代入する
        検索文字列 = Application.InputBox("検索文字列を入力してください。", "検索文字列を入力", "東京都")
        
        'Findメソッドで検索結果を変数に代入する
        Set 結果 = 検索範囲.Find(検索文字列, LookIn:=xlValues)
        
        If 結果 Is Nothing Then '検索にヒットしない場合
            Debug.Print 検索文字列 & " が入力されたセルはありませんでした。"
            Exit Sub
        Else
            '検索した文字列があるセルのアドレスを表示する
            Debug.Print 結果.Address
        
            'FindPreviousメソッドでFindメソッドの結果を引数に指定した戻り値を変数に代入する
            Set 結果 = 検索範囲.FindPrevious(結果)
        
            '検索した文字列があるセルのアドレスを表示する
            Debug.Print 結果.Address
        
            'FindPreviousメソッドでFindメソッドの結果を引数に指定した戻り値を変数に代入する
            Set 結果 = 検索範囲.FindPrevious(結果)
        
            '検索した文字列があるセルのアドレスを表示する
            Debug.Print 結果.Address
            
        End If
    End With

End Sub

18行目

Findメソッドを使って検索文字列に代入された文字列を検索します。返されるRangeオブジェクトを変数結果に代入します。

25行目

変数結果のアドレスをイミディエイトウィンドウに表示します。

28行目

FindPreviousメソッドをつかって検索をします。引数にはFindメソッドで得た変数結果を指定しています。
変数結果にFindPreviousメソッドで返ってきたRangeオブジェクトで上書きします。

31行目

変数結果のアドレスをイミディエイトウィンドウに表示します。
※28行目で上書きされているので、FindPreviousメソッドで見つかったRangeオブジェクトのアドレスになります。

34行目

FindPreviousメソッドをつかって検索をします。今回も引数には変数結果を指定していますが、28行目のFindPreviousメソッドで返ってきたRangeオブジェクトが引数となっています。

あらためて変数結果にFindPreviousメソッドで返ってきたRangeオブジェクトで上書きします。

37行目

変数結果のアドレスをイミディエイトウィンドウに表示します。
※34行目で上書きされているので、FindPreviousメソッドで見つかったRangeオブジェクトのアドレスになります。

続いて実行結果を確認してみましょう。

　Findメソッドで検索でセル”$A$4“が返ってきています。最初のFindPreviousメソッドの検索で見つかったセルのアドレス”$C$11“が、2度目のFindPreviousメソッドで”$C$4“を返すことができました。

　FindNextメソッドのサンプルコード(Sub FindNextメソッド1)の実行結果と異なっている点は、結果で表示されたアドレスを表示する順番になります。

Findメソッドの検索で見つかったセルのアドレスが”$A$4″であり、この”$A$4“を引数としてFindNextメソッドでは”$C$4“が返り、FindPreviousメソッドでは”$C$11“が先に検索結果として返ってきています。

　そして、FindNextメソッドと同じくFindPreviousメソッドもくり返し処理と組み合わせて使うことでその真価を発揮します。

FindPreviousメソッドで複数のセルを検索すサンプルマクロ

  Copy
Option Explicit

Sub FindPreviousメソッド2()

    Dim 検索範囲 As Range '検索する範囲を代入する変数を宣言する
    Dim 結果 As Range '結果を代入する変数を宣言する
    Dim 初回結果 As String
    Dim 検索文字列 As String '検索文字列を代入する変数を宣言する
    
    With Sheet1
        '検索範囲を変数に代入する
        Set 検索範囲 = .Range("A1:E11")
        
        '検索文字列を変数に代入する
        検索文字列 = Application.InputBox("検索文字列を入力してください。", "検索文字列を入力", "東京都")
        
        '検索結果を変数に代入する
        Set 結果 = 検索範囲.Find(検索文字列, LookIn:=xlValues)
        
        '検索ヒットしたときとしなかったときで処理を分岐する
        If 結果 Is Nothing Then '検索にヒットしない場合
            Debug.Print 検索文字列 & " が入力されたセルはありませんでした。"
        Else '検索にヒットした場合
            初回結果 = 結果.Address '最初にヒットしたセルのアドレスを変数に代入する
            Do
                Debug.Print 検索文字列 & " は" & 結果.Address & " です。" '結果を表示する
                Set 結果 = 検索範囲.FindPrevious(結果) 'FindPreviousメソッドによる次の検索
            Loop While 結果.Address <> 初回結果 '繰り返し条件：最初にヒットしたセルのアドレスと現在のセルアドレスが異なる間は繰り返す
        End If
    
    End With

End Sub

24行目

　7行目で宣言した変数初回結果にFindメソッドで見つかったセルのアドレスを代入します。
これは、後のDo~Loop文の条件式に利用して無限ループを回避するための処理です。

25行目から28行目

　Do~Loop文でくり返し処理にします。
くり返す条件はFindPreviousメソッドで見つかったセルのアドレスが、変数初回結果と同じになるまでです。これにより2回目以降の検索で見つかったセルのアドレスが、最初に見つかったセルのアドレスと同じになった時点で処理は終了することになります。

繰り返し処理の詳しい解説は、こちらの記事をあわせて参考にしてください。

　Findメソッドによる検索の結果は”$A$4“を変数の初回結果に代入しているため、FindPreviousメソッドで”$A$4“が返ってくるとくり返しが終了します。

これで検索キーワードが含まれているセルのアドレスをすべて抽出することができました。

PR

FindNextとFindPreviousメソッドのちがい

　この記事では、FindメソッドとFindNextメソッド・FindPreviousメソッドの使いかたやサンプルマクロの解説をしました。FindNextメソッド、FindPreviousメソッドはFindメソッドの検索を継続するメソッドなので、Findメソッドと組み合わせてつかう必要がありました。

　すでにサンプルマクロの解説などで一部紹介していますが、あらためてFindNextメソッド、FindPreviousメソッドのちがいを説明します。

検索をかける方向がちがう

　結論を言ってしまえば、以下のとおりです。

• FindNextメソッドはFindメソッドで得た検索結果から次の検索をする
• FindPreviousメソッドはFindメソッドで得た検索結果から前の検索をする

　分かりやすくするために、具体的なサンプルマクロのコードで見てみましょう。
これまでと同じく以下の表からキーワードである”オレンジ“が入力されたセルの検索してみます。

  Copy
Option Explicit

Sub difference_Next_Previous()

    Dim 検索範囲 As Range '検索する範囲を代入する変数を宣言する
    Dim 結果 As Range '結果を代入する変数を宣言する
    Dim 初回結果 As String
    Dim 検索文字列 As String '検索文字列を代入する変数を宣言する
    
    With Sheet1
        '検索範囲を変数に代入する
        Set 検索範囲 = .Range("A1:E11")
        
        '検索文字列を変数に代入する
        検索文字列 = "オレンジ"
        
        '検索結果を変数に代入する
        Set 結果 = 検索範囲.Find(検索文字列, After:=Range("$A$4"), LookIn:=xlValues)
        
        '検索ヒットしたときとしなかったときで処理を分岐する
        If 結果 Is Nothing Then '検索にヒットしない場合
            Debug.Print 検索文字列 & " が入力されたセルはありませんでした。"
        Else '検索にヒットした場合
            初回結果 = 結果.Address '最初にヒットしたセルのアドレスを変数に代入する
            Do
                Debug.Print 検索文字列 & " は" & 結果.Address & " です。" '結果を表示する
                Set 結果 = 検索範囲.FindNext(結果) 'FindNextメソッドによる次の検索
            Loop While 結果.Address <> 初回結果 '繰り返し条件：最初にヒットしたセルのアドレスと現在のセルアドレスが異なる間は繰り返す
        End If
    
    End With

End Sub

18行目

　Findメソッドを使って”オレンジ“が記入されたセルを検索します。これまでとちがう点は引数のAfterに”$A$4“を指定している箇所になります。

After引数を指定したことで、Find関数は”$A$4″の次のセルから検索するようになります。
※ここでの解説をわかりやすくするために指定しています。

26行目

　イミディエイトウィンドウで検索されたセルのアドレスを表示します。

実行結果から検索される順番を確認してみましょう。

　Findメソッドでの検索はAfterの指定により”$A$4“の次のセルから検索を開始するため、検索の最初で見つかったセルのアドレスは”$C$4“が返ってきます。
その後のFindNextメソッドは”$C$4“の次のセルから検索を継続するので、見つかったセルのアドレスの”$C$11“が返されます。”$C$11“はくり返しを終了する条件のアドレス(“$C$4“)と一致しないため、さらに検索を継続し、検索範囲の末尾であるE11セルまで検索が到達するとA1セルに戻って検索を続けるといった動きになります。

よって、実行結果のとおり、”$C$4“　→　”$C$11“　→　”$A$4“　という順番で検索されることになります。

  Copy
Option Explicit

Sub difference_Next_Previous2()

    Dim 検索範囲 As Range '検索する範囲を代入する変数を宣言する
    Dim 結果 As Range '結果を代入する変数を宣言する
    Dim 初回結果 As String
    Dim 検索文字列 As String '検索文字列を代入する変数を宣言する
    
    With Sheet1
        '検索範囲を変数に代入する
        Set 検索範囲 = .Range("A1:E11")
        
        '検索文字列を変数に代入する
        検索文字列 = "オレンジ"
        
        '検索結果を変数に代入する
        Set 結果 = 検索範囲.Find(検索文字列, After:=Range("$A$4"), LookIn:=xlValues)
        
        '検索ヒットしたときとしなかったときで処理を分岐する
        If 結果 Is Nothing Then '検索にヒットしない場合
            Debug.Print 検索文字列 & " が入力されたセルはありませんでした。"
        Else '検索にヒットした場合
            初回結果 = 結果.Address '最初にヒットしたセルのアドレスを変数に代入する
            Do
                Debug.Print 検索文字列 & " は" & 結果.Address & " です。" '結果を表示する
                Set 結果 = 検索範囲.FindPrevious(結果) 'FindPreviousメソッドによる前の検索
            Loop While 結果.Address <> 初回結果 '繰り返し条件：最初にヒットしたセルのアドレスと現在のセルアドレスが異なる間は繰り返す
        End If
    
    End With

End Sub

　FindNextメソッドをFindPreviousメソッドに書きかえたマクロのコードです。
FindメソッドのAfter引数の指定により、Findメソッドは”$A$4“の次のセルから検索します。

　実行結果をFindNextメソッドをつかった検索のときと比べてみましょう。

　Findメソッドでの検索はAfterの指定により”$A$4“の次のセルから検索を開始するため、検索の最初で見つかったセルのアドレスは”$C$4“が返ってきます。
その後のFindPreviousメソッドは”$C$4“の前のセルから検索を継続するので、見つかったセルのアドレスの”$A$4“が返されます。”$A$4“はくり返しを終了する条件のアドレス(“$C$4“)と一致しないため、さらに検索を継続し、検索範囲の先頭のセルA1まで検索すると末尾のE11セルから検索を続けるといった動きになります。

よって、実行結果のとおり、”$C$4“　→　”$A$4“　→　”$C$11“　という順番で検索されることになります。

InStr関数

　InStr関数は、ある文字列の中から別の文字列を検索して、最初に見つかった位置を返す関数です。
例えば、InStr("Excel", "c")は、”Excel”という文字列の中で”c“という文字列が最初に現れる位置を数字で返します。

InStr(“Excel”, “c”)で返ってくる結果は3です。

　InStr関数は検索した文字列の最初の位置を返すため、検索する文字列が重複していた場合も重複していないときと同じ結果になります。

例えば、InStr("COFFEE", "E")では、5が返り、InStr("COFFEE", "F")では、3を返します。

　なお、検索文字列が見つからない場合(InStr("COFFEE", "N")など)は、0が返ってきます。

InStr関数の実用例

• 文字列の中に特定の文字や記号が含まれているかどうかを判定する
• 文字列の中から括弧やコンマなどで区切られた部分を取り出す
• 文字列の中から特定の単語やフレーズを検索する

InStr関数の使いかた

　InStr関数は引数として指定した検索対象の文字列から、検索値として指定した文字列の開始位置を返す関数です。ここではInStr関数の構文や引数についての説明と、簡単なサンプルコードの実行結果も含めてみていきましょう。

こちらで紹介するInStr関数は文字列をあつかう関数のひとつです。VBAにはこのほかにも文字列をあつかうことのできる関数が多くあります。文字列をあつかえる関数を紹介した記事もありますので、詳しく知りたい人は以下のリンクをクリックしてご覧になってください。

＼文字列操作ができる関数についてはコチラ／

InStr関数の書きかた

InStr([ start ], string1, string2, [ compare ])

引数	必須／省略可	説明
start	省略可	それぞれの検索の開始位置を指定する数値です。 省略すると、最初の文字位置から検索開始となる。 引数のcompareを指定する場合は、startも必須となります。
string1	必須	検索対象となる文字列です。
string2	必須	検索値となる(開始位置を特定したい)文字列です。
compare	省略可	文字列比較の種類を指定します。

定数	値	説明
vbUseCompareOption	-1	宣言セクションで宣言した比較方法で比較を実行します。 ※指定がない場合はバイナリ比較を実行します。
vbUseCompareOption	-1	宣言セクションで宣言した比較方法で比較を実行します。 ※指定がない場合はバイナリ比較を実行します。
vbBinaryCompare	0	バイナリ比較を実行します。 ※全角半角や大文字小文字を区別します。
vbTextCompare	1	テキスト比較を実行します。
vbDatabaseCompare	2	Microsoft Access のみで使用します。エクセルでは使いません。

InStr関数をつかったサンプルマクロ

　InStr関数をつかったサンプルコードを紹介します。

  Copy
Option Explicit

Sub InStr関数()

    '文字列[ABCDE]のCは3番目
    Debug.Print InStr("ABCDE", "C")
    
    '文字列[COFFEE]で"F"は3番目
    Debug.Print InStr("COFFEE", "F")
    
    '文字列[COFFEE]で"E"は5番目
    Debug.Print InStr("COFFEE", "E")
    
    '文字列[COFFEE]で"N"は検索されないので0
    Debug.Print InStr("COFFEE", "N")

    '文字列[COFFEE]の検索値を指定しない場合は開始位置を返すため1
    Debug.Print InStr("COFFEE", "")
    
    '文字列[COFFEE]の検索値をしない場合は開始位置を返すため3
    Debug.Print InStr(3, "COFFEE", "")

　　 '文字列[COFFEE]の文字数を越えた開始位置7を指定したときは0
    Debug.Print InStr(7, "COFFEE", "O")

    '文字列[あかあおきいろ]の"あお"を開始位置3から検索する場合3
    Debug.Print InStr(3, "あかあおきいろ", "あお", vbTextCompare) 'テキスト比較を定数で指定

    '文字列[あかあおきいろ]の"あお"を開始位置4から検索する場合0
    Debug.Print InStr(4, "あかあおきいろ", "あお", 1) 'テキスト比較を値で指定

End Sub

06行目

　InStr関数を使って文字列”ABCDE“の中から”C“が記入された位置を返します。
返ってくる数値をイミディエイトウィンドウに表示します。

09行目

　InStr関数を使って文字列”COFFEE“の中から”F“が記入された位置を返します。
返ってくる数値をイミディエイトウィンドウに表示します。

12行目

　InStr関数を使って文字列”COFFEE“の中から”E“が記入された位置を返します。
返ってくる数値をイミディエイトウィンドウに表示します。

15行目

　InStr関数を使って文字列”COFFEE“の中から”N“が記入された位置を返します。
返ってくる数値をイミディエイトウィンドウに表示します。

18行目

　InStr関数を使って文字列”COFFEE“の中から””(空白)を検索します。
返ってくる数値をイミディエイトウィンドウに表示します。

21行目

　InStr関数を使って文字列”COFFEE“の中から””(空白)を検索します。18行目とのちがいとして、引数startに”3“にすることで検索を開始する位置を指定しています。

返ってくる数値をイミディエイトウィンドウに表示します。

24行目

　InStr関数を使って文字列”COFFEE“の中から”O“を検索します。引数startに”7“にすることで検索を開始する位置を指定していますが、”COFFEE“の文字数(6文字)を上回った数値になっています。

返ってくる数値をイミディエイトウィンドウに表示します。

27行目

　InStr関数を使って文字列”あかあおきいろ“の中から”あお“を検索します。引数startに”3“にすることで検索を開始する位置を指定しており、文字列の比較種別の引数compareを”vbTextCompare“にしています。

返ってくる数値をイミディエイトウィンドウに表示します。

30行目

　InStr関数を使って文字列”あかあおきいろ“の中から”あお“を検索します。引数startに”4“にすることで検索を開始する位置を指定しており、文字列の比較種別の引数compareを”1“にしています。

返ってくる数値をイミディエイトウィンドウに表示します。

　引数である start を指定した場合、string2 の検索を開始する位置は指定値に応じますが、返ってくる値は start からの位置ではなく、あくまで string1 の最初に見つかった位置を返します。

※検索する文字列(String2)が見つからないときは0が返ります。
※検索する文字列(String2)が空白であり、開始位置(Start)の指定があった場合は開始位置が返ってきます。

※検索する文字列(String2)が開始位置(Start)以降に見つからなかった場合は0が返ります。

PR

FindメソッドとInStr関数を使いわける

　この記事で説明してきたFindメソッドやInStr関数はどちらも文字列を検索するものです。

これまではFindメソッドやInStr関数の基本的なことについて説明してきましたが、ここではFindメソッドとInStr関数のちがいと使いわけかたについて紹介します。

まずは、それぞれの機能についておさらいです。

Findメソッド

　Rangeオブジェクトのメソッドで、ワークシート上のセル範囲内の条件に当てはまるセルを検索するものです。
ワークシート操作の「検索と置換」の「検索」の機能をVBAで使う場合に利用して見つかったセルのRangeオブジェクトを返します。

InStr関数

　VBAの組み込み関数であり、文字列の中から指定した文字列を検索して、最初に見つかった文字位置を返すものです。InStr関数は、文字列操作や判定に使われる関数です。InStr関数は、見つかった文字位置の数値を返します。

FindメソッドとInStr関数を使いわける基準

• ワークシート上のセルの値や書式を検索したいときは、Findメソッドを使います。
• 文字列変数やセルの値などの文字列から部分文字列を検索したいときは、InStr関数を使います。

InStr関数でFindメソッドの機能をもつマクロ

　InStr関数は特定の文字列の開始位置を返すものでした。
また、指定した文字列が見つからなければ0が返ってくる仕様となっています。じつはこの仕様を利用することで、Findメソッドのようなマクロをつくることもできます。

　Findメソッドの説明で紹介したときと同じく、以下の表から検索値“オレンジ”が入力されたセルのアドレスを検索して出力結果として返すマクロを作ってみます。

  Copy
Option Explicit

Sub InStr関数でFindメソッド()

    
    Dim 結果フラグ As Long 'InStr関数の開始位置を代入する変数
    Dim 検索結果リスト() As String '検索結果のアドレスを代入する配列
    Dim 検索範囲 As Range '検索するセル範囲を代入する変数
    Dim 検索セル As Variant '繰り返し処理用のセルを代入する変数(Variant型)
    Dim i As Long 'カウンタ変数
    
    With Sheet1
        '検索範囲を変数に代入する
        Set 検索範囲 = .Range("A1:E11")
        
        'すべての検索対象の範囲すべてを検索する
        For Each 検索セル In 検索範囲
            結果フラグ = InStr(検索セル, "オレンジ") '検索値のオレンジの開始位置を変数(結果フラグ)に代入
            If 結果フラグ >= 1 Then '開始が1以上であれば実行する
                ReDim Preserve 検索結果リスト(i) As String '配列検索結果リストを再定義する
                検索結果リスト(i) = 検索セル.Address '検索セルのアドレスを検索結果リストに代入する
                i = i + 1 '配列インデックスカウンタの変数に1を足す
            End If
        Next 検索セル
        
        For i = 0 To UBound(検索結果リスト) '正順検索
            Debug.Print 検索結果リスト(i) '配列に代入された値をすべて表示する
        Next i
    
'        For i = UBound(検索結果リスト) To 0 Step -1 '逆順検索
'            Debug.Print 検索結果リスト(i)’配列に代入された値をすべて表示する
'        Next i
    
    End With

End Sub

06行目から10行目

必要な変数を宣言しています。

InStr関数の実行で返ってきた値を代入する変数として結果フラグを宣言します。

検索したい文字列が含まれているセルのアドレスを代入するための変数として検索結果リスト宣言します。
(※複数セルが該当するため、動的配列として宣言しています。)

セル範囲を代入するための変数として検索範囲を宣言します。

検索している個別のセルを代入するための変数として検索セルを宣言します。

くり返し処理で利用する変数としてiを宣言します。

17行目から24行目

くり返し処理を実行するため、ForEachステートメントで、検索範囲に含まれる検索セルを対象に処理をします。

InStr関数をつかって、検索セルのなかに”オレンジ“があるかを検索し、開始位置を結果フラグに代入します。

結果フラグに代入された開始位置が1以上であれば、検査結果リストに検索セルのアドレスを追加します。

検索結果リストのインデックスとして、変数iに1を足します。

InStr関数の使いかたのポイント

　InStr関数で検索した文字列が見つからなかったときは0が返される仕様を利用して、返ってきた値が1以上かそうでないかの条件判定をすることでFindメソッドのような動きを実現しています。

26行目から28行目

結果をイミディエイトウィンドウに表示する処理です。
くり返し処理であるForNextステートメントをつかって検索結果リストに追加されたアドレスをイミディエイトウィンドウに表示します。
(※検索されたアドレスを逆順で取り出したいときは30行目から32行目の書きかたを参考にしてください。)

動的配列について

　このサンプルマクロでは複数のセルのアドレスがあつかえるように動的配列を利用しています。
　配列については他の記事で取り上げていますので、以下のリンクより記事をご覧ください。

まとめ

　今回はエクセルにおける検索に着目し、FindメソッドやInStr関数を使って指定の検索値をさがす処理をする方法について紹介しました。

　Findメソッドは、検索値とする文字列がどのセルに入力されているかを調べることに向いています。ただし、Findメソッドの戻り値は検索で最初にみつかったセルのRangeオブジェクトを返します。

そのため、複数の検索結果がある場合はFindNextやFindPreviousメソッドを組み合わせて使う工夫が必要でした。

　一方で、InStr関数はセル内の文字列の一部に指定の文字列が含まれてるかを調べることができ、開始位置を数値で返します。

InStr関数は文字列の開始位置を数値で返しますが、返ってくる値の利用方法を工夫することで、Findメソッドのようなはたらきをするマクロを作成することもできました。

今回はここまで。