Search Mutiple WorkSheets

  • Thread starter Thread starter Ben Jimenez
  • Start date Start date
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
 
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...
 
Hello!

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

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