Sub randomize_roster()
Dim randnum As Integer, staff As String, x As String, y As String, lastrow As Integer, areaval As Integer, dayval As Integer, ival As Integer, check As Integer, timeslooped As Integer, areacheck As Integer
Application.ScreenUpdating = False
Range("B3:F21").ClearContents
tryagain:
timeslooped = 0
dayval = 2
areaval = 22
lastrow = Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
Sheets("Sheet2").Activate
area16:
randnum = Int((lastrow - 2 + 1) * Rnd + 2)
staff = Worksheets("Sheet2").Cells(randnum, 1).Value
x = WorksheetFunction.Index(ActiveWorkbook.Worksheets("Sheet2").Range(Cells(1, dayval), ActiveWorkbook.Worksheets("Sheet2").Cells(lastrow, dayval)), randnum, 0)
y = WorksheetFunction.Index(ActiveWorkbook.Sheets("Sheet2").Range(Cells(1, areaval), Worksheets("Sheet2").Cells(lastrow, areaval)), randnum, 0)
If x = "X" Or y = "X" Then
GoTo area16
Else
Worksheets("Sheet1").Cells(20, dayval).Value = staff
dayval = dayval + 1
If dayval = 7 Then
GoTo endarea16
Else: GoTo area16
End If
End If
endarea16:
dayval = 2
areaval = 23
area17:
randnum = Int((lastrow - 2 + 1) * Rnd + 2)
staff = Worksheets("Sheet2").Cells(randnum, 1).Value
x = WorksheetFunction.Index(ActiveWorkbook.Worksheets("Sheet2").Range(Cells(1, dayval), ActiveWorkbook.Worksheets("Sheet2").Cells(lastrow, dayval)), randnum, 0)
y = WorksheetFunction.Index(ActiveWorkbook.Sheets("Sheet2").Range(Cells(1, areaval), Worksheets("Sheet2").Cells(lastrow, areaval)), randnum, 0)
If x = "X" Or y = "X" Then
GoTo area17
Else
check = 20
If Worksheets("Sheet1").Cells(check, dayval).Value = staff Then
GoTo area17
Else
End If
Worksheets("Sheet1").Cells(21, dayval).Value = staff
dayval = dayval + 1
If dayval = 7 Then
GoTo endarea17
Else: GoTo area17
End If
End If
endarea17:
dayval = 2
areaval = 7
area1:
randnum = Int((lastrow - 2 + 1) * Rnd + 2)
staff = Worksheets("Sheet2").Cells(randnum, 1).Value
Sheets("Sheet2").Activate
x = WorksheetFunction.Index(ActiveWorkbook.Worksheets("Sheet2").Range(Cells(1, dayval), ActiveWorkbook.Worksheets("Sheet2").Cells(lastrow, dayval)), randnum, 0)
y = WorksheetFunction.Index(ActiveWorkbook.Sheets("Sheet2").Range(Cells(1, areaval), Worksheets("Sheet2").Cells(lastrow, areaval)), randnum, 0)
If x = "X" Or y = "X" Then
GoTo area1
Else
For check = 20 To 21
If Worksheets("Sheet1").Cells(check, dayval).Value = staff Then
timeslooped = timeslooped + 1
GoTo area1
Else
End If
Next check
Select Case dayval
Case 1 To 2
Case 3
If Worksheets("Sheet1").Cells(3, 2).Value = staff Then
timeslooped = timeslooped + 1
GoTo area1
End If
Case 4
For areacheck = 2 To 3
If Worksheets("Sheet1").Cells(3, areacheck).Value = staff Then
timeslooped = timeslooped + 1
GoTo area1
End If
Next areacheck
Case 5
For areacheck = 2 To 4
If Worksheets("Sheet1").Cells(3, areacheck).Value = staff Then
timeslooped = timeslooped + 1
GoTo area1
End If
Next areacheck
Case 6
For areacheck = 2 To 5
If Worksheets("Sheet1").Cells(3, areacheck).Value = staff Then
timeslooped = timeslooped + 1
GoTo area1
End If
Next areacheck
End Select
Worksheets("Sheet1").Cells(3, dayval).Value = staff
dayval = dayval + 1
If dayval = 7 Then
GoTo endarea1
Else: GoTo area1
End If
End If
endarea1:
dayval = 2
areaval = 8
area2:
randnum = Int((lastrow - 2 + 1) * Rnd + 2)
staff = Worksheets("Sheet2").Cells(randnum, 1).Value
x = WorksheetFunction.Index(ActiveWorkbook.Worksheets("Sheet2").Range(Cells(1, dayval), ActiveWorkbook.Worksheets("Sheet2").Cells(lastrow, dayval)), randnum, 0)
y = WorksheetFunction.Index(ActiveWorkbook.Sheets("Sheet2").Range(Cells(1, areaval), Worksheets("Sheet2").Cells(lastrow, areaval)), randnum, 0)
If x = "X" Or y = "X" Then
GoTo area2
Else
check = 3
If Worksheets("Sheet1").Cells(check, dayval).Value = staff Then
timeslooped = timeslooped + 1
GoTo area2
Else
End If
For check = 20 To 21
If Worksheets("Sheet1").Cells(check, dayval).Value = staff Then
timeslooped = timeslooped + 1
GoTo area2
Else
End If
Next check
Select Case dayval
Case 1 To 2
Case 3
If Worksheets("Sheet1").Cells(4, 2).Value = staff Then
timeslooped = timeslooped + 1
GoTo area2
End If
Case 4
For areacheck = 2 To 3
If Worksheets("Sheet1").Cells(4, areacheck).Value = staff Then
timeslooped = timeslooped + 1
GoTo area2
End If
Next areacheck
Case 5
For areacheck = 2 To 4
If Worksheets("Sheet1").Cells(4, areacheck).Value = staff Then
timeslooped = timeslooped + 1
GoTo area2
End If
Next areacheck
Case 6
For areacheck = 2 To 5
If Worksheets("Sheet1").Cells(4, areacheck).Value = staff Then
timeslooped = timeslooped + 1
GoTo area2
End If
Next areacheck
End Select
Worksheets("Sheet1").Cells(4, dayval).Value = staff
dayval = dayval + 1
If dayval = 7 Then
GoTo endarea2
Else: GoTo area2
End If
End If
endarea2:
dayval = 2
areaval = 9
area3:
randnum = Int((lastrow - 2 + 1) * Rnd + 2)
staff = Worksheets("Sheet2").Cells(randnum, 1).Value
x = WorksheetFunction.Index(ActiveWorkbook.Worksheets("Sheet2").Range(Cells(1, dayval), ActiveWorkbook.Worksheets("Sheet2").Cells(lastrow, dayval)), randnum, 0)
y = WorksheetFunction.Index(ActiveWorkbook.Sheets("Sheet2").Range(Cells(1, areaval), Worksheets("Sheet2").Cells(lastrow, areaval)), randnum, 0)
If x = "X" Or y = "X" Then
GoTo area3
Else
For check = 3 To 4
If Worksheets("Sheet1").Cells(check, dayval).Value = staff Then
timeslooped = timeslooped + 1
GoTo area3
Else
End If
Next check
For check = 20 To 21
If Worksheets("Sheet1").Cells(check, dayval).Value = staff Then
timeslooped = timeslooped + 1
GoTo area3
Else
End If
Next check
Worksheets("Sheet1").Cells(6, dayval).Value = staff
dayval = dayval + 1
If dayval = 7 Then
GoTo endarea3
Else: GoTo area3
End If
End If
endarea3:
dayval = 4
areaval = 10
area4:
If timeslooped > 12500 Then GoTo tryagain Else
randnum = Int((lastrow - 2 + 1) * Rnd + 2)
staff = Worksheets("Sheet2").Cells(randnum, 1).Value
x = WorksheetFunction.Index(ActiveWorkbook.Worksheets("Sheet2").Range(Cells(1, dayval), ActiveWorkbook.Worksheets("Sheet2").Cells(lastrow, dayval)), randnum, 0)
y = WorksheetFunction.Index(ActiveWorkbook.Sheets("Sheet2").Range(Cells(1, areaval), Worksheets("Sheet2").Cells(lastrow, areaval)), randnum, 0)
If x = "X" Or y = "X" Then
GoTo area4
Else
For check = 3 To 6
If Worksheets("Sheet1").Cells(check, dayval).Value = staff Then
timeslooped = timeslooped + 1
GoTo area4
Else
End If
Next check
For check = 20 To 21
If Worksheets("Sheet1").Cells(check, dayval).Value = staff Then
timeslooped = timeslooped + 1
GoTo area4
Else
End If
Next check
Worksheets("Sheet1").Cells(7, dayval).Value = staff
dayval = dayval + 1
If dayval = 7 Then
GoTo endarea4
Else: GoTo area4
End If
End If
endarea4:
dayval = 2
areaval = 11
area5:
If timeslooped > 12500 Then GoTo tryagain Else
randnum = Int((lastrow - 2 + 1) * Rnd + 2)
staff = Worksheets("Sheet2").Cells(randnum, 1).Value
x = WorksheetFunction.Index(ActiveWorkbook.Worksheets("Sheet2").Range(Cells(1, dayval), ActiveWorkbook.Worksheets("Sheet2").Cells(lastrow, dayval)), randnum, 0)
y = WorksheetFunction.Index(ActiveWorkbook.Sheets("Sheet2").Range(Cells(1, areaval), Worksheets("Sheet2").Cells(lastrow, areaval)), randnum, 0)
If x = "X" Or y = "X" Then
GoTo area5
Else
For check = 3 To 7
If Worksheets("Sheet1").Cells(check, dayval).Value = staff Then
timeslooped = timeslooped + 1
GoTo area5
Else
End If
Next check
For check = 20 To 21
If Worksheets("Sheet1").Cells(check, dayval).Value = staff Then
timeslooped = timeslooped + 1
GoTo area5
Else
End If
Next check
Worksheets("Sheet1").Cells(8, dayval).Value = staff
dayval = dayval + 1
If dayval = 7 Then
GoTo endarea5
Else: GoTo area5
End If
End If
endarea5:
dayval = 2
areaval = 12
area6:
If timeslooped > 12500 Then GoTo tryagain Else
randnum = Int((lastrow - 2 + 1) * Rnd + 2)
staff = Worksheets("Sheet2").Cells(randnum, 1).Value
x = WorksheetFunction.Index(ActiveWorkbook.Worksheets("Sheet2").Range(Cells(1, dayval), ActiveWorkbook.Worksheets("Sheet2").Cells(lastrow, dayval)), randnum, 0)
y = WorksheetFunction.Index(ActiveWorkbook.Sheets("Sheet2").Range(Cells(1, areaval), Worksheets("Sheet2").Cells(lastrow, areaval)), randnum, 0)
If x = "X" Or y = "X" Then
GoTo area6
Else
For check = 3 To 8
If Worksheets("Sheet1").Cells(check, dayval).Value = staff Then
timeslooped = timeslooped + 1
GoTo area6
Else
End If
Next check
For check = 20 To 21
If Worksheets("Sheet1").Cells(check, dayval).Value = staff Then
timeslooped = timeslooped + 1
GoTo area6
Else
End If
Next check
Worksheets("Sheet1").Cells(9, dayval).Value = staff
dayval = dayval + 1
If dayval = 7 Then
GoTo endarea6
Else: GoTo area6
End If
End If
endarea6:
dayval = 2
areaval = 13
area7:
If timeslooped > 12500 Then GoTo tryagain Else
randnum = Int((lastrow - 2 + 1) * Rnd + 2)
staff = Worksheets("Sheet2").Cells(randnum, 1).Value
x = WorksheetFunction.Index(ActiveWorkbook.Worksheets("Sheet2").Range(Cells(1, dayval), ActiveWorkbook.Worksheets("Sheet2").Cells(lastrow, dayval)), randnum, 0)
y = WorksheetFunction.Index(ActiveWorkbook.Sheets("Sheet2").Range(Cells(1, areaval), Worksheets("Sheet2").Cells(lastrow, areaval)), randnum, 0)
If x = "X" Or y = "X" Then
GoTo area7
Else
For check = 3 To 9
If Worksheets("Sheet1").Cells(check, dayval).Value = staff Then
timeslooped = timeslooped + 1
GoTo area7
Else
End If
Next check
For check = 20 To 21
If Worksheets("Sheet1").Cells(check, dayval).Value = staff Then
timeslooped = timeslooped + 1
GoTo area7
Else
End If
Next check
Worksheets("Sheet1").Cells(10, dayval).Value = staff
dayval = dayval + 1
If dayval = 7 Then
GoTo endarea7
Else: GoTo area7
End If
End If
endarea7:
dayval = 2
areaval = 14
area8:
If timeslooped > 12500 Then GoTo tryagain Else
randnum = Int((lastrow - 2 + 1) * Rnd + 2)
staff = Worksheets("Sheet2").Cells(randnum, 1).Value
x = WorksheetFunction.Index(ActiveWorkbook.Worksheets("Sheet2").Range(Cells(1, dayval), ActiveWorkbook.Worksheets("Sheet2").Cells(lastrow, dayval)), randnum, 0)
y = WorksheetFunction.Index(ActiveWorkbook.Sheets("Sheet2").Range(Cells(1, areaval), Worksheets("Sheet2").Cells(lastrow, areaval)), randnum, 0)
If x = "X" Or y = "X" Then
GoTo area8
Else
For check = 3 To 10
If Worksheets("Sheet1").Cells(check, dayval).Value = staff Then
timeslooped = timeslooped + 1
GoTo area8
Else
End If
Next check
For check = 20 To 21
If Worksheets("Sheet1").Cells(check, dayval).Value = staff Then
timeslooped = timeslooped + 1
GoTo area8
Else
End If
Next check
Worksheets("Sheet1").Cells(11, dayval).Value = staff
dayval = dayval + 1
If dayval = 7 Then
GoTo endarea8
Else: GoTo area8
End If
End If
endarea8:
dayval = 2
areaval = 15
area9:
If timeslooped > 12500 Then GoTo tryagain Else
randnum = Int((lastrow - 2 + 1) * Rnd + 2)
staff = Worksheets("Sheet2").Cells(randnum, 1).Value
x = WorksheetFunction.Index(ActiveWorkbook.Worksheets("Sheet2").Range(Cells(1, dayval), ActiveWorkbook.Worksheets("Sheet2").Cells(lastrow, dayval)), randnum, 0)
y = WorksheetFunction.Index(ActiveWorkbook.Sheets("Sheet2").Range(Cells(1, areaval), Worksheets("Sheet2").Cells(lastrow, areaval)), randnum, 0)
If x = "X" Or y = "X" Then
GoTo area9
Else
For check = 3 To 11
If Worksheets("Sheet1").Cells(check, dayval).Value = staff Then
timeslooped = timeslooped + 1
GoTo area9
Else
End If
Next check
For check = 20 To 21
If Worksheets("Sheet1").Cells(check, dayval).Value = staff Then
timeslooped = timeslooped + 1
GoTo area9
Else
End If
Next check
Worksheets("Sheet1").Cells(13, dayval).Value = staff
dayval = dayval + 1
If dayval = 7 Then
GoTo endarea9
Else: GoTo area9
End If
End If
endarea9:
dayval = 2
areaval = 16
area10:
If timeslooped > 12500 Then GoTo tryagain Else
randnum = Int((lastrow - 2 + 1) * Rnd + 2)
staff = Worksheets("Sheet2").Cells(randnum, 1).Value
x = WorksheetFunction.Index(ActiveWorkbook.Worksheets("Sheet2").Range(Cells(1, dayval), ActiveWorkbook.Worksheets("Sheet2").Cells(lastrow, dayval)), randnum, 0)
y = WorksheetFunction.Index(ActiveWorkbook.Sheets("Sheet2").Range(Cells(1, areaval), Worksheets("Sheet2").Cells(lastrow, areaval)), randnum, 0)
If x = "X" Or y = "X" Then
GoTo area10
Else
For check = 3 To 13
If Worksheets("Sheet1").Cells(check, dayval).Value = staff Then
timeslooped = timeslooped + 1
GoTo area10
Else
End If
Next check
For check = 20 To 21
If Worksheets("Sheet1").Cells(check, dayval).Value = staff Then
timeslooped = timeslooped + 1
GoTo area10
Else
End If
Next check
Worksheets("Sheet1").Cells(14, dayval).Value = staff
dayval = dayval + 1
If dayval = 7 Then
GoTo endarea10
Else: GoTo area10
End If
End If
endarea10:
dayval = 2
areaval = 17
area11:
If timeslooped > 12500 Then GoTo tryagain Else
randnum = Int((lastrow - 2 + 1) * Rnd + 2)
staff = Worksheets("Sheet2").Cells(randnum, 1).Value
x = WorksheetFunction.Index(ActiveWorkbook.Worksheets("Sheet2").Range(Cells(1, dayval), ActiveWorkbook.Worksheets("Sheet2").Cells(lastrow, dayval)), randnum, 0)
y = WorksheetFunction.Index(ActiveWorkbook.Sheets("Sheet2").Range(Cells(1, areaval), Worksheets("Sheet2").Cells(lastrow, areaval)), randnum, 0)
If x = "X" Or y = "X" Then
GoTo area11
Else
For check = 3 To 14
If Worksheets("Sheet1").Cells(check, dayval).Value = staff Then
timeslooped = timeslooped + 1
GoTo area11
Else
End If
Next check
For check = 20 To 21
If Worksheets("Sheet1").Cells(check, dayval).Value = staff Then
timeslooped = timeslooped + 1
GoTo area11
Else
End If
Next check
Worksheets("Sheet1").Cells(15, dayval).Value = staff
dayval = dayval + 1
If dayval = 7 Then
GoTo endarea11
Else: GoTo area11
End If
End If
endarea11:
dayval = 2
areaval = 18
area12:
If timeslooped > 12500 Then GoTo tryagain Else
randnum = Int((lastrow - 2 + 1) * Rnd + 2)
staff = Worksheets("Sheet2").Cells(randnum, 1).Value
x = WorksheetFunction.Index(ActiveWorkbook.Worksheets("Sheet2").Range(Cells(1, dayval), ActiveWorkbook.Worksheets("Sheet2").Cells(lastrow, dayval)), randnum, 0)
y = WorksheetFunction.Index(ActiveWorkbook.Sheets("Sheet2").Range(Cells(1, areaval), Worksheets("Sheet2").Cells(lastrow, areaval)), randnum, 0)
If x = "X" Or y = "X" Then
GoTo area12
Else
For check = 3 To 15
If Worksheets("Sheet1").Cells(check, dayval).Value = staff Then
timeslooped = timeslooped + 1
GoTo area12
Else
End If
Next check
For check = 20 To 21
If Worksheets("Sheet1").Cells(check, dayval).Value = staff Then
timeslooped = timeslooped + 1
GoTo area12
Else
End If
Next check
Worksheets("Sheet1").Cells(16, dayval).Value = staff
dayval = dayval + 1
If dayval = 7 Then
GoTo endarea12
Else: GoTo area12
End If
End If
endarea12:
dayval = 2
areaval = 19
area13:
If timeslooped > 12500 Then GoTo tryagain Else
randnum = Int((lastrow - 2 + 1) * Rnd + 2)
staff = Worksheets("Sheet2").Cells(randnum, 1).Value
x = WorksheetFunction.Index(ActiveWorkbook.Worksheets("Sheet2").Range(Cells(1, dayval), ActiveWorkbook.Worksheets("Sheet2").Cells(lastrow, dayval)), randnum, 0)
y = WorksheetFunction.Index(ActiveWorkbook.Sheets("Sheet2").Range(Cells(1, areaval), Worksheets("Sheet2").Cells(lastrow, areaval)), randnum, 0)
If x = "X" Or y = "X" Then
GoTo area13
Else
For check = 3 To 16
If Worksheets("Sheet1").Cells(check, dayval).Value = staff Then
timeslooped = timeslooped + 1
GoTo area13
Else
End If
Next check
For check = 20 To 21
If Worksheets("Sheet1").Cells(check, dayval).Value = staff Then
timeslooped = timeslooped + 1
GoTo area13
Else
End If
Next check
Worksheets("Sheet1").Cells(17, dayval).Value = staff
dayval = dayval + 1
If dayval = 7 Then
GoTo endarea13
Else: GoTo area13
End If
End If
endarea13:
dayval = 2
areaval = 20
area14:
If timeslooped > 12500 Then GoTo tryagain Else
randnum = Int((lastrow - 2 + 1) * Rnd + 2)
staff = Worksheets("Sheet2").Cells(randnum, 1).Value
x = WorksheetFunction.Index(ActiveWorkbook.Worksheets("Sheet2").Range(Cells(1, dayval), ActiveWorkbook.Worksheets("Sheet2").Cells(lastrow, dayval)), randnum, 0)
y = WorksheetFunction.Index(ActiveWorkbook.Sheets("Sheet2").Range(Cells(1, areaval), Worksheets("Sheet2").Cells(lastrow, areaval)), randnum, 0)
If x = "X" Or y = "X" Then
GoTo area14
Else
For check = 3 To 17
If Worksheets("Sheet1").Cells(check, dayval).Value = staff Then
timeslooped = timeslooped + 1
GoTo area14
Else
End If
Next check
For check = 20 To 21
If Worksheets("Sheet1").Cells(check, dayval).Value = staff Then
timeslooped = timeslooped + 1
GoTo area14
Else
End If
Next check
Worksheets("Sheet1").Cells(18, dayval).Value = staff
dayval = dayval + 1
If dayval = 7 Then
GoTo endarea14
Else: GoTo area14
End If
End If
endarea14:
For Z = 2 To lastrow
staff = Cells(Z, 1).Value
ival = Application.WorksheetFunction.CountIf(Sheets("Sheet1").Range("B3:F21"), staff)
If ival < 1 Then
dayval = 2
areaval = 7
GoTo area1
Else
End If
Next Z
Sheets("Sheet1").Activate
Application.ScreenUpdating = True
End Sub