division

deeem · #1 (**permalink**) 02-27-06, 00:36

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). Each group has a unique name.

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.

Code:

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