find text
 

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.

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.

find text
 

Try this.

Sub FindText()

Dim aRows() As Integer
Dim r%, wRowFound%
Dim c As Range
Const txt As String = "fd"

With Sheet1
For r = 1 To .Cells.SpecialCells(xlCellTypeLastCell).Row
For Each c In .Range(.Cells(r, 1), .Cells(r,
..Cells.SpecialCells(xlCellTypeLastCell).Column))
If InStr(1, c.Value, txt) 0 Then
wRowFound = wRowFound + 1
ReDim Preserve aRows(1 To wRowFound)
aRows(wRowFound) = r
Exit For
End If
Next c
Next r
End With

End Sub

Alok


"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.