From: icutey2 on 25 Apr 2010 00:15 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 From: joel on 25 Apr 2010 09:09 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 of 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 -- joel ------------------------------------------------------------------------ joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229 View this thread: http://www.thecodecage.com/forumz/showthread.php?t=198267 http://www.thecodecage.com/forumz  |