Search Mutiple WorkSheets

[Ben Jimenez]

Hello,

I'm trying to create a search prompt that will search all
worksheets in an open Excel file. I'm creating it as a
macro. Any ideas how to search each sheet for the same
data and i give some kind of info that tells the user
witch sheet the info they are looking for is on??????

Ben

 

[JS]

Hi Ben,

If you are using Excel 2002 when you use the Find command it has a Find All
option that searches all sheets in the workbook - this may be sufficient.
This is not available with Excel 97. I have written a macro that searches
all worksheets for use with earlier versions that I can e-mail you if you
want. You can also search all open workbooks with it. Let me know if you
want me to send it...

 

[Ben Jimenez]

Hello!

Yes, could you please send! I need to use this for my
workbook. Thanks very much. (e-mail address removed)

Ben

 

[Ken]

Sorry to bother you, but I use excel 97 at work & 2000 at home. I would like
a copy of that macro
if you wouldn't mind. Thanks,

Ken

 

[Tom Ogilvy]

A basic approach would be:

Dim sh as Worksheet
Dim rng as Range
Dim sTarget as String
sTarget = "SearchString"
for each sh in thisworkbook.worksheets
set rng = nothing
set rng = sh.cells.find(sTarget)
if not rng is nothing then
sh.Activate
rng.select
msgbox "target found, click to continue search"
end if
Next

I can't guess how you want to interact with the user, so this just
demonstrates looping through the sheets and using find.

Regards,
Tom Ogilvy

 

[Ben Jimenez]

Thanks,

This will help me also.

Ben

-----Original Message-----
A basic approach would be:

Dim sh as Worksheet
Dim rng as Range
Dim sTarget as String
sTarget = "SearchString"
for each sh in thisworkbook.worksheets
set rng = nothing
set rng = sh.cells.find(sTarget)
if not rng is nothing then
sh.Activate
rng.select
msgbox "target found, click to continue search"
end if
Next

I can't guess how you want to interact with the user, so this just
demonstrates looping through the sheets and using find.

Regards,
Tom Ogilvy







.