A few days ago, Tom Ogilvy provided a macro for me on
this newsgroup that enabled someone to search an entire
workbook in Excel 97/2000, not just the current sheet.
I'm posting a follow up question as a new thread as Tom
is probably not still checking the original thread.

The macro Tom supplied (pasted below) creates an infinite
loop if (I think) you give an empty string in response to
the input box. It just keeps putting up the message "Hit
key to continue" and you can't break out of it.

Would it be possible for anyone (or Tom if he's reading
this) to correct the macro so an infinite loop does not
occur, please?

Thanks
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"
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

Added Exit Sub in the If statement for the "" test.
Must have left it out of the original. Apologies.

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

--
Regards,
Tom Ogilvy


"Steve Wylie" wrote in message
...
A few days ago, Tom Ogilvy provided a macro for me on
this newsgroup that enabled someone to search an entire
workbook in Excel 97/2000, not just the current sheet.
I'm posting a follow up question as a new thread as Tom
is probably not still checking the original thread.

The macro Tom supplied (pasted below) creates an infinite
loop if (I think) you give an empty string in response to
the input box. It just keeps putting up the message "Hit
key to continue" and you can't break out of it.

Would it be possible for anyone (or Tom if he's reading
this) to correct the macro so an infinite loop does not
occur, please?

Thanks
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"
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