There's several ways to approach this. the first question is: How insistent are you on using VBA?
I have a list of 12 names that I need to sort "randomly". what I did was to put column next to the name with =rand() I then sort by this value. In fact, I created a button that "calculates" the sheet 10 times, then sorts it. this would be the simplest method. a VBA solution would be to do something like: put values in A1-A10 load values into an array. use Rnd to randomly select an array index and if it has a value, use it, and remove the value. continue until the 10 values have been used..: Sub srt() Dim I, nArray(11) Dim lbnd, ubnd Dim ttl, inx lbnd = 1 ubnd = 10 For I = 1 To 10 nArray(I) = Cells(I, 1) Next I ttl = 0 While ttl < 10 inx = Int((ubnd - lbnd + 1) * Rnd + lbnd) If (nArray(inx) <> "") Then ttl = ttl + 1 Cells(ttl, 2) = nArray(inx) nArray(inx) = "" End If Wend End Sub hope this helps. Paul ----- Original Message ---- > From: peace <kansaskan...@gmail.com> > To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com> > Sent: Tue, January 5, 2010 11:13:54 AM > Subject: $$Excel-Macros$$ random selection from excel > > I have a list of 10 items and I want to select randomly (and > repeatedly) from the list. > > Once I have selected an item, I want to be able to select randomly > from the other 9 items. After 10 tries i should have selected all > items once. > > For the 11th try I should be able to select randomly from all 10. > What would be the best way to do this? with Excel VBA. > > Thank you - one and all - for any suggestions. > -- > ---------------------------------------------------------------------------------- > Some important links for excel users: > 1. Follow us in TWITTER for tips tricks and links : > http://twitter.com/exceldailytip > 2. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at > http://www.excelitems.com > 3. Excel tutorials at http://www.excel-macros.blogspot.com > 4. Learn VBA Macros at http://www.quickvba.blogspot.com > 5. 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 > <><><><><><><><><><><><><><><><><><><><><><> > HELP US GROW !! > > We reach over 6,500 subscribers worldwide and receive many nice notes about > the > learning and support from the group.Let friends and co-workers know they can > subscribe to group at http://groups.google.com/group/excel-macros/subscribe
-- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us in TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. 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 <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 6,500 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
