View Single Post
  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
noname noname is offline
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.