ブック内でテキスト検索を行い、すべての該当セルを列挙するマクロです。Findメソッドをループさせて全件抽出するような処理の基礎構造になります。
ブック内の検索で全件抽出するマクロ
Sub ブック内を全範囲検索()
Const txtSearch As String = "検索語"
Dim oWS As Worksheet
Dim rngFound As Range
Dim adrX As String
Dim sMsg As String
For Each oWS In ActiveWorkbook.Worksheets
Set rngFound = oWS.Cells.Find( _
What:=txtSearch, _
LookIn:=xlValues, _
LookAt:=xlPart, _
Searchorder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
MatchByte:=False, _
SearchFormat:=False)
If Not (rngFound Is Nothing) Then
'1個目が検出されたら
adrX = rngFound.Address
sMsg = sMsg & oWS.Name & "!" & adrX & vbCrLf
'2個目以降の検出(1個目が再検出されたら離脱)
Do
Set rngFound = oWS.Cells.FindNext(rngFound)
If rngFound.Address = adrX Then Exit Do
sMsg = sMsg & oWS.Name & "!" & rngFound.Address & vbCrLf
Loop
End If
Next oWS
MsgBox sMsg
End Subコードの解説
検出はシートまたはシート内の指定範囲
検索の対象はシートまたはシート内の指定範囲です。ブック内の検索をする場合は、For Each...Nextステートメントで各シートの検索を行います。
FindメソッドとFindNextメソッドの併用
該当セルの1個目の検出には Findメソッドを使い、2個目以降の検出は FindNextメソッドを使います。どちらのメソッドも検出されたセルのRangeオブジェクトを返します。
この例ではセルのシート名とアドレスを収集していますが、Rangeオブジェクトが持つほかの情報を取り出すこともできます。
Findメソッド
シート内で1個目の該当セルを検出し、変数に格納しています。該当セルがない場合は、変数が空になるかで判定できます。(rngFound Is Nothing)
Findメソッドには多くのパラメーターがありますが、[検索]ダイアログボックスの操作をマクロ記録して流用するのが手っ取り早いです。またパラメータには前回検索（ユーザー操作でもマクロでも）の指定が記憶されるため、省略しないで指定するようにします。
FindNextメソッド
Findメソッドと同条件で次の該当セルを検出し、変数に格納しています。パラメーターには検索開始位置として先に検出されたRangeを指定します。これをDo...Loopで繰り返します。
FindNextの無限ループに注意!
要注意なのが、FindNextは末尾まで検索したら先頭に戻って検索することです。つまり、該当セルが1個だけでもFindNextはその1個を検出し続けてしまいます。これを避けるため、Findで検出済みのセルがFindNextで再検出されたらループから抜けるようにします。この判定のために、1個目のセルのアドレスを変数adrXに保持しておいて2個目以降のアドレスと比較しています。