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.

Ben




-----Original Message-----
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...




.

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

"JS" wrote in message
...
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...

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" wrote in message
...
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

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




"Ben Jimenez" wrote in message
...
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



.