random permutation of an array

[Shatin]

Can anyone help me with a script that would shuffle the elements of an array
of integers randomly. For example:

MyArray before shuffling: 1, 2, 3, 4, 5
MyArray after shuffling: 3, 5, 4, 2, 1

I want the reordering to be really random.

TIA

 

[Bernard Liengme]

With the array in A1:A10;
Select B1:B10 (or any suitable vertical range)
Use =RandomA(A1:A10); commit with CTRL+SHIFT+ENTER
-----------------------------
Option Base 1
Function randomA(myrange)
Dim myarray(100, 2)
rcount = myrange.Count
For j = 1 To rcount
myarray(j, 1) = myrange(j) 'transfer data from cell to array
myarray(j, 2) = Rnd 'give second vector a random value
Next j
For j = 1 To rcount - 1 'bubble sort by random number
For k = j To rcount
If myarray(k, 2) > myarray(j, 2) Then
tempa = myarray(j, 1)
tempb = myarray(j, 2)
myarray(j, 1) = myarray(k, 1)
myarray(j, 2) = myarray(k, 2)
myarray(k, 1) = tempa
myarray(k, 2) = tempb
End If
Next k
Next j
randomA = myarray
End Function

 

[Mike H]

You could try this

Sub Shuffle()
Dim myarray(6)
For x = 1 To 6
myarray(x) = x
Debug.Print myarray(x)
ActiveSheet.Cells(x, 1).Value = myarray(x)
ActiveSheet.Cells(x, 1).Offset(, 1).Value = Rnd
Next
ActiveSheet.Range("A1:B6").Sort Key1:=Range("B1"), Order1:=xlAscending
For x = 1 To 6
myarray(x) = ActiveSheet.Cells(x, 1).Value
Next
ActiveSheet.Range("A1:B6").ClearContents
End Sub

Mike

 

[Gary Brown]

My 2 cents:

'/===========================/
Sub Macro1()
Dim aryData 'declare an array
Dim i As Integer, iCount As Integer
Dim strOldSheetName As String
Dim strNewSheetName As String

iCount = 5 '# of randoms
ReDim aryData(iCount - 1) 're-dimension the array

'get name of current worksheet
strOldSheetName = ActiveSheet.Name

Sheets.Add 'create a 'working' worksheet

'get name of new worksheet
strNewSheetName = ActiveSheet.Name

'fill data w/ values
For i = 1 To iCount
ActiveCell.Offset(i - 1, 0).Value = i
Next i

'put random generator next to values
For i = 1 To iCount
ActiveCell.Offset(i - 1, 1).Value = "=RAND()"
Next i

'sort randomly based on =RAND formula in Col B
Range("A1:" & ActiveCell.Offset(iCount - 1, 1).Address).Sort _
Key1:=Range("B1"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

'put random #s into an array
For i = 0 To iCount - 1
aryData(i) = ActiveCell.Offset(i).Value
Next i

'delete the 'working' worksheet
Sheets(strNewSheetName).Select
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True

'go to the original sheet
Sheets(strOldSheetName).Select

For i = 0 To iCount - 1
Debug.Print aryData(i)
Next i

Application.DisplayAlerts = True

End Sub
'/===========================/

 

[Rick Rothstein \(MVP - VB\)]

The following is a generalized shuffling routine that I have posted in the
past over in the compiled VB newsgroups, but it works fine in the VBA world
of Excel as well. Give it an array of elements and it will put them in
random order and return the randomized elements back in the original array
that was passed to it. It only visits *each* array element *once* so it is
quick. The code takes care of running the Randomize statement one time only
(which is all that is necessary).

Sub RandomizeArray(ArrayIn As Variant)
Dim X As Long
Dim RandomIndex As Long
Dim TempElement As Variant
Static RanBefore As Boolean
If Not RanBefore Then
RanBefore = True
Randomize
End If
If VarType(ArrayIn) >= vbArray Then
For X = UBound(ArrayIn) To LBound(ArrayIn) Step -1
RandomIndex = Int((X - LBound(ArrayIn) + 1) * _
Rnd + LBound(ArrayIn))
TempElement = ArrayIn(RandomIndex)
ArrayIn(RandomIndex) = ArrayIn(X)
ArrayIn(X) = TempElement
Next
Else
'The passed argument was not an array
'Put error handler here, such as . . .
Beep
End If
End Sub

After passing your array into the RandomizeArray subroutine, its elements
will be randomly reordered. The passed array may be of any normal type --
integer, string, single, etc. The neat thing is, if you pass an already
randomized array to this routine, those randomly ordered elements will be
randomize -- sort of like shuffling an already shuffled deck of cards.

Rick

 

[Shatin]

Thanks to all for your help!