Thanks. My data is mostly larger than 100 cells. how can I increase the varGroupSize to 200?
Thanks, Mike On Tue, May 5, 2009 at 2:38 PM, Fabio Lemos <flnle...@gmail.com> wrote: > Ok. > That's possible too... > > > There is a named range (a value in this case) that put a limit on each > block size.... if you set it to a very large number the calculation will > last longer.... (varGroupSize that is set to 100). > > If you need to increase it you can. > > Regards! > > 2009/5/5 Michael Fowler <blank.appa...@gmail.com> > > Thanks Fabio. >> >> But, this doesn't take into account only the range UNTIL the next blank. I >> have made a sheet from your that shows three groups of data. The cell >> calling the random data is taking into account ALL of the data. Can this be >> limited to only each group and only to the next blank cell (after the last >> data cell of ea. range)? >> >> Thanks for your help. >> >> On Mon, May 4, 2009 at 5:58 PM, Fabio Lemos <flnle...@gmail.com> wrote: >> >>> The formula was wrong, see this new file. >>> >>> 2009/5/4 Fabio Lemos <flnle...@gmail.com> >>> >>> you could create a variable range name and use it in your formula... >>>> >>>> 2009/5/4 Satti Charvak <sat...@gmail.com> >>>> >>>> Prepare en excel sheet and send it back...your usage of the formula is >>>>> not clear. >>>>> >>>>> On Mon, May 4, 2009 at 7:05 AM, Blank <blank.appa...@gmail.com> wrote: >>>>> >>>>>> >>>>>> I am trying to get a random selection of a cell containing text, >>>>>> basically: >>>>>> >>>>>> IF there is an empty cell in a column, get a random cell of data from >>>>>> the range below it UNTIL the next empty cell. >>>>>> >>>>>> I have achieved this manually with the following formula: >>>>>> >>>>>> =INDEX(A2:A171,RANDBETWEEN(1,169)) >>>>>> >>>>>> The issues that I have with this are: >>>>>> >>>>>> 1. I have to manually select each range (tedious when I have a full >>>>>> worksheet(s) of like data) >>>>>> 2. I have to calculate the number of rows (ie. the 169 in the >>>>>> example) so I don't grab from next groups range >>>>>> >>>>>> Does anyone know a better way to do this? >>>>>> >>>>>> Thanks for your assistance, >>>>>> >>>>>> Blank >>>>>> >>>>>> >>>>>> -- >>>>>> >>>>>> Thanks and regards, >>>>>> Satti Charvak >>>>>> ____________________ >>>>>> >>>>>> >>>>>> >>>> >>>> >>>> -- >>>> Fabio L Lemos >>>> >>>> email: flnle...@gmail.com >>>> >>>> >>> >>> >>> -- >>> Fabio L Lemos >>> >>> email: flnle...@gmail.com >>> >>> >>> >>> >> >> >> > > > -- > Fabio L Lemos > > email: flnle...@gmail.com > > > > > --~--~---------~--~----~------------~-------~--~----~ ------------------------------------------------------------------------------------- Some important links for excel users: 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 2. Excel tutorials at http://www.excel-macros.blogspot.com 3. Learn VBA Macros at http://www.vbamacros.blogspot.com 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com or Ashish Jain @ 26may.1...@gmail.com ------------------------------------------------------------------------------------- -~----------~----~----~----~------~----~------~--~---
