Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Shuffling a Data List
Is anyone aware of a simple VBA routine to shuffle the contents of a list in
random fashion. Don |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Shuffling a Data List
Random Selection
http://www.tushar-mehta.com/excel/ne...ion/index.html -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , don.lloyd2 @virgin.net says... Is anyone aware of a simple VBA routine to shuffle the contents of a list in random fashion. Don |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Shuffling a Data List
here's another: (assuming the list is in a column - as written, starting in
A1) Sub Shuffle() ' ' Algorithm from: ' The Art of Computer Programming: _ ' SemiNumerical Algorithms Vol 2, 2nd Ed. ' Donald Knuth ' p. 139 [Algorithm P] ' ' Dim list As Variant Dim rng As Range Dim t As Long, j As Long, k As Long t = 100 Set rng = Range(Cells(1, 1), Cells(1, 1).End(xlDown)) list = rng.Value t = UBound(list, 1) j = t Randomize For i = 1 To t k = Rnd() * j + 1 lngTemp = list(j, 1) list(j, 1) = list(k, 1) list(k, 1) = lngTemp j = j - 1 Next rng.Value = list End Sub -- Regards, Tom Ogilvy "Don Lloyd" wrote in message ... Is anyone aware of a simple VBA routine to shuffle the contents of a list in random fashion. Don |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Shuffling a Data List
Than you Tom,
Just the job. I like the use of an array for doing the spadework. Don "Tom Ogilvy" wrote in message ... here's another: (assuming the list is in a column - as written, starting in A1) Sub Shuffle() ' ' Algorithm from: ' The Art of Computer Programming: _ ' SemiNumerical Algorithms Vol 2, 2nd Ed. ' Donald Knuth ' p. 139 [Algorithm P] ' ' Dim list As Variant Dim rng As Range Dim t As Long, j As Long, k As Long t = 100 Set rng = Range(Cells(1, 1), Cells(1, 1).End(xlDown)) list = rng.Value t = UBound(list, 1) j = t Randomize For i = 1 To t k = Rnd() * j + 1 lngTemp = list(j, 1) list(j, 1) = list(k, 1) list(k, 1) = lngTemp j = j - 1 Next rng.Value = list End Sub -- Regards, Tom Ogilvy "Don Lloyd" wrote in message ... Is anyone aware of a simple VBA routine to shuffle the contents of a list in random fashion. Don |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I change a date to UK format without shuffling the actual | Excel Discussion (Misc queries) | |||
Shuffling | Excel Discussion (Misc queries) | |||
data validation list: how do i 'force' a user to enter data from the list? | Excel Discussion (Misc queries) | |||
data validation list: how do i 'force' a user to enter data from the list? | Excel Discussion (Misc queries) | |||
Shuffling Data To Make Lists Match | Excel Worksheet Functions |