What would be the most efficient way using programming code to find all
instances of a text phrase within a selected range of cells? I'm using an
Inputbox to capture the text to search for, and when the text is found, run
whatever code I want, then find the next occurance, run my code, etc. After
it's found the last occurance, stop.

I'm finding that my code wants to keep going back to the start and
continuously looping (I'm using a "For Each ItemFound in Selection" approach
unsuccessfully so far). Thanks for any suggestions!
--
Steve C

This procedure finds all instances of the text within a range you specify,
and adds them all to a collection, which then you can modify.

Sub FindAll()
Dim c As Range
Dim firstAddress As Variant
Dim SearchRange As Range
Dim FindThis As Variant
Dim Collection As New Collection
Dim Item As Variant

Item = ""

'Set the string you're looking for
FindThis = "Some String"

'Set your search range here
'Loops, finds all results, and adds them to a collection appropriately named
"Collection"
With ActiveSheet.Range("A:A")
Set c = .Find(FindThis, LookIn:=xlValues, MatchCase:=False)
If Not c Is Nothing Then
firstAddress = c.Address
Do
On Error Resume Next
Collection.Add c
On Error GoTo 0
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
MsgBox "Total number of search results: " & Collection.count
Else
MsgBox "The target ''" & FindThis & "'' could not be found.",
vbExclamation, "Search Result"
End If
End With

End Sub

--------
Cheers,
Anony


"Steve C" wrote:

What would be the most efficient way using programming code to find all
instances of a text phrase within a selected range of cells? I'm using an
Inputbox to capture the text to search for, and when the text is found, run
whatever code I want, then find the next occurance, run my code, etc. After
it's found the last occurance, stop.

I'm finding that my code wants to keep going back to the start and
continuously looping (I'm using a "For Each ItemFound in Selection" approach
unsuccessfully so far). Thanks for any suggestions!
--
Steve C

A belated thank you to both of you for your responses. They help a lot!
--
Steve C


"Anony" wrote:

This procedure finds all instances of the text within a range you specify,
and adds them all to a collection, which then you can modify.

Sub FindAll()
Dim c As Range
Dim firstAddress As Variant
Dim SearchRange As Range
Dim FindThis As Variant
Dim Collection As New Collection
Dim Item As Variant

Item = ""

'Set the string you're looking for
FindThis = "Some String"

'Set your search range here
'Loops, finds all results, and adds them to a collection appropriately named
"Collection"
With ActiveSheet.Range("A:A")
Set c = .Find(FindThis, LookIn:=xlValues, MatchCase:=False)
If Not c Is Nothing Then
firstAddress = c.Address
Do
On Error Resume Next
Collection.Add c
On Error GoTo 0
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
MsgBox "Total number of search results: " & Collection.count
Else
MsgBox "The target ''" & FindThis & "'' could not be found.",
vbExclamation, "Search Result"
End If
End With

End Sub

--------
Cheers,
Anony


"Steve C" wrote:

What would be the most efficient way using programming code to find all
instances of a text phrase within a selected range of cells? I'm using an
Inputbox to capture the text to search for, and when the text is found, run
whatever code I want, then find the next occurance, run my code, etc. After
it's found the last occurance, stop.

I'm finding that my code wants to keep going back to the start and
continuously looping (I'm using a "For Each ItemFound in Selection" approach
unsuccessfully so far). Thanks for any suggestions!
--
Steve C

Is your range multiple columns, multiple rows, both?

You can loop from range.row to range.row+range.rows.count and from range
..column to range.column+range.columns.count

Hope that helps some.

Peter Richardson

"Steve C" wrote:

What would be the most efficient way using programming code to find all
instances of a text phrase within a selected range of cells? I'm using an
Inputbox to capture the text to search for, and when the text is found, run
whatever code I want, then find the next occurance, run my code, etc. After
it's found the last occurance, stop.

I'm finding that my code wants to keep going back to the start and
continuously looping (I'm using a "For Each ItemFound in Selection" approach
unsuccessfully so far). Thanks for any suggestions!
--
Steve C