This small macro will search all the sheets of a workbook for some text and
reports both the sheet name and cell address where the text can be found:


Sub gsnu()
Dim r As Range
Dim text As String
Dim ws As Worksheet

text = "happy"

For Each ws In Worksheets
ws.Activate
For Each r In ActiveSheet.UsedRange
If InStr(1, r.Value, text) 0 Then
MsgBox (ws.Name & " " & r.Address)
End If
Next
Next

End Sub

The macro can easily be modified to loop over all open workbooks.
--
Gary's Student


"Dave" wrote:

Anybody know of a search engine that will find a word or phrase in a file,
such as Excel, which has several 'sub' documents/worksheets, and
identify not only the file but the worksheet it is on? I have Agent
Ransack, which is good but doesn't seem to do that.

Thx
Dave