Search orkbook
 

Greetings,

I use the following code to find MyNumber within the current worksheet:

Cells.Find(What:=MyNumber, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False, _
SearchFormat:=False).Activate

Would appreciate someone advising what changes are required to the above to
search
the entire workbook instead of just the current worksheet.

Thanks.

Regards, Bob Mignault

Search orkbook
 

How about something like this UNTESTED code:

Dim w as worksheet

For each w in worksheets
w.cells.find(What:=MyNumber).Activate
msgbox "Click OK to find next hit"
Next

The key is to just do the same thing on each worksheet; you can loop through
them with the "For...Each" construct.
--
HTH -

-Frank
Microsoft Excel MVP
Dolphin Technology Corp.
http://vbapro.com



"Bob Mignault" wrote in message
...
Greetings,

I use the following code to find MyNumber within the current worksheet:

Cells.Find(What:=MyNumber, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False, _
SearchFormat:=False).Activate

Would appreciate someone advising what changes are required to the above
to
search
the entire workbook instead of just the current worksheet.

Thanks.

Regards, Bob Mignault

Search orkbook
 

You need to watch for linewrap. This will get you started. It cycles
through all sheets, and changes all instances of
MyNumber to a gray interior color.

Sub FindMe()
' Highlights cells that contain MyNumber

Dim rngC As Range
Dim FirstAddress As String
Dim wSht as Worksheet
Dim MyNumber

MyNumber = 37358

For Each wSht in WorkSheets

wSht.Activate

With wSht.UsedRange
Set rngC = .Find(what:=MyNumber, LookAt:=xlWhole)
If Not rngC Is Nothing Then
FirstAddress = rngC.Address
Do
rngC.Interior.Pattern = xlPatternGray50
Set rngC = .FindNext(rngC)
Loop While Not rngC Is Nothing And rngC.Address <
FirstAddress
End If
End With

Next wSht

End Sub

HTH
Paul
--------------------------------------------------------------------------------------------------------------
Be advised to back up your WorkBook before attempting to make changes.
--------------------------------------------------------------------------------------------------------------
On Mon, 15 Sep 2003 15:13:16 -0400, "Bob Mignault"
wrote:

Greetings,

I use the following code to find MyNumber within the current worksheet:

Cells.Find(What:=MyNumber, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False, _
SearchFormat:=False).Activate

Would appreciate someone advising what changes are required to the above to
search
the entire workbook instead of just the current worksheet.

Thanks.

Regards, Bob Mignault