Microsoft Office Forums - View Single Post - Return row index of all cells where text is found.

gmaxey · 01-23-2025, 09:28 PM

I have a very simply worksheet with the following content in column 1

Pears
Pears
Apples
Peaches
Pears

I need to return the row index of each cell containing the text "Pears"

I have cobbled together the code below which returns:
2, 5 and 1. Yes, that is the correct numbers but they are not in the correct order.

I have already discovered that if I add a column heading e.g.,

Fruit
Pears
Pears
Apples
Peaches
Pears

... then the code correctly returns 2, 3, and 6.

Some questions are included in the code.

Code:

Sub GetRowIndexes()
Dim lngIndex As Long
Dim oSheet As Worksheet
Dim oRng
Dim oCol As New Collection
  Set oSheet = ThisWorkbook.Worksheets(1)
    With oSheet.Columns(1)
      Set oRng = .Cells.Find(What:="Pears")
        'Why is the first instance not found if it is located in the first row? 

        If Not oRng Is Nothing Then
          On Error GoTo Err_GetOut
            Do Until oRng Is Nothing
              oCol.Add oRng.Row, CStr(oRng.Row)
              Set oRng = .FindNext(oRng) 

              'Is there a better way to escape the loop?
              'Is there a better way overall to achieve this objective?

            Loop
        End If
    End With
Err_GetOut:
  Set oRng = Nothing
  For lngIndex = 1 To oCol.Count
    Debug.Print oCol.Item(lngIndex)
  Next
lbl_Exit:
  Exit Sub
End Sub

Thank you!!