Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 10-20-2024, 11:05 PM
ImAhNoBoDy ImAhNoBoDy is offline Cell Range to Address Array Windows 7 64bit Cell Range to Address Array Office 2007
Novice
Cell Range to Address Array
 
Join Date: Jan 2012
Posts: 17
ImAhNoBoDy is on a distinguished road
Default Cell Range to Address Array

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.
Reply With Quote
  #2  
Old 10-21-2024, 12:38 AM
ArviLaanemets ArviLaanemets is offline Cell Range to Address Array Windows 8 Cell Range to Address Array Office 2016
Expert
 
Join Date: May 2017
Posts: 932
ArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant future
Default

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)))
Reply With Quote
  #3  
Old 10-21-2024, 02:18 AM
p45cal's Avatar
p45cal p45cal is offline Cell Range to Address Array Windows 10 Cell Range to Address Array Office 2021
Expert
 
Join Date: Apr 2014
Posts: 947
p45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond repute
Default

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

2024-10-21_101625.jpg
Reply With Quote
  #4  
Old 10-21-2024, 02:40 AM
p45cal's Avatar
p45cal p45cal is offline Cell Range to Address Array Windows 10 Cell Range to Address Array Office 2021
Expert
 
Join Date: Apr 2014
Posts: 947
p45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond repute
Default

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
Reply With Quote
  #5  
Old 10-22-2024, 12:14 AM
ImAhNoBoDy ImAhNoBoDy is offline Cell Range to Address Array Windows 7 64bit Cell Range to Address Array Office 2007
Novice
Cell Range to Address Array
 
Join Date: Jan 2012
Posts: 17
ImAhNoBoDy is on a distinguished road
Default

Quote:
Originally Posted by ArviLaanemets View Post
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 View Post
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 View Post
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)))
Reply With Quote
  #6  
Old 10-22-2024, 12:20 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Cell Range to Address Array Windows 10 Cell Range to Address Array Office 2021
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,920
Pecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond repute
Default

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 ?
__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #7  
Old 10-22-2024, 03:20 AM
p45cal's Avatar
p45cal p45cal is offline Cell Range to Address Array Windows 10 Cell Range to Address Array Office 2021
Expert
 
Join Date: Apr 2014
Posts: 947
p45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond repute
Default

Quote:
Originally Posted by ImAhNoBoDy View Post
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.
Reply With Quote
  #8  
Old 10-22-2024, 05:24 AM
ImAhNoBoDy ImAhNoBoDy is offline Cell Range to Address Array Windows 11 Cell Range to Address Array Office 2021
Novice
Cell Range to Address Array
 
Join Date: Jan 2012
Posts: 17
ImAhNoBoDy is on a distinguished road
Default

Quote:
Originally Posted by p45cal View Post
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
Reply With Quote
Reply



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
Cell Range to Address Array 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
Cell Range to Address Array Error: AGGREGATE_doesn't ignore hidden rows for particualr array range adisco Excel 1 01-10-2017 10:59 AM
Cell Range to Address Array 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

Other Forums: Access Forums

All times are GMT -7. The time now is 07:34 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2025, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2025 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft