row number of any cell that contains a certain word

[sedgely]

i need to return the row numbers of any row where the first cell (column A) contains a certain text string. i know how to look for the row number of the first instance of the text, but how do i continue looking down the column for more instances?

Option Explicit


Function findRows()

'declare variables

Dim strSearchString 'text to search for
Dim ws1 As Worksheet 'worksheet to search
Dim ws2 As Worksheet 'destination worksheet
Dim rngSearch As Range 'range of cells to search
Dim lngRowNum As Long 'holds row number
Dim rngDest As Range 'cells to hold returned row numbers

strSearchString = "Ad-hoc Duties"
Set ws1 = Worksheets("sheet1")
Set ws2 = Worksheets("sHider")
Set rngSearch = ws1.Range("A:A")
Set rngDest = ws2.Range("h2")
lngRowNum = rngSearch.Find(strSearchString).Row

rngDest.Value = lngRowNum

End Function

Cheers, Craig
Si fractum non sit, noli id reficere

 

[Loomah]

hi
have a look in the help files under FIND and use the example given there as the basis on which you can build a solution to your problem.

any problems adjusting or understanding the code then jsut post back

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[Drederick]

Sub list_rows()
Dim search As String, i As Integer, j As Integer

search = "world"
j = 1

Range("B:B").EntireColumn.Clear

For i = 1 To 1000
If InStr(1, UCase(Range("A" & i)), UCase(search)) > 0 Then
Range("B" & j) = i
j = j + 1
End If
Next i
End Sub

 

[AnotherHiggins]

Craig,

Out of curiosity, what do you want to do with these numbers? Do you want to do something specific to all rows that are found?

I ask because I suspect there is a faster way to do what you want.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.