特定の文字列を含むセルを検索する - Instr関数

ある文字列を含むセルを検索する方法を解説します。

For文またはFor Each文でセルをループし、
Instr関数で特定の文字列を含んでいるか判定します。

指定列内にある特定の文字列を含むセルを検索する

指定の列内にある特定の文字列を含むセルを検索するコードがこちらです。

Sub A列にある特定の文字列を含むセルを検索する()

    Dim ws対象シート As Worksheet: Set ws対象シート = ThisWorkbook.Worksheets("○○")
    Dim 最終行 As Long: 最終行 = ws対象シート.Cells(ws対象シート.Rows.Count, 1).End(xlUp).Row
    
    Dim 検索文字列 As String: 検索文字列 = "みかん"
    
    Dim R As Long
    For R = 2 To 最終行

        ' 検索文字列を含むセルか判定
        If InStr(ws対象シート.Cells(R, 1).Value, 検索文字列) > 0 Then

            ' 対象セルの位置をイミディエイトウィンドウに出力
            Debug.Print ws対象シート.Cells(R, 1).Address(0, 0)
            
            ' 対象のセルを赤色に着色
            ws対象シート.Cells(R, 1).Interior.Color = RGB(255, 0, 0)

        End If
    
    Next

End Sub

指定列の全てのセルをFor文でループしていき、
Instr関数によって特定の文字列を含んでいるか判定するコードです。

Instr関数は「ある文字列が何文字目に登場するか」を取得する関数で、
例えば「Instr("愛媛みかん", "みかん")」は「3」を返します。

特定の文字列が見つからなかった場合は「0」を返しますので、

If InStr(ws対象シート.Cells(R, 1).Value, 検索文字列) > 0 Then

このように「Instr関数の結果が0より大きいか」を調べることで、
セルの値が特定の文字列を含んでいるかを判定できます。

より細かい判定を行いたい場合

ただ含んでいるかを判定するだけならInstr関数で十分ですが、
「A000(0は任意の数字)」を含んでいるかなどを調べたいときもあります。

このときは「Like演算子」を使用して、判定部を以下の通り書き換えます。

If ws対象シート.Cells(R, 1).Value Like "*A###*" Then

「*」は任意の文字列(いわゆるワイルドカード)を表し、
「#」は1文字の数字を表しています。

このようにLike演算子であればより細かい判定ができます。

詳しくはこちらのページをご覧ください。

www.limecode.jp

指定のセル範囲内にある特定の文字列を含むセルを検索する

続いて指定セル範囲内にある特定の文字列を含むセルを検索するコードがこちらです。

Sub 指定セル範囲にある特定の文字列を含むセルを検索する()

    Dim ws対象シート As Worksheet: Set ws対象シート = ThisWorkbook.Worksheets("○○")
    Dim rng検索範囲 As Range: Set rng検索範囲 = ws対象シート.Range("A2:C100")
    
    Dim 検索文字列 As String: 検索文字列 = "みかん"
    
    Dim セル As Range
    For Each セル In rng検索範囲.Cells

        ' 検索文字列を含むセルか判定
        If InStr(セル.Value, 検索文字列) > 0 Then

            ' 対象セルの位置をイミディエイトウィンドウに出力
            Debug.Print セル.Address(0, 0)
            
            ' 対象のセルを赤色に着色
            セル.Interior.Color = RGB(255, 0, 0)
            
            ' 対象セルの右隣のセルに「発見」と出力
            セル.Offset(0, 1).Value = "発見"

        End If
    
    Next

End Sub

特定のセル範囲内にあるすべてのセルをループする場合は、
For Each ステートメントを使用します。

For文より少し書くのが難しいですが、
セルそのものが変数に入るためコード自体は読みやすくなりますね。

1点、行番号でループをしている訳ではないため、
同じ行の別のセルを指定するときには注意が必要です。

上記コードのようにOffsetプロパティで右側のセルを指定するか、
セル.Rowで行番号を取得して使用してください。

おまけ：Findメソッドについて

さて、最後に「Findメソッド」についても触れておきます。

Findメソッドとは、ワークシート上での「検索」ダイアログ

この機能をVBA上で実行する機能です。

この機能、ワークシートではとても便利なのですが、
残念ながらVBAから実行するのはおすすめしません。

まずは一つ目の理由として、
Findメソッドは「すべて検索」するコードがかなり複雑になります。

Findメソッドに加えてFindNextメソッドも使い、
ひとつずつ「次を検索」していくイメージでコードを書く必要があります。

そうして複雑なコードを何とか書きあげたとして、
それが高速でいい処理ならよかったのですがそんなこともありません。

むしろFindメソッドはVBAで使うには欠点だらけで、

1. オートフィルターによる非表示セルを見つけられない
2. ユーザーと設定(完全/部分一致など)を共有するため、
   直前にユーザーが行った操作によって挙動が変わる
3. MATCH関数やVLOOKUP関数より十数倍レベルで遅い

このような欠点を抱えています。

難しい上に使うメリットがほとんどありませんので、検索系のコードは

• 今回のようにFor文やForEach文でしっかりループする
• VLOOKUPやMATCHなどのシート関数に頼る
• オートフィルターで抽出する

などの方法をとった方が良いです。

詳しくは以下の記事で解説していますので、
気になる方は読んでみてください。
www.limecode.jp