View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ron Rosenfeld[_2_] Ron Rosenfeld[_2_] is offline
external usenet poster
 
Posts: 1,045
Default split range address into individual cell addresses

On Sat, 4 Aug 2012 06:36:41 -0700 (PDT), noname wrote:

On Saturday, August 4, 2012 12:04:54 PM UTC+5:30, noname wrote:
hi,



Given a range say, "A1:E1", i need a formula to return a string of cells "A1","B1","C1","D1","E1". I do not want to use helper columns.



has anyone tried something like this?



Hi Ron,

thanks for your reply.
my question is very clear and i.e. to split a range address into its individual cell addresses (cell references). the above formulae containing index functions only returns the VALUE at those indexes and not their ADDRESSES. Also, it means, if you have 30 cells in a range, it would involve writing 30 INDEX functions and concatenating them together. Will appear too long!

your VBA solution takes into account a FOR EACH LOOP, which i would like to avoid. i need a shorter code aka a one- liner!


Your question may have been clear to you, but it is open to a variety of interpretations.

A "string of cells" does not convey the same meaning to me as would the phrase "A string of cell addresses"; and, given your example, it seems that what you really want is:
Given a range reference, return a string of cell addresses delimited by quotation marks and separated by commas.
It is also not clear (to me) if the "range" is being entered as a string or as a reference.

Furthermore, your requirement for a one-liner and "shorter code" suggests there is something else going on than mere production of the string. Care to share that?

In any event, taking into account that the range reference may be passes as either a string or as a range, the following UDF should return the result you show in your original question. It does require a loop and does some rudimentary error checking.

=============================
Option Explicit
Function RangeAddresses(ByVal rg) As String
Dim r As Range, c As Range
Dim v() As Variant, i As Long

On Error GoTo Handler
Select Case VarType(rg)
Case Is = vbArray + vbVariant
Set r = rg
Case Is = vbString
Set r = Range(rg)
Case Is = vbEmpty
Set r = rg
End Select
On Error GoTo 0

ReDim v(0 To r.Count - 1)
For Each c In r
v(i) = c.Address
i = i + 1
Next c
RangeAddresses = """" & Join(v, """,""") & """"
Exit Function

Handler:
MsgBox ("Invalid Range Reference")
End Function
================================