Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search Active Worksheet
I'm trying to search all cells ONLY within the active worksheet and find
every instance of a string. My FindString sub below searches all worksheets and it only finds the 1st instance of the string before moving to another worksheet. Can someone help me modify my code to search for ALL instances of a string and limit the search to just the active worksheet? ' CODE: Sub FindString(sString As String) Dim bk As Workbook, sh As Worksheet Dim rng As Range, ans As Long For Each bk In Application.Workbooks For Each sh In bk.Worksheets Set rng = sh.Cells.Find(sString) If Not rng Is Nothing Then Dim sMsg, iStyle, sTitle, Help, Ctxt, Response sMsg = "Found at " & rng.Address(external:=True) & vbCrLf & vbCrLf & _ "Do you want to continue ?" iStyle = vbYesNo + vbInformation + vbDefaultButton2 sTitle = "Find String" Response = MsgBox(sMsg, iStyle, sTitle) If Response = vbYes Then 'Continue to search ElseIf Response = vbNo Then Exit Sub End If End If Next Next End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search Active Worksheet
Sub FindString(str as String)
Dim c As Range, rng1 As Range Dim firstAddress As String With Activesheet.Cells Set c = .Find(What:=sStr, _ After:=Range("A1"), _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) If Not c Is Nothing Then firstAddress = c.Address Do If rng1 Is Nothing Then Set rng1 = c Else Set rng1 = Union(rng1, c) End If Set c = .FindNext(c) Loop While c.Address < firstAddress End If End With If Not rng1 Is Nothing Then rng1.select Else MsgBox sStr & " was not found" End If End Sub -- Regards, Tom Ogilvy "scott" wrote: I'm trying to search all cells ONLY within the active worksheet and find every instance of a string. My FindString sub below searches all worksheets and it only finds the 1st instance of the string before moving to another worksheet. Can someone help me modify my code to search for ALL instances of a string and limit the search to just the active worksheet? ' CODE: Sub FindString(sString As String) Dim bk As Workbook, sh As Worksheet Dim rng As Range, ans As Long For Each bk In Application.Workbooks For Each sh In bk.Worksheets Set rng = sh.Cells.Find(sString) If Not rng Is Nothing Then Dim sMsg, iStyle, sTitle, Help, Ctxt, Response sMsg = "Found at " & rng.Address(external:=True) & vbCrLf & vbCrLf & _ "Do you want to continue ?" iStyle = vbYesNo + vbInformation + vbDefaultButton2 sTitle = "Find String" Response = MsgBox(sMsg, iStyle, sTitle) If Response = vbYes Then 'Continue to search ElseIf Response = vbNo Then Exit Sub End If End If Next Next End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search Active Worksheet
I ran your code on an active sheet containing many instances of the string,
but it didn't find any. I should have noted before that I need to search both cell values and formulas. These strings are actually part of formulas. Do you think that's why I don't get any "found message"? Also, I also need something like my below code within the sub to identify exactly which cells contain the formulas with the string. CODE SNIPPLET: Dim sMsg, iStyle, sTitle, Help, Ctxt, Response sMsg = "Found at " & rng.Address(external:=True) & vbCrLf & vbCrLf & _ "Do you want to continue ?" iStyle = vbYesNo + vbInformation + vbDefaultButton2 sTitle = "Find String" Response = MsgBox(sMsg, iStyle, sTitle) If Response = vbYes Then 'Continue to search ElseIf Response = vbNo Then Exit Sub End If "Tom Ogilvy" wrote in message ... Sub FindString(str as String) Dim c As Range, rng1 As Range Dim firstAddress As String With Activesheet.Cells Set c = .Find(What:=sStr, _ After:=Range("A1"), _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) If Not c Is Nothing Then firstAddress = c.Address Do If rng1 Is Nothing Then Set rng1 = c Else Set rng1 = Union(rng1, c) End If Set c = .FindNext(c) Loop While c.Address < firstAddress End If End With If Not rng1 Is Nothing Then rng1.select Else MsgBox sStr & " was not found" End If End Sub -- Regards, Tom Ogilvy "scott" wrote: I'm trying to search all cells ONLY within the active worksheet and find every instance of a string. My FindString sub below searches all worksheets and it only finds the 1st instance of the string before moving to another worksheet. Can someone help me modify my code to search for ALL instances of a string and limit the search to just the active worksheet? ' CODE: Sub FindString(sString As String) Dim bk As Workbook, sh As Worksheet Dim rng As Range, ans As Long For Each bk In Application.Workbooks For Each sh In bk.Worksheets Set rng = sh.Cells.Find(sString) If Not rng Is Nothing Then Dim sMsg, iStyle, sTitle, Help, Ctxt, Response sMsg = "Found at " & rng.Address(external:=True) & vbCrLf & vbCrLf & _ "Do you want to continue ?" iStyle = vbYesNo + vbInformation + vbDefaultButton2 sTitle = "Find String" Response = MsgBox(sMsg, iStyle, sTitle) If Response = vbYes Then 'Continue to search ElseIf Response = vbNo Then Exit Sub End If End If Next Next End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search for active file name (modified), then.... | Excel Programming | |||
Basic Question - How do I return the worksheet number of the active worksheet? | Excel Programming | |||
create a form for search text in active sheet then got to selected | Excel Programming | |||
Create a search Field within a worksheet to search command buttons | Excel Programming | |||
VBA newbie - can i search the spreadsheet based on the active cell? | Excel Programming |