Search Active Worksheet

[scott]

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

 

[Tom Ogilvy]

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

 

[scott]

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