Search Mutiple WorkSheets

B

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
 
J

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...
 
K

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
 
T

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
 
B

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







.
Click to expand...
 
Top