Search orkbook

[Bob Mignault]

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

 

[Frank Isaacs]

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

 

[pfsardella]

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