Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old August 4th 12, 07:34 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2007
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?

  #2   Report Post  
Old August 4th 12, 12:37 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2010
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   Report Post  
Old August 4th 12, 02:36 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2007
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   Report Post  
Old August 4th 12, 04:28 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2007
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   Report Post  
Old August 4th 12, 05:41 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2010
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   Report Post  
Old August 4th 12, 06:06 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2010
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   Report Post  
Old August 4th 12, 06:50 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2007
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   Report Post  
Old August 4th 12, 08:30 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2010
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   Report Post  
Old August 4th 12, 08:41 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2007
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   Report Post  
Old August 5th 12, 01:33 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2010
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
================================


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


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 01:14 PM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017