Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search, find or lookup defined text in text string | Excel Worksheet Functions | |||
can you find specific text in a string ignoring any other text | Excel Discussion (Misc queries) | |||
open some txt files ,find text , copy the text before that to a single cell | Excel Programming | |||
find and delete text, find a 10-digit number and put it in a textbox | Excel Programming | |||
backwards find function to find character in a string of text | Excel Programming |