Random

  • Thread starter Thread starter shapper
  • Start date Start date
S

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
 
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
 
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
 
Back
Top