Randomly Re-arrange Entries in an array.

[james_tasker]

Hi,

I have a 203x203 array of non-negative numbers which I want to re-arrange at
random in another sheet. I've tried creating and using an array of random
numbers in another sheet to reorder the data but it isn't quite working out.

Does anybody know of a formula I can use to accomplish this?

Many thanks

 

[Gary''s Student]

1. We copy the table to a single column on tab sh2
2. We shuffle the single column of tab sh2
3. We copy the single column on sh2 to a table on sh3

Sub shuffle()
Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet
Dim i As Integer, j As Integer, k As Long
Set sh1 = Sheets("sh1")
Set sh2 = Sheets("sh2")
Set sh3 = Sheets("sh3")
sh1.Activate
Application.ScreenUpdating = False
Application.Calculation = xlManual

k = 1
For i = 1 To 203
For j = 1 To 203
sh2.Cells(k, 1).Value = Cells(i, j).Value
k = k + 1
Next
Next

sh2.Activate
Range("B1:B41209").Formula = "=rand()"
Columns("A:B").Select
Selection.Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlNo

k = 1
For i = 1 To 203
For j = 1 To 203
sh3.Cells(i, j).Value = Cells(k, 1).Value
k = k + 1
Next
Next
Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
End Sub