Hi,

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

Thank You,
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

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

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