Problems with Random sorting macro

[LuukRost]

Hi,

I work in a school and i have to make an excelsheet that assigns the names from the students into cells on a new tab to randomly assign them to seats when taking tests or exams.

So i have one tab called "students", in this tab there is a row from A3 to A36 for the first class of students.
In the second tab called "sorting" i have a mock setup of the class layout so 4 tables wide and 7 tables long with the range B3 to E9.

What i want to do is make a macro button that takes the names of the students in tab "students" and randomly sorts them into the class layout in the tab "sorting".

I have gone so far as to make the macro working but it removes the references to the student cells in the "students" tab.

I am pretty ok with some light coding but this is a bit challenging, hence the post.

Can anyone help me with this?

Thanks in advance!

This is the code:

Sub Rechthoek1_Klikken()
Dim v As Variant, t As Variant, x As Long
Dim RwCell1 As Long, RwCell2 As Long, ColCell1 As Long, ColCell2 As Long
Dim Rw As Long, Col As Long, Iterations As Long
v = Selection.Value
Rw = Selection.Rows.Count
Col = Selection.Columns.Count
Iterations = Rw * Col
For x = Iterations To 1 Step -1


RwCell1 = Int(1 + Rw * Rnd)
RwCell2 = Int(1 + Rw * Rnd)
ColCell1 = Int(1 + Col * Rnd)
ColCell2 = Int(1 + Col * Rnd)
If Selection.Rows.Count = 1 Then
If ColCell1 <> ColCell2 Then
t = v(RwCell1, ColCell1)
v(RwCell1, ColCell1) = v(RwCell2, ColCell2)
v(RwCell2, ColCell2) = t
End If
ElseIf Selection.Columns.Count = 1 Then
If RwCell1 <> RwCell2 Then
t = v(RwCell1, ColCell1)
v(RwCell1, ColCell1) = v(RwCell2, ColCell2)
v(RwCell2, ColCell2) = t
End If
Else
If RwCell1 <> RwCell2 And ColCell1 <> ColCell2 Then
t = v(RwCell1, ColCell1)
v(RwCell1, ColCell1) = v(RwCell2, ColCell2)
v(RwCell2, ColCell2) = t
End If
End If
Next x
Selection.Value = v
End Sub

 

[gallen]

This is perfect! i had it setup differently in my head but this is much simpler and more clean!

Thank you so much!

You're very welcome. Sometimes we just need another perspective