find text
Here is some code that searches Column B on the active sheet. It creates the
array you asked for along with a rnage object rngFoundAll which is all of the
found cells.
Public Sub FindStuff()
Dim rngToSearch As Range
Dim rngFound As Range
Dim rngFoundAll As Range
Dim rngFirst As Range
Dim aryRowNumbers() As Long
Dim wks As Worksheet
Dim lngCounter As Long
lngCounter = 0
Set wks = ActiveSheet
Set rngToSearch = wks.Columns("B")
Set rngFound = rngToSearch.Find("This")
If rngFound Is Nothing Then
MsgBox "Sorry nothing was found"
Else
Set rngFoundAll = rngFound
Set rngFirst = rngFound
Do
Set rngFoundAll = Union(rngFoundAll, rngFound)
ReDim Preserve aryRowNumbers(lngCounter)
aryRowNumbers(lngCounter) = rngFound.Row
lngCounter = lngCounter + 1
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = rngFirst.Address
rngFoundAll.Select
MsgBox UBound(aryRowNumbers) + 1
End If
End Sub
--
HTH...
Jim Thomlinson
"Dave B" wrote:
Can someone suggest a simple way to find each "target text" in a
worksheet then create an array with the row # of each found "target
text"? Something like:
i = 0
Cells(1, 1).Select
Cells.Find("target text"...)
Do Until ?????
Cell.FindNext(...).Activate
intMatchedRows(i) = ActiveCell.Row
i = i + 1
Loop
Thanks.
|