VBAで指定した文字列を含まないセルを見つける

指定した文字列を含まないセル

Excelでセルに書いてある文字列を検索する場合、「検索と置換」ダイアログを使って検索をします。しかし「検索と置換」ダイアログは入力した文字列が含まれているか、という検索しかできません。

それとは逆に「セルの文字列の中に指定した文字列が含まれない場合」で検索したいことがあります。

その方法について、ここでは、Instr関数を使う方法と正規表現を使う方法の２つを紹介します。

Instr関数を使う方法

以下のコードは、Instr関数を使って、選択セル範囲から指定した文字列を含まないセルに背景色を設定する関数です。

何も入力されていないセルもInstr関数では「含まれていない」と判定されるため、その場合は背景色を設定しないように除外しています。

Sub SearchInstr(a_rRange As Range, a_sSearch As String)
    Dim r   As Range        '// 処理対象セル
    
    '// 引数のセル範囲の全セルをループ
    For Each r In a_rRange
        '// セルが未入力の場合
        If r.Value = "" Then
            '// 次のセルの処理を行う
            GoTo CONTINUE
        End If
    
        '// セル文字列に検索文字列が含まれていない場合
        If InStr(1, r.Value, a_sSearch) = 0 Then
            '// セルの背景色を黄色に設定する
            r.Interior.Color = RGB(255, 255, 0)
        End If
CONTINUE:
    Next
End Sub

Sub SearchInstr(a_rRange As Range, a_sSearch As String)

Dim r As Range '// 処理対象セル

'// 引数のセル範囲の全セルをループ

For Each r In a_rRange

'// セルが未入力の場合

If r.Value = "" Then

'// 次のセルの処理を行う

GoTo CONTINUE

End If

'// セル文字列に検索文字列が含まれていない場合

If InStr(1, r.Value, a_sSearch) = 0 Then

'// セルの背景色を黄色に設定する

r.Interior.Color = RGB(255, 255, 0)

End If

CONTINUE:

End Sub

使い方

上のSearchInstr関数の使い方です。

選択範囲のセルの中から、「あ」と書いてあるセル以外に背景色を設定します。

Sub SearchInstrTest()
    Dim sSearch     As String   '// 検索文字列
    
    Call SearchInstr(Selection, "あ")
End Sub

Sub SearchInstrTest()

Dim sSearch As String '// 検索文字列

Call SearchInstr(Selection, "あ")

End Sub

正規表現（RegExpクラス）を使う方法

単純な文字列の否定検索であれば上のInstr関数の方法で十分ですが、例えば「数字が含まれていない」などのような検索条件に幅がある場合は正規表現を使う方法がラクになります。

RegExpクラスを使う場合は、CreateObject関数か参照設定かのどちらかの方法で利用できるようにする必要がありますが、ここでは単一の関数であるためCreateObject関数を使った方法を採用しています。

参照設定の方法は、VBA画面のツールメニュー→参照設定→参照設定ダイアログで「Microsoft VBScript Regular Expressions 5.5」にチェックを付けると、クラスとして実装することができるようになりますが、ここでは単一の関数であるためCreateObject関数を使う方法にしています。

RegExpクラスについての詳細は「VBAで正規表現（RegExp）を利用する」をご参照ください。

処理の流れは上のInstr関数の内容とほとんど同じです。

Sub SearchRegExp(a_rRange As Range, a_sPattern As String)
    Dim r   As Range        '// 処理対象セル
    Dim reg As Object       '// RegExpクラス
    
    '// RegExpクラスのインスタンス作成
    Set reg = CreateObject("VBScript.RegExp")
    
    '// 検索条件設定
    reg.Global = True               '// 検索範囲（True：文字列の最後まで検索、False：最初の一致まで検索）
    reg.IgnoreCase = True           '// 大文字小文字の区別（True：区別しない、False：区別する）
    reg.Pattern = a_sPattern        '// 検索パターン（引数の検索パターンを設定）

    '// 引数のセル範囲の全セルをループ
    For Each r In a_rRange
        '// セルが未入力の場合
        If r.Value = "" Then
            '// 次のセルの処理を行う
            GoTo CONTINUE
        End If
    
        '// セル文字列に検索パターンが含まれていない場合
        If reg.Test(r.Value) = False Then
            '// セルの背景色を黄色に設定する
            r.Interior.Color = RGB(255, 255, 0)
        End If
CONTINUE:
    Next

End Sub

Sub SearchRegExp(a_rRange As Range, a_sPattern As String)

Dim r As Range '// 処理対象セル

Dim reg As Object '// RegExpクラス

'// RegExpクラスのインスタンス作成

Set reg = CreateObject("VBScript.RegExp")

'// 検索条件設定

reg.Global = True '// 検索範囲（True：文字列の最後まで検索、False：最初の一致まで検索）

reg.IgnoreCase = True '// 大文字小文字の区別（True：区別しない、False：区別する）

reg.Pattern = a_sPattern '// 検索パターン（引数の検索パターンを設定）

'// 引数のセル範囲の全セルをループ

For Each r In a_rRange

'// セルが未入力の場合

If r.Value = "" Then

'// 次のセルの処理を行う

GoTo CONTINUE

End If

'// セル文字列に検索パターンが含まれていない場合

If reg.Test(r.Value) = False Then

'// セルの背景色を黄色に設定する

r.Interior.Color = RGB(255, 255, 0)

End If

CONTINUE:

End Sub

使い方

正規表現版であるSearchRegExp関数の使い方です。

選択範囲のセルの中から、英字が書いてあるセル以外に背景色を設定します。英字の判定を正規表現の「[a-zA-Z]」で表現しています。

Sub SearchRegExpTest()
    Dim sSearch     As String   '// 検索文字列
    
    Call SearchRegExp(Selection, "[a-zA-Z]")
End Sub

Sub SearchRegExpTest()

Dim sSearch As String '// 検索文字列

Call SearchRegExp(Selection, "[a-zA-Z]")

End Sub

指定した文字列を含まないセル

Instr関数を使う方法

使い方

正規表現（RegExpクラス）を使う方法

使い方

関連記事