| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Find over entire workbook
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Using Find over entire workbook
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. -- HTH... Jim Thomlinson "Otto Moehrbach" wrote: 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Using Find over entire workbook
Try Jan Karel Pierterse's FlexFind
http://www.jkp-ads.com/OfficeMarketPlaceFF-EN.asp Regards, Peter T "Otto Moehrbach" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Using Find over entire workbook
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 "Otto Moehrbach" wrote: 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Using Find over entire workbook
I agree with FSt1; Find All, Within Workbook will list all results,
the worksheet/cell address and the value in a handy find table. On Jul 30, 3:28*pm, FSt1 wrote: 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 "Otto Moehrbach" wrote: 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Using Find over entire workbook
Perhaps not in his version. It's in XL2003, not sure about XP, not in
xl97/2000 Regards, Peter T "FSt1" wrote in message ... 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 "Otto Moehrbach" wrote: 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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Using Find over entire workbook
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 "Peter T" wrote: Perhaps not in his version. It's in XL2003, not sure about XP, not in xl97/2000 Regards, Peter T "FSt1" wrote in message ... 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 "Otto Moehrbach" wrote: 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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Using Find over entire workbook
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 "Jim Thomlinson" wrote in message ... 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. -- HTH... Jim Thomlinson "Otto Moehrbach" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Find duplicates in entire workbook | Excel Discussion (Misc queries) | |||
| Find duplicates in entire workbook | Excel Discussion (Misc queries) | |||
| How to Copy entire Worksheet from Workbook S to Workbook D | Excel Programming | |||
| Find/Replace Across Entire Workbook in Excel 2000 | Excel Discussion (Misc queries) | |||
| Find character in entire workbook and format color | Excel Programming | |||
![Otto Moehrbach[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
