Searching Across Worksheets: Please help!

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
--
Gary's Student


"computerfineman" wrote:

>
> 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!
>
>
> --
> computerfineman
> ------------------------------------------------------------------------
> computerfineman's Profile: http://www.excelforum.com/member.php...o&userid=37165
> View this thread: http://www.excelforum.com/showthread...hreadid=571152
>
>

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

On Sun, 13 Aug 2006 09:12:30 -0400, computerfineman
<[email protected]> wrote:

>
>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!

You may want to try Jan Karel Pieterse's FlexFind:
http://www.oaltd.co.uk/MVP/

It'll work with any version of excel.

computerfineman wrote:
>
> 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!
>
> --
> computerfineman
> ------------------------------------------------------------------------
> computerfineman's Profile: http://www.excelforum.com/member.php...o&userid=37165
> View this thread: http://www.excelforum.com/showthread...hreadid=571152

--

Dave Peterson

Thank's guys,

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

computerfineman.