Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Searching sheets | Excel Worksheet Functions | |||
Searching across all sheets | Excel Discussion (Misc queries) | |||
Searching data within sheets | Excel Discussion (Misc queries) | |||
searching all sheets in a wb | Excel Worksheet Functions | |||
Searching for neg. #'s on different sheets | Excel Programming |