Natasha has attended:
Excel Advanced course
Excel VBA Introduction course
Excel Advanced - Formulas & Functions course
Excel VBA Intermediate course
Excel Dashboards for Business Intelligence course
Excel Forecasting and Data Analysis course
Excel VBA Advanced course
Random group allocation
I am trying to write a macro that randomizes a list of students and then puts the students into 52 groups of 6.
I don't know how to break this down. Do I randomise the list first? Do I need to use a For Next Loop? I would be grateful for any help or pointers.
RE: Random group allocation
Hello Natasha,
Thank you for your question. The code below worked for me. Please make sure your first student is in cell B1. The macro assigns a random number to each student, then sorts ascending on the numbers. Afterwards it inserts a blank row after each 6th student. I assume you have 312 students, so simply change the A18 references in my code to A312.
Sub Macro1()
Dim LastRow As Long
Dim i As Long
Range("a1") = "=INT(RAND()*100)"
Selection.AutoFill Destination:=Range("A1:A18")
Range("A1:A18").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Range("A1").Select
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A1"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("A1:B18")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = LastRow - LastRow Mod 6 To 6 Step -6
.Rows(i + 1).Insert
Next i
End With
End Sub
I hope this helps.
Kind regards
Marius Barnard
STL
RE: Random group allocation
Hi Marius
Thank you for this. This does what I need it to do and works in principal except that the random number does not get sorted even though I have the students in column B. The code appears to be sorting on "A" so I don't know why it doesn't work.
Also, is it possible to remove the blank row after each set? I have tried removing this line but have broken the macro doing this.
Kind regards
Natasha
RE: Random group allocation
Hi Natasha,
In your code, try changing the B18 reference in the line:
.SetRange Range("A1:B18")
to B312 or whichever your last row is.
To stop the code from inserting blank rows, remove the code below from your macro:
With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = LastRow - LastRow Mod 6 To 6 Step -6
.Rows(i + 1).Insert
Next i
End With
Good luck!
RE: Random group allocation
Hi Marius
The last student is in cell B328. Is there anything wrong with the code below that you can see? - as this still does not run I'm afraid.
Sub Macro1()
Dim LastRow As Long
Dim i As Long
Range("a1") = "=INT(RAND()*100)"
Selection.AutoFill Destination:=Range("A1:B328")
Range("A1:B328").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Range("A1").Select
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A1"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("A1:B328")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = LastRow - LastRow Mod 6 To 6 Step -6
.Rows(i + 1).Insert
Next i
End With
End Sub
RE: Random group allocation
Hi Natasha,
I can't see any incorrect code. I'm afraid this is as far as my VBA knowledge extends. My colleague who teaches VBA to advanced level will be back from leave on Monday. I will ask him to have a look at it.
Apologies for the delay.
Kind regards
Marius
RE: Random group allocation
Thank you anyway Marius.
I would be grateful if you could ask your colleague about the code when he is back.
Please could you ask him to check it without the line break coding i.e. just the code below?
Range("a1") = "=INT(RAND()*100)"
Selection.AutoFill Destination:=Range("A1:B328")
Range("A1:B328").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Range("A1").Select
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A1"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("A1:B328")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
RE: Random group allocation
Hi Natasha,
I think he will probably give you much better code than I could. I'll show him my code anyway.
Best regards
Marius
