Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Randomize the order of the contents of an array
I have a range of cells 5 * 5 (A1:E5) which have string contents in them at
present. I want to run a macro that randomly sorts the existing contents into different cells - it has to be the order of the existing content that is randomly re-ordered not the content itself. Think of it similar to a bingo card with the contents being the text strings one, two,...., twenty five - where I want to produce random outputs for printing. How would I do this in VBA. Cheers. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Randomize the order of the contents of an array
On Sat, 12 Jul 2003 07:53:45 +0100, "Lee Wold"
wrote in microsoft.public.excel.programming: I have a range of cells 5 * 5 (A1:E5) which have string contents in them at present. I want to run a macro that randomly sorts the existing contents into different cells - it has to be the order of the existing content that is randomly re-ordered not the content itself. Think of it similar to a bingo card with the contents being the text strings one, two,...., twenty five - where I want to produce random outputs for printing. How would I do this in VBA. Cheers. "Excel 2002 Power Programming with VBA", John Walkenbach, p.342: "Randomizing a range" "The RANGERANDOMIZE function ... accepts a range argument and returns an array that consists of the input range - in random order." -- Michael Bednarek, IT Manager, Tactical Global Management Waterfront Pl, Brisbane 4000, Australia. "POST NO BILLS" http://mcmbednarek.tripod.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Randomize the order of the contents of an array
one way:
Public Sub RandomizeRange() Dim temp As Variant Dim arr As Variant Dim rng As Range Dim i As Integer, i1 As Integer Dim j As Integer, j1 As Integer Set rng = Range("A1:E5") arr = rng.Value For i = UBound(arr, 1) To 1& Step -1& For j = UBound(arr, 2) To 1& Step -1& i1 = Int(Rnd() * i) + 1& j1 = Int(Rnd() * j) + 1& temp = arr(i, j) arr(i, j) = arr(i1, j1) arr(i1, j1) = temp Next j Next i rng.Value = arr End Sub In article , "Lee Wold" wrote: I have a range of cells 5 * 5 (A1:E5) which have string contents in them at present. I want to run a macro that randomly sorts the existing contents into different cells - it has to be the order of the existing content that is randomly re-ordered not the content itself. Think of it similar to a bingo card with the contents being the text strings one, two,...., twenty five - where I want to produce random outputs for printing. How would I do this in VBA. Cheers. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Randomize the order of the contents of an array
While John's excellent routine will work very well, the OP would
have to have a copy of his book in order to use your reply. I realize that everyone *should* have a copy, of course... In article , Michael Bednarek wrote: "Excel 2002 Power Programming with VBA", John Walkenbach, p.342: "Randomizing a range" "The RANGERANDOMIZE function ... accepts a range argument and returns an array that consists of the input range - in random order." |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
does an array contain contents of cell | New Users to Excel | |||
search an array in reverse (bottom to top) order | Excel Discussion (Misc queries) | |||
arrange contents of a table in Alphabetical order in excelsheet | Excel Worksheet Functions | |||
Transpose and order array numbers | Excel Worksheet Functions | |||
How do I lookup a value in a array that is not in ascending order | Excel Worksheet Functions |