Very Computer

I thought I was an "expert", but this one has me stumped (and I am sure
the solution is simple).

I want to search all the worksheets in a workbook to find a word or
phrase of text. It is easy enough to search a single sheet but no matter
how I try to use the FOR EACH command, the FIND command does not seem to
work.

Any ideas would be appreciated!
TIA

The following routine is a very rough expansion of the FIND example in Excel
97's VB
help file that will activate each sheet in the current workbook in turn &
change a cell to red if it contains "Test Phrase". (Obviously, one would
need to customise the phrase being searched for as well as the action within
the Do...While loop.)

One caveat: it will intentionally skip over any worksheet where the
UsedRange consists of only a single cell or the sheet is otherwise empty. (I
was being lazy at the time I wrote it & didn't feel like trapping the error
generated in those very rare instances).

The syntax for Replace is similar to Find, but not identical, & could be
adapted.

Sub SearchWorkbook()
    Dim wksht As Worksheet
    Dim c As Range
    Dim firstaddress As String

    For Each wksht In Sheets
        wksht.Activate
        With ActiveSheet.UsedRange
            If .Count > 1 Then
            ' Act only if worksheet has more than one cell in use
                Set c = .Find(What:="Test Phrase", _
                    After:=ActiveCell, _
                    LookIn:=xlFormulas, _
                    LookAt:=xlPart, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlNext, _
                    MatchCase:=False)
                If Not c Is Nothing Then
                    firstaddress = c.Address
                    Do
                           '  Customize per your needs
                           c.Interior.ColorIndex = 3
                           Set c = .FindNext(c)
                    Loop While Not c Is Nothing And c.Address <>
firstaddress
                End If
            End If
        End With
    Next wksht
End Sub

Good Luck,
George

No macro is necessary to do this, although there may be specific advantages
to using a macro approach depending on the details of what you're doing.

To do it without a macro, select all the sheets you want to search by:
-clicking the leftmost tab of the group of sheets you want to search, then
-hold the shift key down while clicking on the rightmost tab - the group of
sheets' tabs should be highlighted, indicating a multiple sheet selection
-click on file...find... and away you go.

Try using the macro recorder for the above sequence - it may show a simple
way to do the same thing with a macro, and giving you a skeleton of code you
can jazz up to do other tricky stuff if you need a macro approach.

Notes:
1) If the group of sheets are not all "side by each", use the control key
instead of the shift key - in this case you must do it for each sheet until
you've activated all the ones you want to search.
2) It is possible to have a celle ntry that is buried under a merged group
of cells. Example: "John Doe" in cell A1 and "Joe Blow" in cell B2. Select
A2:B2 and merge the cells. You see "John Doe" stretched across the two
merged cells, and you don't see "Joe Blow". Getting to the point, neither
does the find function invoked by file...find...

M. Hodgson:

FYI: In Excel 97 at least, if you want to accurately search more than one
sheet for all possible occurances of a value all at one time, you DO have to
do it with a macro.

You can manually use Find on "grouped" sheets ONLY if you're looking for a
unique value or a value that will only appear on one sheet.  With multiple
sheets selected, once a matching value is found, Excel won't "look" at any
other sheet regardless of how many times you press "Find Next".  So, if the
value exists on more than one sheet, you'd never know it.

The Help file doesn't come out and say this, but it does only refer to
worksheet in the singular, not plural, so you could say this behavior is
sort of documented.

George