Finding Buried Text

[Dave]

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

 

[Guest]

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.

 

[Dave]

Worked great - thank you

Now - since my macro skills are weak, how would I expand this to the other
workbooks, open or (what I'd really like) closed???

Dave

 

[Guest]

Hi Dave:

Search un-openned workbooks is possible, but difficult. This routine is
similar to the first, but will search all open workbooks and worksheets for
the text:


Sub gsnu2()
Dim r As Range
Dim text As String
Dim ws As Worksheet
Dim wb As Workbook

text = "happy"
For Each wb In Workbooks
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)
MsgBox (wb.Name)
End If
Next
Next
Next
End Sub

The mesages give you the workbook, the worksheet and the cell address.