Macro to find text in all worksheets -pawan
 

Hi,

Can we arite a mcro to find text/number in all wprksheets of a file at one
time.

Thank You,
Pawan

Macro to find text in all worksheets -pawan
 

Option Explicit
Private results() As String
Private index As Long
Sub BigFind()

Dim startaddress As String
Dim ws As Worksheet
Dim cell As Range
Dim what As String


what = "dog"

For Each ws In Worksheets

Set cell = ws.Cells.Find(what)
If Not cell Is Nothing Then
startaddress = cell.Address
Do
index = index + 1
ReDim Preserve results(1 To 2, 1 To index)
results(1, index) = ws.Name
results(2, index) = cell.Address
Set cell = ws.Cells.FindNext(cell)
Loop While cell.Address < startaddress
End If
Next

Set ws = Worksheets.Add
With ws
.Range(.Range("A1"), .Cells(UBound(results, 2), UBound(results, 1)))
= _
WorksheetFunction.Transpose(results)
End With

End Sub


"Pawan" wrote:

Hi,

Can we arite a mcro to find text/number in all wprksheets of a file at one
time.

Thank You,
Pawan

Macro to find text in all worksheets -pawan
 

Patrick,
Is it possible to name the new worksheet created? The name should be equal
to the text "Search Result_" + the search string. e.g. if I search word
'dog', name of the sheet should be ""Search Result_dog"
Thank You

-Pawan


"Patrick Molloy" wrote:

Option Explicit
Private results() As String
Private index As Long
Sub BigFind()

Dim startaddress As String
Dim ws As Worksheet
Dim cell As Range
Dim what As String


what = "dog"

For Each ws In Worksheets

Set cell = ws.Cells.Find(what)
If Not cell Is Nothing Then
startaddress = cell.Address
Do
index = index + 1
ReDim Preserve results(1 To 2, 1 To index)
results(1, index) = ws.Name
results(2, index) = cell.Address
Set cell = ws.Cells.FindNext(cell)
Loop While cell.Address < startaddress
End If
Next

Set ws = Worksheets.Add
With ws
.Range(.Range("A1"), .Cells(UBound(results, 2), UBound(results, 1)))
= _
WorksheetFunction.Transpose(results)
End With

End Sub


"Pawan" wrote:

Hi,

Can we arite a mcro to find text/number in all wprksheets of a file at one
time.

Thank You,
Pawan

Macro to find text in all worksheets -pawan
 

With ws
.Range(.Range("A1"), .Cells(UBound(results, 2), UBound(results, 1)))
= _
WorksheetFunction.Transpose(results)
.Name = "Results_" & What

End With


just be sure that it name is legitimate - add a test just in case


"Pawan" wrote:

Patrick,
Is it possible to name the new worksheet created? The name should be equal
to the text "Search Result_" + the search string. e.g. if I search word
'dog', name of the sheet should be ""Search Result_dog"
Thank You

-Pawan


"Patrick Molloy" wrote:

Option Explicit
Private results() As String
Private index As Long
Sub BigFind()

Dim startaddress As String
Dim ws As Worksheet
Dim cell As Range
Dim what As String


what = "dog"

For Each ws In Worksheets

Set cell = ws.Cells.Find(what)
If Not cell Is Nothing Then
startaddress = cell.Address
Do
index = index + 1
ReDim Preserve results(1 To 2, 1 To index)
results(1, index) = ws.Name
results(2, index) = cell.Address
Set cell = ws.Cells.FindNext(cell)
Loop While cell.Address < startaddress
End If
Next

Set ws = Worksheets.Add
With ws
.Range(.Range("A1"), .Cells(UBound(results, 2), UBound(results, 1)))
= _
WorksheetFunction.Transpose(results)
End With

End Sub


"Pawan" wrote:

Hi,

Can we arite a mcro to find text/number in all wprksheets of a file at one
time.

Thank You,
Pawan