Searching Across Worksheets: Please help!

  • Thread starter Thread starter computerfineman
  • Start date Start date
C

computerfineman

Assuming I have a workbook with many worksheets and I want to search for
a particular word contained in one of the cells, in one of the sheets
somewhere in the workbook, how do I go about it?

Please help!
 
The following macro will find a cell whose contents is the word "treasure" in
a workbook:

Sub findtreasure()
Dim w As Worksheet
Dim s As String
Dim r As Range
s = "treasure"
For Each w In ActiveWorkbook.Worksheets
w.Activate
For Each r In ActiveSheet.UsedRange
If r.Value = s Then
MsgBox (w.Name & " " & r.Address)
Exit Sub
End If
Next
Next
MsgBox ("value not found")
End Sub

If you are not familiar with using macros, see:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
Edit>Find>Options>Within>Workbook.

If running an older version of Excel <2002 you won't have this option so use the
code Gary's Student provided.


Gord Dibben MS Excel MVP
 
Thank's guys,

I don't undestand the macros thing but I guess Gord Dibben's variatio
works out real good. (Edit>Find>Options>Within>Workbook)

computerfineman
 
Back
Top