Return row index of all cells where text is found.

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

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