Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search all sheets
I need a short macro that will search all sheets in the
active workbook for a certain string of text, and when it finds it, it stops at that point on that sheet. I asked a question of this newsgroup some time ago about doing a Find & Replace operation across multiple sheets, and got a reply, but I cannot figure out how to amend the macro to just find and not find and replace. Could anyone suggest how I could alter the macro below just to do a Find on multiple sheets? Thanks, Steve The following macro will loop through all of the sheets in the workbook and remove every occurrence of 1, and replace it with 2, just for example. Sub ReplaceAll() On Error Resume Next Sheets(1).Select For i = 1 To Sheets.Count Cells.Select Selection.Replace What:="1", Replacement:="2", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False Range("A1").Select ActiveSheet.Next.Select Next i End Sub The On Error Resume Next line prevents the error developed when the last sheet is encountered and the .Next.Select line tries to execute, which it can not, since it is at the last sheet already. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search all sheets
Sub FindAll()
Dim sh As Worksheet Dim rng As Range, firstAddress as String For Each sh In ThisWorkbook.Worksheets Set rng = sh.Cells.Find(What:="1", 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 End Sub -- Regards, Tom Ogilvy "Steve Wylie" wrote in message ... I need a short macro that will search all sheets in the active workbook for a certain string of text, and when it finds it, it stops at that point on that sheet. I asked a question of this newsgroup some time ago about doing a Find & Replace operation across multiple sheets, and got a reply, but I cannot figure out how to amend the macro to just find and not find and replace. Could anyone suggest how I could alter the macro below just to do a Find on multiple sheets? Thanks, Steve The following macro will loop through all of the sheets in the workbook and remove every occurrence of 1, and replace it with 2, just for example. The On Error Resume Next line prevents the error developed when the last sheet is encountered and the .Next.Select line tries to execute, which it can not, since it is at the last sheet already. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search all sheets
Hi Tom
I cannot get this macro to work. I did make one alteration to it - to allow me to input a value rather than have to include it in the macro text. I paste the amended text below, but it still doesn't work, even before I made my amendment: Sub FindAcrossAllSheets() Dim sh As Worksheet Dim rng As Range, firstAddress As String Dim FindVal As String FindVal = InputBox("Enter text to find") For Each sh In ThisWorkbook.Worksheets Set rng = sh.Cells.Find(What:=FindVal, 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 End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search all sheets
It worked fine for me, but try this:
Sub FindAll() Dim sh As Worksheet Dim rng As Range Dim sStr As String sStr = InputBox("Enter item to search for") If sStr = "" Then MsgBox "You hit cancel" End If For Each sh In ThisWorkbook.Worksheets Set rng = sh.Cells.Find(What:=sStr, _ After:=sh.Range("A1"), _ 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 End Sub It should put each found cell in the upper left corner of the window. -- Regards, Tom Ogilvy "Steve" wrote in message ... Hi Tom I cannot get this macro to work. I did make one alteration to it - to allow me to input a value rather than have to include it in the macro text. I paste the amended text below, but it still doesn't work, even before I made my amendment: Sub FindAcrossAllSheets() Dim sh As Worksheet Dim rng As Range, firstAddress As String Dim FindVal As String FindVal = InputBox("Enter text to find") For Each sh In ThisWorkbook.Worksheets Set rng = sh.Cells.Find(What:=FindVal, 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 End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search all sheets
Ah - I know what I was doing wrong. As per usual, I put the macro into one
workbook spreadsheet on its own, then called up my workbook I wanted to work on and played the macro. The macro only searches the workbook that it is recorded in. It will activate from another open workbook, but will only search the workbook it is recorded in. Does this mean I must record it on every workbook I need to search through, or can the macro be amended to search the workbook currently having focus? Or is there a way I should "add-in" the macro workbook into the one that contains the data? Steve |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search all sheets
Put it in a general module of Personal.xls or other workbook that will be
open Sub FindAll() Dim sh As Worksheet Dim rng As Range Dim sStr As String sStr = InputBox("Enter item to search for") If sStr = "" Then MsgBox "You hit cancel" End If For Each sh In ActiveWorkbook.Worksheets Set rng = sh.Cells.Find(What:=sStr, _ After:=sh.Range("A1"), _ 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 End Sub I have changed ThisWorkbook to ActiveWorkbook. -- Regards, Tom Ogilvy "Steve Wylie" wrote in message ... Ah - I know what I was doing wrong. As per usual, I put the macro into one workbook spreadsheet on its own, then called up my workbook I wanted to work on and played the macro. The macro only searches the workbook that it is recorded in. It will activate from another open workbook, but will only search the workbook it is recorded in. Does this mean I must record it on every workbook I need to search through, or can the macro be amended to search the workbook currently having focus? Or is there a way I should "add-in" the macro workbook into the one that contains the data? Steve |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search all sheets
Thanks for your help Tom, that's just what I'm looking for!
Cheers Steve |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search a value in all w/sheets and make a list. | Excel Discussion (Misc queries) | |||
Search in Multiple Sheets | Excel Discussion (Misc queries) | |||
Search for datats in different Exel sheets | Excel Discussion (Misc queries) | |||
Time Sheets (Yes, I've already done a search on previous posts) | New Users to Excel | |||
Search Mutiple Excel Sheets. | Excel Programming |