3 hour rotating schedule

[icutey2]

I would like to make a rotating schedule for our vbs. It would be for 3
hours a day. Each day being the same schedule. Time start would be 6:00
p.m. End would be 9:00 p.m. I have 5 groups and a total of 6 classes or time
slots.

Opening assembly being 15 mins long
2nd class 20 mins
3rd class 60 mins
4th class 20 mins
5th class 55 mins
closing assembly 15 mins

something like this:...

Preschool/kind Beginner Primary
Junior Teens

opening assembly x:00 x:00 x:00
x:00 x:00
15 min
Music x x x
x x
20
Bible Lesson x x x
x x
60
snacks x x x
x x
20
crafts x x x
x x
55
Closing Assembly x x x
x x
15 min

 

[joel]

See if this helps


Sub MakeSchedule()

Const NumberofGroups = 5
Const NumberofDays = 10

TimePeriods = Array("6:00", "6:15", "6:35", "7:35", "7:55", "8:50")
Activities = Array("Music", "Bible Lesson", "snacks", "crafts")
NumberofActivities = UBound(Activities) + 1

Range("A1") = "Day"
Range("B1") = "Time"
Range("C1") = "Preschool/kind"
Range("D1") = "Beginner"
Range("E1") = "Primary"
Range("F1") = "Junior"
Range("G1") = "Teens"

StartActivity = 1
RowCount = 2
For DayCount = 1 To NumberofDays
ClassCount = 0
For Each ClassTime In TimePeriods
If ClassCount = 0 Then
Range("A" & RowCount) = DayCount
End If
For Group = 1 To NumberofGroups
Range("B" & RowCount) = ClassTime
Select Case ClassCount
Case 0
Activity = "Opening Assembly"
Case 5
Activity = "Closing Assembly"
Case Else
'index of array starts at 0 so adjust as required
GroupNumber = StartActivity + ClassCount + _
(Group - 1)
'use mod function to get a number 0 to Number o
Activities
GroupNumber = GroupNumber Mod NumberofActivities
Activity = Activities(GroupNumber)
End Select
Cells(RowCount, Group + 2) = Activity
Next Group
ClassCount = ClassCount + 1
RowCount = RowCount + 1
Next ClassTime
If StartActivity = NumberofActivities Then
StartActivity = 1
Else
StartActivity = StartActivity + 1
End If
Next DayCount

End Sub