【Excel VBA】開いている全ブックの全シートを対象に一括検索する方法

2025年8月13日

複数のExcelファイルにまたがって、特定のキーワードや担当者名がどこで使われているかを一度に調べたい、という場面はありませんか？Excelの標準の検索機能では、ブックを一つずつ開いて検索を繰り返す必要があり、非常に手間がかかります。

VBAを使えば、現在開いているすべてのブックの、すべてのシートを対象に一括で文字列検索を行い、結果を一つのシートにリストアップするという、強力な「横断検索」ツールを作成できます。

この記事では、.Findと.FindNextメソッドを使った、そのための実践的なコードを解説します。

準備：検索結果を出力するシート

このマクロは、検索結果を特定のシートに書き出します。マクロを記述するブックに、あらかじめ「SearchResults」という名前のシートを作成し、1行目に以下のような見出しを設定しておいてください。

A列	B列	C列	D列
ブック名	シート名	見つかった数	セル番地

完成したVBAコード

Sub SearchAllOpenWorkbooks()

    ' 変数を宣言します
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim searchTerm As String
    Dim resultsSheet As Worksheet
    Dim outputRow As Range
    Dim foundCell As Range
    Dim firstFoundAddress As String

    '--- 設定 ---
    searchTerm = "東京支店" ' 検索したい文字列
    Set resultsSheet = ThisWorkbook.Worksheets("SearchResults") ' 結果を出力するシート
    '--- 設定ここまで ---
    
    ' 結果出力用の変数を初期化
    resultsSheet.Range("A2:D" & Rows.Count).ClearContents
    Set outputRow = resultsSheet.Range("A2")

    '--- 1. 開いているすべてのブックをループ ---
    For Each wb In Workbooks
        '--- 2. ブック内のすべてのシートをループ ---
        For Each ws In wb.Worksheets
            ' 検索結果シート自身は検索対象から除外
            If ws.Parent.FullName = resultsSheet.Parent.FullName And ws.Name = resultsSheet.Name Then
                '何もしない
            Else
                '--- 3. Findメソッドで最初のセルを検索 ---
                Set foundCell = ws.Cells.Find(What:=searchTerm, LookIn:=xlValues, LookAt:=xlPart)
                
                '--- 4. 見つかった場合は、FindNextで残りを検索 ---
                If Not foundCell Is Nothing Then
                    firstFoundAddress = foundCell.Address
                    
                    ' 見つかったセルをUnionで一つのRangeオブジェクトにまとめる
                    Dim foundRange As Range
                    Set foundRange = foundCell
                    
                    Do
                        Set foundCell = ws.Cells.FindNext(After:=foundCell)
                        ' ループの脱出条件：見つからなくなった、または最初に見つけたセルに戻ってきた
                        If foundCell Is Nothing Then Exit Do
                        If foundCell.Address = firstFoundAddress Then Exit Do
                        
                        Set foundRange = Union(foundRange, foundCell)
                    Loop
                    
                    '--- 5. 検索結果をシートに書き出す ---
                    outputRow.Cells(1, 1).Value = wb.Name
                    outputRow.Cells(1, 2).Value = ws.Name
                    outputRow.Cells(1, 3).Value = foundRange.Count
                    outputRow.Cells(1, 4).Value = foundRange.Address(False, False)
                    
                    ' 次の書き込み行へ移動
                    Set outputRow = outputRow.Offset(1)
                End If
            End If
        Next ws
    Next wb
    
    MsgBox "すべてのブックの検索が完了しました。"

End Sub

コードのポイント解説

①/② 入れ子ループで全シートを巡回

For Each wb In Workbooks
    For Each ws In wb.Worksheets

最初の2つのFor Eachループで、開いているすべてのブック（Workbooksコレクション）と、そのブック内のすべてのシート（Worksheetsコレクション）を、一つずつ順番に処理対象にしていきます。

③ `.Find`メソッドで最初の検索

Set foundCell = ws.Cells.Find(What:=searchTerm, LookIn:=xlValues, LookAt:=xlPart)

シート内の検索には.Findメソッドを使います。

What: 検索する文字列を指定します。
LookIn: xlValues（値）かxlFormulas（数式）のどちらを検索対象とするかを指定します。
LookAt: xlWhole（完全一致）かxlPart（部分一致）かを指定します。

Findメソッドは、見つかったセルをRangeオブジェクトとして返します。見つからなかった場合はNothingを返します。

④ `.FindNext`を使ったループ処理

.Findメソッドは、最初に見つかったセルしか返しません。シート上のすべての該当セルを見つけるには、.FindNextをループで繰り返し実行する必要があります。これはVBAにおける定型パターンです。

最初に見つかったセルのアドレスをfirstFoundAddressに記憶しておく。
Do...Loopを開始する。
.FindNextで次のセルを検索する。
見つかったセルのアドレスが、最初のアドレスfirstFoundAddressに戻ってきたら、シートを一周したのでループを抜ける。

⑤ `Union`関数で結果を統合

Union関数は、複数のRangeオブジェクトを、一つのRangeオブジェクトにまとめる機能を持っています。ループの中でFindNextが見つけるたびに、見つかったセルをUnionでfoundRangeに追加していくことで、最終的にそのシートで見つかったすべてのセルを含む一つのRangeオブジェクトを構築しています。これにより、見つかったセルの総数（.Count）やアドレス（.Address）を簡単に取得できます。