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.

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/

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.

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."