Return row index of all cells where text is found.

[gmaxey]

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.

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!!

[Alansidman]

An alternative means with Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
    #"Filtered Rows" = Table.SelectRows(#"Added Index", each ([Column1] = "Pears")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Column1"})
in
    #"Removed Columns"

[gmaxey]

Thank you. I'm not sure how I would employ that method. I don't need the data displayed in a sheet. I need the row indexes for additional processing. E.g., in a larger process:

For lngIndex = 1 To oCol.Count 'Do some things. Next

[p45cal]

change:

Set oRng = .Cells.Find(What:="Pears")

to:

Set oRng = .Cells.Find(What:="Pears", after:=.Cells(.Cells.Count))

The default place .Find starts looking is after the first cell in the range it's searching, which is the 2nd cell).
The adjustment makes it start searching after the last cell in that range, so it starts searching from the first cell in the range.

[batman1]

I would change it to (don't use On Error... ONLY to exit Do... Loop)

If Not oRng Is Nothing Then
    FirstAdd = oRng.Address
    Do
        oCol.Add oRng.Row, CStr(oRng.Row)
        Set oRng = .FindNext(oRng)
    Loop While oRng.Address <> FirstAdd
End If

[gmaxey]

Very nice. Thank you.


So, can I assume that there is no built-in VBA function that would return an array of row indexes? Similar to the return of all the cell addresses given by the Find dialog when "Find All" is chosen.

[p45cal]

Originally Posted by gmaxey

So, can I assume that there is no built-in VBA function that would return an array of row indexes? Similar to the return of all the cell addresses given by the Find dialog when "Find All" is chosen.

No built-in one, no, but you can do something in vba like:

x = [TOCOL(IF(A1:A9="Pears",ROW(A1:A9),NA()),3,FALSE)]

which with your data will get you a 2d array x(1 to 3, 1 to 1) which you could transpose to 1d array with

x = [transpose(TOCOL(IF(A1:A9="Pears",ROW(A1:A9),NA()),3,FALSE))]

However, it matters more what you intend to do with the results whether your function returns a range object or just an array of row numbers, or something else?

[gmaxey]

Thanks, but either of those returns an error 2029 with my simple test data in Column 1


Sub A()
Dim x
x = [TOCOL(IF(A1:A9="Pears",ROW(A1:A9),NA()),3,FALSE)]
End Sub

[p45cal]

Version of Excel?
If still 2019 then that vba won't work.
As said earlier, what are you going to do with the results? The answer to that will dictate the vba code.

[gmaxey]

Yes, still 2019. Sorry.


I just need to loop through the indexes. So if the word "pear" is in rows 2, 3 and 6



For lngIndex = 1 To oCol.Count Debug.Print oCol.Item(lngIndex) 'Should print 2, 3 and 6 Next

[p45cal]

Sub blah()
With ThisWorkbook.Worksheets(1).Columns(1)       'any single column range, eg. Range("B3:B100")
  x = .Value
  FirstRow = .Row
  For rw = 1 To UBound(x)
    If x(rw, 1) = "Pears" Then Debug.Print rw + FirstRow - 1
  Next rw
End With
End Sub

[gmaxey]

p45cal,


Sorry for late reply. Thanks for that. I can make that work.