Search all sheets
 

I need a short macro that will search all sheets in the
active workbook for a certain string of text, and when it
finds it, it stops at that point on that sheet.

I asked a question of this newsgroup some time ago about
doing a Find & Replace operation across multiple sheets,
and got a reply, but I cannot figure out how to amend the
macro to just find and not find and replace. Could
anyone suggest how I could alter the macro below just to
do a Find on multiple sheets?

Thanks, Steve


The following macro will loop through all of the sheets
in the workbook and remove every occurrence of 1, and
replace it with 2, just for example.

Sub ReplaceAll()
On Error Resume Next
Sheets(1).Select
For i = 1 To Sheets.Count
Cells.Select
Selection.Replace What:="1", Replacement:="2",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Range("A1").Select
ActiveSheet.Next.Select
Next i
End Sub

The On Error Resume Next line prevents the error
developed when the last sheet is encountered and
the .Next.Select line tries to execute, which it can not,
since it is at the last sheet already.

Search all sheets
 

Sub FindAll()
Dim sh As Worksheet
Dim rng As Range, firstAddress as String
For Each sh In ThisWorkbook.Worksheets
Set rng = sh.Cells.Find(What:="1", After:=ActiveCell, 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
...
I need a short macro that will search all sheets in the
active workbook for a certain string of text, and when it
finds it, it stops at that point on that sheet.

I asked a question of this newsgroup some time ago about
doing a Find & Replace operation across multiple sheets,
and got a reply, but I cannot figure out how to amend the
macro to just find and not find and replace. Could
anyone suggest how I could alter the macro below just to
do a Find on multiple sheets?

Thanks, Steve


The following macro will loop through all of the sheets
in the workbook and remove every occurrence of 1, and
replace it with 2, just for example.


The On Error Resume Next line prevents the error
developed when the last sheet is encountered and
the .Next.Select line tries to execute, which it can not,
since it is at the last sheet already.

Search all sheets
 

Hi Tom

I cannot get this macro to work. I did make one
alteration to it - to allow me to input a value rather
than have to include it in the macro text. I paste the
amended text below, but it still doesn't work, even
before I made my amendment:

Sub FindAcrossAllSheets()

Dim sh As Worksheet
Dim rng As Range, firstAddress As String
Dim FindVal As String
FindVal = InputBox("Enter text to find")
For Each sh In ThisWorkbook.Worksheets
Set rng = sh.Cells.Find(What:=FindVal,
After:=ActiveCell, 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

Search all sheets
 

It worked fine for me, but try this:

Sub FindAll()
Dim sh As Worksheet
Dim rng As Range
Dim sStr As String
sStr = InputBox("Enter item to search for")
If sStr = "" Then
MsgBox "You hit cancel"
End If
For Each sh In ThisWorkbook.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

It should put each found cell in the upper left corner of the window.

--
Regards,
Tom Ogilvy

"Steve" wrote in message
...
Hi Tom

I cannot get this macro to work. I did make one
alteration to it - to allow me to input a value rather
than have to include it in the macro text. I paste the
amended text below, but it still doesn't work, even
before I made my amendment:

Sub FindAcrossAllSheets()

Dim sh As Worksheet
Dim rng As Range, firstAddress As String
Dim FindVal As String
FindVal = InputBox("Enter text to find")
For Each sh In ThisWorkbook.Worksheets
Set rng = sh.Cells.Find(What:=FindVal,
After:=ActiveCell, 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

Search all sheets
 

Ah - I know what I was doing wrong. As per usual, I put the macro into one
workbook spreadsheet on its own, then called up my workbook I wanted to work
on and played the macro. The macro only searches the workbook that it is
recorded in. It will activate from another open workbook, but will only
search the workbook it is recorded in.

Does this mean I must record it on every workbook I need to search through,
or can the macro be amended to search the workbook currently having focus?
Or is there a way I should "add-in" the macro workbook into the one that
contains the data?

Steve

Search all sheets
 

Put it in a general module of Personal.xls or other workbook that will be
open

Sub FindAll()
Dim sh As Worksheet
Dim rng As Range
Dim sStr As String
sStr = InputBox("Enter item to search for")
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

I have changed ThisWorkbook to ActiveWorkbook.

--
Regards,
Tom Ogilvy


"Steve Wylie" wrote in message
...
Ah - I know what I was doing wrong. As per usual, I put the macro into
one
workbook spreadsheet on its own, then called up my workbook I wanted to
work
on and played the macro. The macro only searches the workbook that it is
recorded in. It will activate from another open workbook, but will only
search the workbook it is recorded in.

Does this mean I must record it on every workbook I need to search
through,
or can the macro be amended to search the workbook currently having focus?
Or is there a way I should "add-in" the macro workbook into the one that
contains the data?

Steve

Search all sheets
 

Thanks for your help Tom, that's just what I'm looking for!

Cheers
Steve