Division

[milkshake]

Hi all,

I have a list of teaching subjects, each subject consists of lecture, tutorial and practical. The list of subjects are repeated as there are a certain number of classes taking each subject.

Example: For Science, I have 2 lecture groups (made up of classes) and 6 tutorial groups (individual classes) as well as 6 practical groups(individual classes).

I want to take the number of tutorial groups or practical groups (either one) and divide them by the number of lecture groups to know how many classes are in each lecture group (lecture group is equally made up of classes).

How can I do this? Please help. Thank you.

Here is my code, I do not know how about to continue it, where I need to check for the number of classes in a particular lecture group. I am not sure how to sure the countdistinct or whether it is suitable for this need.

Sub allocate()
Dim modcode(700) As String
Dim actcode(700) As String
Dim modcode1(700) As String
Dim actcode1(700) As String
Dim actno(700) As String
Dim sesspattern(700) As String
Dim staffassign(700) As String
Dim arrLectgrp(700, 700) As Variant
Dim size(700) As Integer

Dim session(700) As String
Dim staff(700) As String
Dim activity(700) As String

lrow = Cells(Rows.Count, "B").End(xlUp).Row
j = 1
For i = 350 To lrow
'If Cells(i, "B") <> Cells(i + 1, "B") Then
If InStr(1, Cells(i + 1, "B"), "IT", vbTextCompare) = 1 Then 'to store subject codes, activity code (which in this case only lecture) for lectures only
If Cells(i + 1, "C") = "LEC" Then
modcode(j) = Cells(i + 1, "B") 'subject code
actcode(j) = Cells(i + 1, "C") ' activity code (only lecture)
actno(j) = Cells(i + 1, "D")
sesspattern(j) = Cells(i + 1, "F")
staffassign(j) = Cells(i + 1, "G")
j = j + 1
End If
End If
'End If
Next i

For l = 350 To lrow
'If Cells(i, "B") <> Cells(i + 1, "B") Then
If InStr(1, Cells(i + 1, "B"), "IT", vbTextCompare) = 1 Then 'to store subject codes, activity code (tutorial/practical) for tutorials and practicals only
If Cells(i + 1, "C") = "TUT" Or "LAB" Then
modcode1(k) = Cells(i + 1, "B") 'subject code
actcode1(k) = Cells(i + 1, "C") 'activity code (whether tutorial or lab)
k = k + 1
End If
End If
'End If
Next l


'to check for the number of classes in each modcode(j) lecture by dividing the number of tutorial or practical classes for particular modcode(j)
'lecture groups of same modcode will consist same number of classes


ActiveWorkbook.Save

Application.ScreenUpdating = True
MsgBox "Lectures Allocated"
End Sub