| Forum Home | ||||
| Press F1 | ||||
| Thread ID: 61218 | 2005-08-29 00:09:00 | Excel help - find feature | jonp (7517) | Press F1 |
| Post ID | Timestamp | Content | User | ||
| 384355 | 2005-08-29 00:09:00 | If i use the find function in excel it appears to only search the worksheet I am in. Is there a function that allows the entire workbook to be searched without having to go into each indivdual work sheet ? ta very much |
jonp (7517) | ||
| 384356 | 2005-08-29 01:49:00 | You didn't say what version of Excel you are using. In XP click on Options in the Find and Replace dialog box and then choose worksheet from the drop down list beside Within: Hope this helps |
wlowscrk (395) | ||
| 384357 | 2005-08-29 06:07:00 | If you don't have ExcelXP, the following macro code adapted from Tom Ogilvie will do what you want. It asks you to input what you want to find, then goes through each worksheet in turn, stopping at each found instance before proceeding to the next. Sub FindAll() Dim sh As Worksheet Dim rng As Range, firstAddress As String Dim fv As String fv = InputBox("ENTER ITEM TO FIND") For Each sh In ThisWorkbook.Worksheets Set rng = sh.Cells.Find(what:=fv, After:=ActiveCell, _ LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False) If Not rng Is Nothing Then firstAddress = rng.Address Do If Not rng Is Nothing Then Application.Goto rng, True MsgBox "Hit key to continue" End If Set rng = sh.Cells.FindNext(rng) Loop Until rng.Address = firstAddress End If Next HTH |
rad_s4 (7401) | ||
| 384358 | 2005-08-29 21:44:00 | If i use the find function in excel it appears to only search the worksheet I am in. Is there a function that allows the entire workbook to be searched without having to go into each indivdual work sheet ? ta very much Hi, I take it you have Excel 97/2k? The extended Find feature is only in later versions. I have created an addin that mimics the Find option for later versions at home, so if you send me a PM of your email address I will send it to you tonight. regards, Graham |
Parry (5696) | ||
| 1 | |||||