A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

split range address into individual cell addresses



 
 
Thread Tools Display Modes
  #1  
Old August 4th 12, 07:34 AM posted to microsoft.public.excel.worksheet.functions
noname
external usenet poster
 
Posts: 97
Default split range address into individual cell addresses

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?
Ads
  #2  
Old August 4th 12, 12:37 PM posted to microsoft.public.excel.worksheet.functions
Ron Rosenfeld[_2_]
external usenet poster
 
Posts: 1,045
Default split range address into individual cell addresses

On Fri, 3 Aug 2012 23:34:54 -0700 (PDT), 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?


Your question is not clear.

If you want to return a string that consists of the concatenation of the cells in your defined range, Excel does not have a built in function to do that unless you know the orientation and dimension of the string beforehand.

In your example, which is a horizontal string consisting of five elements, you could extend the following to the fifth element:

=INDEX($A$1:$E$1,1,1)&" "&INDEX($A$1:$E$1,1,2) & " " & INDEX($A$1:$E$1,1,3) ...

and there are other formulas that could work similarly, but would have the same limitations.

If a VBA solution is allowed, it is relatively simple.

Here is a User Defined Function that allows you to supply a range of any size, and includes an optional argument for a separator. If the argument is omitted, the routine will insert a <space> between each element.

To enter this User Defined Function (UDF), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula like

=MultiCellConcat(A1:E1)

in some cell.

===========================================
Option Explicit
Function MultiCellConcat(rg As Range, Optional Sep As String = " ") As String
Dim s As String
Dim c As Range
For Each c In rg
s = s & Sep & c
Next c
MultiCellConcat = Mid(s, 2)
End Function
================================================== ==
  #3  
Old August 4th 12, 02:36 PM posted to microsoft.public.excel.worksheet.functions
noname
external usenet poster
 
Posts: 97
Default split range address into individual cell addresses

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!

  #4  
Old August 4th 12, 04:28 PM posted to microsoft.public.excel.worksheet.functions
noname
external usenet poster
 
Posts: 97
Default split range address into individual cell addresses

On Saturday, August 4, 2012 7:06:41 PM UTC+5:30, 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!



i worked out this worksheet Array formula:

={SUBSTITUTE(CELL("address",INDEX(A1:E1,1,COLUMN(A 1:E1))),"$","")}

which gives me correct result i.e. "A1","B1","C1","D1","E1" across multiple cells.

But question is: how do you i convert this into an equivalent single-line code in VBA? as far as i know, the worksheet CELL function does not have any equivalent in VBA.
  #5  
Old August 4th 12, 05:41 PM posted to microsoft.public.excel.worksheet.functions
Ron Rosenfeld[_2_]
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
================================
  #6  
Old August 4th 12, 06:06 PM posted to microsoft.public.excel.worksheet.functions
Ron Rosenfeld[_2_]
external usenet poster
 
Posts: 1,045
Default split range address into individual cell addresses

On Sat, 4 Aug 2012 08:28:41 -0700 (PDT), noname > wrote:


>
>i worked out this worksheet Array formula:
>
>={SUBSTITUTE(CELL("address",INDEX(A1:E1,1,COLUMN( A1:E1))),"$","")}
>
>which gives me correct result i.e. "A1","B1","C1","D1","E1" across multiple cells.
>
>But question is: how do you i convert this into an equivalent single-line code in VBA? as far as i know, the worksheet CELL function does not have any equivalent in VBA.


Are you now changing your specifications?
Intially, you described a quote-delimited, comma separated string. But this formula of yours only returns one address per cell, and only in a range the same vector and size as your original range reference.

I think your description of what you want is not as clear to me as it seems to be to you.

Perhaps you could rethink how you want to express your requirements; and maybe some more information on the overall project might be useful in devising an efficient solution.
  #7  
Old August 4th 12, 06:50 PM posted to microsoft.public.excel.worksheet.functions
noname
external usenet poster
 
Posts: 97
Default split range address into individual cell addresses

On Saturday, August 4, 2012 10:36:05 PM UTC+5:30, Ron Rosenfeld wrote:
> On Sat, 4 Aug 2012 08:28:41 -0700 (PDT), noname wrote:
>
>
>
>
>
> >

>
> >i worked out this worksheet Array formula:

>
> >

>
> >={SUBSTITUTE(CELL("address",INDEX(A1:E1,1,COLUMN( A1:E1))),"$","")}

>
> >

>
> >which gives me correct result i.e. "A1","B1","C1","D1","E1" across multiple cells.

>
> >

>
> >But question is: how do you i convert this into an equivalent single-line code in VBA? as far as i know, the worksheet CELL function does not have any equivalent in VBA.

>
>
>
> Are you now changing your specifications?
>
> Intially, you described a quote-delimited, comma separated string. But this formula of yours only returns one address per cell, and only in a range the same vector and size as your original range reference.
>
>
>
> I think your description of what you want is not as clear to me as it seems to be to you.
>
>
>
> Perhaps you could rethink how you want to express your requirements; and maybe some more information on the overall project might be useful in devising an efficient solution.



you are right. i need the one-liner code for something else. see attached file.

http://sdrv.ms/NsOdAV

when we use form Groupboxes & OptionButtons on a worksheet, to select those OptionButtons to hide/unhide them, we have to loop through all the optionbuttons in a worksheet i.e. Activesheet.optionbuttons.visible=true/false.

I have named my optionbuttons as "opt_" & cells(i,j).topleftcell.address. i don't want to hide/unhide all the optionbuttons in a worksheet, but the ones found in chosen Region rows. i have used checkboxes to tick/untick Region rows. Now, looping thru all the activesheet.optionbuttons and matching the correct optionbutton name with the topleftcell, takes time. Also, there is some major problem with hiding/unhiding the optionbuttons, ie. they donot hide with the rows but shift down or up.
  #8  
Old August 4th 12, 08:30 PM posted to microsoft.public.excel.worksheet.functions
Ron Rosenfeld[_2_]
external usenet poster
 
Posts: 1,045
Default split range address into individual cell addresses

On Sat, 4 Aug 2012 10:50:26 -0700 (PDT), noname > wrote:

>you are right. i need the one-liner code for something else. see attached file.
>
>http://sdrv.ms/NsOdAV
>
>when we use form Groupboxes & OptionButtons on a worksheet, to select those OptionButtons to hide/unhide them, we have to loop through all the optionbuttons in a worksheet i.e. Activesheet.optionbuttons.visible=true/false.
>
>I have named my optionbuttons as "opt_" & cells(i,j).topleftcell.address. i don't want to hide/unhide all the optionbuttons in a worksheet, but the ones found in chosen Region rows. i have used checkboxes to tick/untick Region rows. Now, looping thru all the activesheet.optionbuttons and matching the correct optionbutton name with the topleftcell, takes time. Also, there is some major problem with hiding/unhiding the optionbuttons, ie. they donot hide with the rows but shift down or up.


Complicated code.

But I still don't see a way of doing it with a one-liner in VBA.
However, the fact that you have control over the input simplifies the code considerably, but I still think you'll have to loop through the address range in order to get them out. Especially since the individual cell addresses are not a property of the range object. You could put the addresses into an array, and then use them to find your controls, I suppose. You'll need someone more knowledgeable than I to help further, though.
  #9  
Old August 4th 12, 08:41 PM posted to microsoft.public.excel.worksheet.functions
noname
external usenet poster
 
Posts: 97
Default split range address into individual cell addresses

On Sunday, August 5, 2012 1:00:47 AM UTC+5:30, Ron Rosenfeld wrote:
> On Sat, 4 Aug 2012 10:50:26 -0700 (PDT), noname > wrote:
>
>
>
> >you are right. i need the one-liner code for something else. see attached file.

>
> >

>
> >http://sdrv.ms/NsOdAV

>
> >

>
> >when we use form Groupboxes & OptionButtons on a worksheet, to select those OptionButtons to hide/unhide them, we have to loop through all the optionbuttons in a worksheet i.e. Activesheet.optionbuttons.visible=true/false.

>
> >

>
> >I have named my optionbuttons as "opt_" & cells(i,j).topleftcell.address.. i don't want to hide/unhide all the optionbuttons in a worksheet, but the ones found in chosen Region rows. i have used checkboxes to tick/untick Region rows. Now, looping thru all the activesheet.optionbuttons and matching the correct optionbutton name with the topleftcell, takes time. Also, there is some major problem with hiding/unhiding the optionbuttons, ie. they donot hide with the rows but shift down or up.

>
>
>
> Complicated code.
>
>
>
> But I still don't see a way of doing it with a one-liner in VBA.
>
> However, the fact that you have control over the input simplifies the code considerably, but I still think you'll have to loop through the address range in order to get them out. Especially since the individual cell addresses are not a property of the range object. You could put the addresses into an array, and then use them to find your controls, I suppose. You'll need someone more knowledgeable than I to help further, though.


Yes, thats what i have in mind, ie. once i have the split addresses in a string e.g StrAddress, then something like,
with Activesheet
.optionbuttons(Array(StrAddress)).visible=false/true.
end with
  #10  
Old August 5th 12, 01:33 AM posted to microsoft.public.excel.worksheet.functions
Ron Rosenfeld[_2_]
external usenet poster
 
Posts: 1,045
Default split range address into individual cell addresses

On Sat, 4 Aug 2012 12:41:39 -0700 (PDT), noname > wrote:

>On Sunday, August 5, 2012 1:00:47 AM UTC+5:30, Ron Rosenfeld wrote:
>> On Sat, 4 Aug 2012 10:50:26 -0700 (PDT), noname > wrote:
>>
>>
>>
>> >you are right. i need the one-liner code for something else. see attached file.

>>
>> >

>>
>> >http://sdrv.ms/NsOdAV

>>
>> >

>>
>> >when we use form Groupboxes & OptionButtons on a worksheet, to select those OptionButtons to hide/unhide them, we have to loop through all the optionbuttons in a worksheet i.e. Activesheet.optionbuttons.visible=true/false.

>>
>> >

>>
>> >I have named my optionbuttons as "opt_" & cells(i,j).topleftcell.address. i don't want to hide/unhide all the optionbuttons in a worksheet, but the ones found in chosen Region rows. i have used checkboxes to tick/untick Region rows. Now, looping thru all the activesheet.optionbuttons and matching the correct optionbutton name with the topleftcell, takes time. Also, there is some major problem with hiding/unhiding the optionbuttons, ie. they donot hide with the rows but shift down or up.

>>
>>
>>
>> Complicated code.
>>
>>
>>
>> But I still don't see a way of doing it with a one-liner in VBA.
>>
>> However, the fact that you have control over the input simplifies the code considerably, but I still think you'll have to loop through the address range in order to get them out. Especially since the individual cell addresses are not a property of the range object. You could put the addresses into an array, and then use them to find your controls, I suppose. You'll need someone more knowledgeable than I to help further, though.

>
>Yes, thats what i have in mind, ie. once i have the split addresses in a string e.g StrAddress, then something like,
>with Activesheet
> .optionbuttons(Array(StrAddress)).visible=false/true.
>end with


It's easy to set up the array, especially if you can control your input type:

========================================
Option Explicit
Sub ClrRange()
Dim r As Range, c As Range
Dim s() As String 'or possibly as range depending on the required
'argument type for your option buttons property
Dim i As Long
Set r = Application.InputBox("Clear Range: ", Type:=8)
ReDim v(0 To r.Count - 1)
For Each c In r
s(i) = c.Address
i = i + 1
Next c

'With ActiveSheet
' .optionbuttons(s).visible=false/true.
'End With

End Sub
================================
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Compiling email addresses from individual cells SwimOff1 Excel Discussion (Misc queries) 2 December 30th 08 08:30 PM
Split individual cells? richzip Excel Discussion (Misc queries) 2 November 5th 08 10:55 PM
Split address in 1 cell into 4 TXDalessandros Excel Programming 7 September 11th 08 07:39 PM
Expand IP Address range from slash notation to individual IPs... Kristin Excel Discussion (Misc queries) 0 July 9th 07 08:24 PM
Hyperlink for a group of addresses without individual entries? Peter R Excel Discussion (Misc queries) 1 February 10th 05 01:10 AM


All times are GMT +1. The time now is 02:48 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright 2004-2017 ExcelBanter.
The comments are property of their posters.