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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching across all sheets
Your solution looks a lot more succinct than mine, Bob!
Cheers! Steve |
Reply |
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 |