Using Find over entire workbook

[Otto Moehrbach]

Excel XP & Win XP
I have some 50 sheets in my file. I want to find the first instance of a
particular text in whatever sheet it occurs. Having found that, I want the
code to stop. Then, starting from that range, I want to find the next
instance, and so forth. I imagine a button to run a macro to find the
first, then a "Next" button to find the next.
I recorded a macro using Find over the entire workbook and the first
instance was found. However, the initial search to find the target sheet
was not recorded, only the search within that sheet. The recorded code
started with selection of that sheet, rather than showing the code to find
that sheet.
How would I code a search over the entire workbook starting in a given sheet
and at a given cell within that sheet? Thanks for your time. Otto

 

[Jim Thomlinson]

Find is a worksheet function. To look across multiple sheets requires looping
through the sheets. Question... Do you want to loop through all of the sheets
or just some of the sheets. Do you want the find to be a loop that repeats
back to the beginning after all of the sheets have been searched or do you
want to stop at the last sheet and then have a message that there are no more
sheets to search?

Note that this is doable but probably a little bit more tricky than you
originally anticipated.

 

[Peter T]

Try Jan Karel Pierterse's FlexFind

http://www.jkp-ads.com/OfficeMarketPlaceFF-EN.asp

Regards,
Peter T

 

[FSt1]

hi
not to discourage you but i was wondering if you knew that this was built in
to xl.
Ctrl+F
click the options button. in the within box, select workbook.
xl will find all, and record all in the list box in the find dialog.

just wordering
regards
FSt1

 

[sbitaxi]

I agree with FSt1; Find All, Within Workbook will list all results,
the worksheet/cell address and the value in a handy find table.

 

[Peter T]

Perhaps not in his version. It's in XL2003, not sure about XP, not in
xl97/2000

Regards,
Peter T

 

[FSt1]

hi
i have xp also and it's there too which is why i brought up the fact that
find all is now builtin.

Regards
FSt1

 

[Otto Moehrbach]

Thanks Jim. The first time it would start with the first sheet and loop
through sheets until a find is accomplished. The second time it would start
searching from the active sheet and active row through subsequent sheets
until a find is accomplished. The search text is derived from the active
row. This would repeat each time the macro is run. The loop would exit
when the find is in the first sheet again. I think I can do this myself
except for the sequencing of sheets. How would I reference the sheets
without using the sheet names? Thanks again. Otto