Cell Range to Address Array

ImAhNoBoDy · #1 10-20-2024, 11:05 PM

Is there a way to get an range and then get all the addresses of the range?

The range would be A1:E1, the output would be $A$1,$B$1,$C$1,$D$1,$E$1.

ArviLaanemets · #2 10-21-2024, 12:38 AM

Select the range A1:E1, and define it as named range, e.g. with name as nMyRange

To get a value from nMyRange, enter into cell, where you want to calculate a value from mMyRange, the formula

Code:

=INDEX(nMyRange, x)

, where x is any expression which returns either 1, 2, 3, 4, or 5 - i.e. the formula returns the x'th value from nMyRange.

To get the address of x'th cell in nMyRange, use the formula like

Code:

=ADDRESS(ROW(INDEX(nMyRange,x)),COLUMN(INDEX(nMyRange,x)))

p45cal · #3 10-21-2024, 02:18 AM

Code:

=REDUCE("",A1:E1,LAMBDA(a,b,TEXTJOIN(",",TRUE,a,CELL("address",b))))

2024-10-21_101625.jpg

p45cal · #4 10-21-2024, 02:40 AM

Otherwise, a user-defined function:

Code:

Function addresses(rng As Range) As String
For Each cll In rng.Cells
  addresses = addresses & "," & cll.Address
Next cll
addresses = Mid(addresses, 2)
End Function

2024-10-21_103925.jpg

ImAhNoBoDy · #5 10-22-2024, 12:14 AM

Quote:

Originally Posted by ArviLaanemets

Select the range A1:E1, and define it as named range, e.g. with name as nMyRange

To get a value from nMyRange, enter into cell, where you want to calculate a value from mMyRange, the formula

Code:

=INDEX(nMyRange, x)

, where x is any expression which returns either 1, 2, 3, 4, or 5 - i.e. the formula returns the x'th value from nMyRange.

To get the address of x'th cell in nMyRange, use the formula like

Code:

=ADDRESS(ROW(INDEX(nMyRange,x)),COLUMN(INDEX(nMyRange,x)))

This only gives me 1 address when I try it. I'm not evaluating any values though, when generating the address array.

Quote:

Originally Posted by p45cal

Code:

=REDUCE("",A1:E1,LAMBDA(a,b,TEXTJOIN(",",TRUE,a,CELL("address",b))))

This solution does work for Excel 365 products. I'm using Excel 2021 and I don't have the LAMDA or REDUCE functions.

Quote:

Originally Posted by p45cal

Otherwise, a user-defined function:

Code:

Function addresses(rng As Range) As String
For Each cll In rng.Cells
  addresses = addresses & "," & cll.Address
Next cll
addresses = Mid(addresses, 2)
End Function

This solution also works, but Polaris Office on my tablet for whatever does not display the address array. So what I do is edit the spreadsheet on my computer and transfer it to my tablet to work on.

As I was searching for another solution, I ended up on a site that creates excel formulas using AI. It came up with this and it works:

Code:

=TEXTJOIN(",",TRUE,ADDRESS(ROW(A1:E1),COLUMN(A1:E1)))

Pecoflyer · #6 10-22-2024, 12:20 AM

Quote:

I'm using Excel 2021 and I don't have the LAMDA or REDUCE functions.

As your profile says you use XL2007, you could perhaps start by updating it ?

p45cal · #7 10-22-2024, 03:20 AM

Quote:

Originally Posted by ImAhNoBoDy

As I was searching for another solution, I ended up on a site that creates excel formulas using AI. It came up with this and it works:

Code:

=TEXTJOIN(",",TRUE,ADDRESS(ROW(A1:E1),COLUMN(A1:E1)))

Excellent. One of those too few occasions when AI produces a sensible result. Could you provide a link to that site please?

ps, you could use 4 as the ADDRESS function's 3rd argument to lose the $ symbols too.

ImAhNoBoDy · #8 10-22-2024, 05:24 AM

Quote:

Originally Posted by p45cal

Excellent. One of those too few occasions when AI produces a sensible result. Could you provide a link to that site please?

ps, you could use 4 as the ADDRESS function's 3rd argument to lose the $ symbols too.

The site I used was this:

https://aiexcelbot.com/formulas/logical/and

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Using IF AND to filter number in a range, works with dragging, but not in an Array	mda99das	Excel	5	11-17-2022 01:27 PM
Loading an Excel range to an array in VBS	BobBridges	Excel Programming	1	12-25-2020 01:04 PM
Clear all cell colors within a range starting at cell A8 and change row of active cell to yellow	FUGMAN	Excel Programming	7	02-05-2017 08:37 AM
Error: AGGREGATE_doesn't ignore hidden rows for particualr array range	adisco	Excel	1	01-10-2017 10:59 AM
If value of cell A Matches a value in a Range of cells (column) then add value of cell A to cell C	rick10r	Excel	1	07-05-2016 12:07 PM