COUNTIFS AND MESSAGEBOXES... HELP!

[curph]

I have a countIF macro that counts up the number of homeworks each student in my system does (My students are in cells A6:A120).
What i want it to do is, on my sheet activation, look at which students have 2 homeworks missed and which have 3 homeworks missed, and display a number of message boxes.
If a student has missed 2 homeworks I want the msgbox to display:
has missed 2 homeworks.
I dont know how to do this so their names are inserted on the msgbox.
Help please!!

[ This Message was edited by: curph on 2002-04-10 06:51 ]

[Al Chara]

Try the following:

Sub MissedHW()
Dim TotalHW, NumStudents, i As Integer
NumStudents = Intersect(Columns(1), ActiveSheet.UsedRange).Rows.Count - 1
TotalHW = Range("c2").Value
For i = 1 To NumStudents
If TotalHW - Range("B" & i + 1).Value = 2 Then
MsgBox Range("A" & i + 1).Value & " has missed 2 homeworks"
ElseIf TotalHW - Range("B" & i + 1).Value = 3 Then
MsgBox Range("A" & i + 1).Value & " has missed 3 homeworks"
End If
Next i
End Sub

This macro assumes that the names are in column A starting with row 2. Column B is how many homeworks they have done and cell C2 is the total number of homeworks assigned. So the macro loops through each name and subtracts the number of homeworks done per student from the total number of homeworks assigned. If this number is 2 or 3 then it displays a message.