I have a 1D array with 40 elements. Element 1 = 1, 2 = 2 etc. I wan
to scramble the numbers within the array. So I want all 40 number
still but scrambled. What is the easiest way to do this?

Dim RandomColumnArray (40) As Variant

'Load Array with numbers from 1 to 40
For X = 1 To ColumnCount
RandomColumnArray(X) = X
Next X

'Scramble Array
???????

--
Message posted from http://www.ExcelForum.com

ExcelMonkey,

Assuming you're just using values from 1 to 40....
This doesn't scramble a prefilled array. It's just an array filling routine.

If you want a true scrambler, then let me know...


Sub testit()
Const cQuantity = 40
Dim i As Long, j As Long, r As Long
Dim arr(1 To cQuantity) As Long

For i = 1 To cQuantity
r = Int((cQuantity * Rnd) + 1)
Do
For j = 1 To i - 1
If arr(j) = r Then Exit For
Next
If j < i Then r = r + 1
If r cQuantity Then r = 1
Loop Until j = i
arr(i) = r
Next

For i = 1 To cQuantity
Cells(i, 1).Value = arr(i)
Next

End Sub


Rob


"ExcelMonkey " wrote in message
...
I have a 1D array with 40 elements. Element 1 = 1, 2 = 2 etc. I want
to scramble the numbers within the array. So I want all 40 numbers
still but scrambled. What is the easiest way to do this?

Dim RandomColumnArray (40) As Variant

'Load Array with numbers from 1 to 40
For X = 1 To ColumnCount
RandomColumnArray(X) = X
Next X

'Scramble Array
????????


---
Message posted from http://www.ExcelForum.com/

Here is a generalized function for shuffling a 1D long array:

Public Function ShuffleArray(varr)

'
' Algorithm from:
' The Art of Computer Programming: _
' SemiNumerical Algorithms Vol 2, 2nd Ed.
' Donald Knuth
' p. 139
'
'
Dim List() As Long
Dim t As Long
Dim i As Long
Dim j As Long
Dim k As Long
Dim lngTemp As Long

t = UBound(varr, 1) - LBound(varr, 1) + 1
ReDim List(1 To t)
For i = 1 To t
List(i) = varr(i)
Next
j = t
Randomize
For i = 1 To t
k = Rnd() * j + 1
lngTemp = List(j)
List(j) = List(k)
List(k) = lngTemp
j = j - 1
Next
ShuffleArray = List
End Function

This shows how to call it:

Sub Main()
Dim varr()
Dim varr1
ReDim varr(1 To 40)
For i = 1 To 40
varr(i) = i
Next
varr1 = ShuffleArray(varr)
Range("A1:A40").Value = Application.Transpose(varr1)
End Sub


--
Regards,
Tom Ogilvy


ExcelMonkey wrote in message
...
I have a 1D array with 40 elements. Element 1 = 1, 2 = 2 etc. I want
to scramble the numbers within the array. So I want all 40 numbers
still but scrambled. What is the easiest way to do this?

Dim RandomColumnArray (40) As Variant

'Load Array with numbers from 1 to 40
For X = 1 To ColumnCount
RandomColumnArray(X) = X
Next X

'Scramble Array
????????


---
Message posted from http://www.ExcelForum.com/

Thank-you Tom. Much appreciated.

RK


---
Message posted from http://www.ExcelForum.com/