Search/Find in any worksheet
 

Hello,

Is it possible to search for a string in all the worksheets of a workbook and not just the active worksheet? If not, is it possible to make a macro to do so (could someone give me a hand)?

Thank you

Daniel

Search/Find in any worksheet
 

Daniel,

The sub bleow will find all complete matches of the entered string in the
active workbook.

HTH,
Bernie
MS Excel MVP

Sub FindAllValuesInWorkbook()
Dim c As Range ' The cell found with what you want
Dim d As Range ' All the cells found with what you want
Dim myFindString As String
Dim firstAddress As String
Dim mySht As Worksheet

myFindString = InputBox("Enter the key word for finding", _
, "What to find")
For Each mySht In ActiveWorkbook.Worksheets
With mySht.Cells

Set c = .Find(myFindString, LookIn:=xlValues, lookAt:=xlWhole)

If Not c Is Nothing Then
Set d = c
firstAddress = c.Address
Else:
MsgBox "Not Found"
GoTo NotFound:
End If

Set c = .FindNext(c)
If Not c Is Nothing And c.Address < firstAddress Then
Do
Set d = Union(d, c)
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With
'Then do what you want with all
'the cells that have been found, like
MsgBox "On sheet " & mySht.Name & ", found in " & d.Address
NotFound:
Next mySht

End Sub


"Daniel" wrote in message
...
Hello,

Is it possible to search for a string in all the worksheets of a workbook
and not just the active worksheet? If not, is it possible to make a macro
to do so (could someone give me a hand)?

Thank you

Daniel

Search/Find in any worksheet
 

Hi
try Jan Karel Pieterse's FLEXFIND.XLA from

http://www.bmsltd.ie/mvp/

--
Regards
Frank Kabel
Frankfurt, Germany


Daniel wrote:
Hello,

Is it possible to search for a string in all the worksheets of a
workbook and not just the active worksheet? If not, is it possible
to make a macro to do so (could someone give me a hand)?

Thank you

Daniel