Search orkbook

  • Thread starter Thread starter Bob Mignault
  • Start date Start date
B

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
 
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
 
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
 
Back
Top