Return row index of all cells where text is found.

gmaxey · #1 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!!

Alansidman · #2 01-24-2025, 12:35 AM

An alternative means with Power Query

Code:

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 · #3 01-24-2025, 05:23 AM

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 · #4 01-24-2025, 06:18 AM

change:

Code:

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

to:

Code:

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 · #5 01-24-2025, 07:26 AM

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

Code:

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 · #6 01-24-2025, 01:02 PM

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 · #7 01-24-2025, 01:27 PM

Quote:

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:

Code:

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

Code:

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 · #8 01-25-2025, 06:25 AM

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 · #9 01-25-2025, 07:13 AM

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 · #10 01-25-2025, 10:59 AM

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 · #11 01-25-2025, 01:48 PM

Code:

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 · #12 01-29-2025, 05:07 AM

p45cal,

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

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Excel VLOOKUP - Return cells that are not blank from range of specified cells	djlw84	Excel	10	05-09-2022 11:59 AM
Index Match and return mutiple results from a table	Bosslb	Excel	7	09-25-2021 11:42 PM
need Index, Match, Max to return SUMPRODUCT?	IRHSAM	Excel	0	11-17-2017 09:48 AM
Index to return value based on 2 conditions	george batshon	Excel	2	01-28-2017 05:11 AM
find a set of characters in a string and return a 0 (zero) if not found	MaineLady	Excel	2	11-05-2015 03:23 PM