Searching across all sheets
 

About a year ago(!) someone on this group supplied me
with the text for a macro that enabled Excel 2000 to
search for text across all the sheets of a workbook
(Excel XP can do this anyway).

The trouble is that if you have lots of occurences of the
find text, the "hit any key" message keeps coming up in a
loop and the only way to get out of it is to press CTRL-
BREAK and crash the macro. Excel counts "Escape" as any
key and just keeps on with the loop.

Can anyone suggest a command I could use so that Escape
just exits the macro? (OnEscape(EndSub) or something?)

The macro is below. Thanks for any help anyone can give.

Steve Wylie


Sub FindAll()
Dim sh As Worksheet
Dim rng As Range
Dim sStr As String
sStr = InputBox("Enter search text")
If sStr = "" Then
MsgBox "You hit cancel"
Exit Sub
End If
For Each sh In ActiveWorkbook.Worksheets
Set rng = sh.Cells.Find(What:=sStr, _
After:=sh.Range("A1"), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng Is Nothing Then
firstAddress = rng.Address
Do
If Not rng Is Nothing Then
Application.Goto rng, True
MsgBox "Hit key to continue"
End If
Set rng = sh.Cells.FindNext(rng)
Loop Until rng.Address = firstAddress
End If
Next
End Sub

Searching across all sheets
 

Change

MsgBox "Hit any key to continue"

to

If MsgBox ("Continue?", vbokcancel) = vbcancel then exit sub

Robert Flanagan
Macro Systems
Delaware, U.S.
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel


Signature
"Steve Wylie" wrote in message
...
About a year ago(!) someone on this group supplied me
with the text for a macro that enabled Excel 2000 to
search for text across all the sheets of a workbook
(Excel XP can do this anyway).

The trouble is that if you have lots of occurences of the
find text, the "hit any key" message keeps coming up in a
loop and the only way to get out of it is to press CTRL-
BREAK and crash the macro. Excel counts "Escape" as any
key and just keeps on with the loop.

Can anyone suggest a command I could use so that Escape
just exits the macro? (OnEscape(EndSub) or something?)

The macro is below. Thanks for any help anyone can give.

Steve Wylie


Sub FindAll()
Dim sh As Worksheet
Dim rng As Range
Dim sStr As String
sStr = InputBox("Enter search text")
If sStr = "" Then
MsgBox "You hit cancel"
Exit Sub
End If
For Each sh In ActiveWorkbook.Worksheets
Set rng = sh.Cells.Find(What:=sStr, _
After:=sh.Range("A1"), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng Is Nothing Then
firstAddress = rng.Address
Do
If Not rng Is Nothing Then
Application.Goto rng, True
MsgBox "Hit key to continue"
End If
Set rng = sh.Cells.FindNext(rng)
Loop Until rng.Address = firstAddress
End If
Next
End Sub

Searching across all sheets
 

I have adjusted the msgbox to allow a negative response and the macro will
quit.

Sub FindAll()
Dim sh As Worksheet
Dim rng As Range
Dim sStr As String
Dim ans as Variant
sStr = InputBox("Enter search text")
If sStr = "" Then
MsgBox "You hit cancel"
Exit Sub
End If
For Each sh In ActiveWorkbook.Worksheets
Set rng = sh.Cells.Find(What:=sStr, _
After:=sh.Range("A1"), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng Is Nothing Then
firstAddress = rng.Address
Do
If Not rng Is Nothing Then
Application.Goto rng, True
ans = MsgBox( "Continue Searching?", _
vbYesNo + vbDefaultButton1)
if ans = vbNo then exit sub
End If
Set rng = sh.Cells.FindNext(rng)
Loop Until rng.Address = firstAddress
End If
Next
End Sub

--
Regards,
Tom Ogilvy

"Steve Wylie" wrote in message
...
About a year ago(!) someone on this group supplied me
with the text for a macro that enabled Excel 2000 to
search for text across all the sheets of a workbook
(Excel XP can do this anyway).

The trouble is that if you have lots of occurences of the
find text, the "hit any key" message keeps coming up in a
loop and the only way to get out of it is to press CTRL-
BREAK and crash the macro. Excel counts "Escape" as any
key and just keeps on with the loop.

Can anyone suggest a command I could use so that Escape
just exits the macro? (OnEscape(EndSub) or something?)

The macro is below. Thanks for any help anyone can give.

Steve Wylie


Sub FindAll()
Dim sh As Worksheet
Dim rng As Range
Dim sStr As String
sStr = InputBox("Enter search text")
If sStr = "" Then
MsgBox "You hit cancel"
Exit Sub
End If
For Each sh In ActiveWorkbook.Worksheets
Set rng = sh.Cells.Find(What:=sStr, _
After:=sh.Range("A1"), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng Is Nothing Then
firstAddress = rng.Address
Do
If Not rng Is Nothing Then
Application.Goto rng, True
MsgBox "Hit key to continue"
End If
Set rng = sh.Cells.FindNext(rng)
Loop Until rng.Address = firstAddress
End If
Next
End Sub

Searching across all sheets
 

Ah! Typical - just when I asked for help I find a
solution myself.

If Not rng Is Nothing Then
Application.Goto rng, True
response = MsgBox("Cancel search ? ", vbYesNo)
If response = 6 Then Exit Sub
End If

This seems to do the job. Thanks anyway!

Steve

Searching across all sheets
 

Your solution looks a lot more succinct than mine, Bob!
Cheers!

Steve