Random

[shapper]

Hello,

I have an array as follows:

Dim data(1 To 6, 1 To 6) As String

Then I give values to my array:

data(1,1) = "Value 1"
data(1,2) = "Value 2"
....
data(6,6) = "Value 36"

How can I, after all values had been assigned to data, randomly
redistribute them?

Thanks,
Miguel

 

[merjet]

Pass your array to the Sub below, e.g. Shuffle myArray

Hth,
Merjet

Sub Shuffle(strX() As String)
Dim iCt As Integer
Sheets.Add after:=Sheets(Sheets.Count)
For iCt = 1 To 36
Cells(iCt, 1) = strX(1 + Int((iCt - 1) / 6), 1 + (iCt - 1) Mod
6)
Cells(iCt, 2) = Rnd()
Next iCt
Range("A1:B36").Sort Key1:=Range("B1"), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
For iCt = 1 To 36
strX(1 + Int((iCt - 1) / 6), 1 + (iCt - 1) Mod 6) = Cells(iCt,
1)
Next iCt
Application.DisplayAlerts = False
Sheets(Sheets.Count).Delete
Application.DisplayAlerts = True
End Sub

 

[Bernd P]

Hello Miguel,

Sub t1()
Dim data(1 To 6, 1 To 6) As String
Dim vrndarray
Dim i As Long, j As Long
vrndarray = VBUniqRandInt(36, 36)
For i = 1 To 6
For j = 1 To 6
data(i, j) = "Value " & vrndarray(i * 6 + j - 6)
Next j
Next i
End Sub

You can find my UDF vbuniqrandint here:
http://www.sulprobil.com/html/uniqrandint.html

Regards,
Bernd