Private Sub CommandButton1_Click()



Dim response As String
response = MsgBox("Are you ready to calculate the grades", vbQuestion + vbYesNo, "Prep")

    If response = vbYes Then
        Clearing
        Calcgrades
        MsgBox "All Commands Executed !"
    Else
        MsgBox "You have terminated the procedure !"
        Exit Sub
    End If
    
    Sheets("Class Roster").Activate

End Sub
Private Sub Calcgrades()

'declare sheet name variable
Dim xsheet As String

'declare row and column counter variables
Dim irow, jcol As Integer

'variable for the last row of data
Dim lastrow As Integer

'declare variales to hold the class attendance,quiz, homework, and test scores
Dim atten, mcad, mlab, labs, test, score As Double
Dim grade As String
grade = "N"

' 1.  First of all lets assess the attendance

xsheet = "Class Roster"
Sheets(xsheet).Activate

lastrow = Range("A65536").End(xlUp).Row

For irow = 3 To lastrow

    'set atten 0 to start each student
    atten = 0

    For jcol = 4 To 11
        If Sheets(xsheet).Cells(irow, jcol).Value <> 0 Then
            atten = atten + 1
        Else
        End If
    Next    'go to next week

    'convert attendance score to a percentage
    atten = 100 * atten / 8
    'save the result on the spreadsheet
    Sheets(xsheet).Cells(irow, 13) = atten

Next    'go to next student
    
    
'2. Now compile and collate Mathcad
xsheet = "mathcad"
Sheets(xsheet).Activate
Sheets(xsheet).Range("T1:AA1").Select

'lastrow = Range("A65536").End(xlUp).Row

For irow = 2 To (lastrow - 1)

    'set mcad 0 to start each student
    mcad = 0
    
    'mcad 1
    If Sheets(xsheet).Cells(irow, 5).Value <> "" Then
    mcad = mcad + Sheets(xsheet).Cells(irow, 5).Value
    Else
    End If
    
    'mcad 2
    If Sheets(xsheet).Cells(irow, 9).Value <> "" Then
    mcad = mcad + Sheets(xsheet).Cells(irow, 9).Value
    Else
    End If
    
    'mcad 3
    If Sheets(xsheet).Cells(irow, 15).Value <> "" Then
    mcad = mcad + Sheets(xsheet).Cells(irow, 15).Value
    Else
    End If
    
    'mcad 4
    If Sheets(xsheet).Cells(irow, 19).Value <> "" Then
    mcad = mcad + Sheets(xsheet).Cells(irow, 19).Value
    Else
    End If

    'save the result on the spreadsheet
    mcad = mcad / 4
    Sheets(xsheet).Cells(irow, 23) = mcad
    
Next    'go to next student


'3. Now compile and collate Matlab
xsheet = "matlab"
Sheets(xsheet).Activate
Sheets(xsheet).Range("Q1:AA1").Select

'lastrow = Range("A65536").End(xlUp).Row

For irow = 2 To (lastrow - 1)

    'set mlab 0 to start each student
    mlab = 0
    
    'mlab 1
    If Sheets(xsheet).Cells(irow, 5).Value <> "" Then
    mlab = mlab + Sheets(xsheet).Cells(irow, 5).Value
    Else
    End If
    
    'mlab 2
    If Sheets(xsheet).Cells(irow, 9).Value <> "" Then
    mlab = mlab + Sheets(xsheet).Cells(irow, 9).Value
    Else
    End If
    
    'mlab 3
    If Sheets(xsheet).Cells(irow, 13).Value <> "" Then
    mlab = mlab + Sheets(xsheet).Cells(irow, 13).Value
    Else
    End If
    
    'mlab 4
    If Sheets(xsheet).Cells(irow, 17).Value <> "" Then
    mlab = mlab + Sheets(xsheet).Cells(irow, 17).Value
    Else
    End If

    'save the result on the spreadsheet
    mlab = mlab / 4
    Sheets(xsheet).Cells(irow, 23) = mlab
    
Next    'student


'so now add up the labs and convert to percentage
For irow = 2 To (lastrow - 1)

    labs = Sheets("mathcad").Cells(irow, 23).Value + Sheets("matlab").Cells(irow, 23).Value
    labs = labs / 2
    'save to Class Roster
    Sheets("Class Roster").Cells(irow + 1, 14) = labs
    
Next    'go to next student


'4. Now lest do the tests
For irow = 2 To (lastrow - 1)

    'set atten 0 to start each student
    test = 0
    
    'test 1, mathcad over 40
    If Sheets("mathcad").Cells(irow, 13).Value <> "" Then
    test = test + Sheets("mathcad").Cells(irow, 13).Value
    Else
    End If
    
    'test 2 over 50
    If Sheets("matlab").Cells(irow, 21).Value <> "" Then
    test = test + Sheets("matlab").Cells(irow, 21).Value
    Else
    End If
    
    'convert test score to a percentage
    test = 100 * test / 90
    'save the result on the spreadsheet
    Sheets("Class Roster").Cells(irow + 1, 15) = test
    
Next    'go to next student


'5. Now the aggregate score and grade
For irow = 2 To (lastrow - 1)

    'set atten 0 to start each student
    score = 0
    
    'score calc
    'score = 20%*labs + 25%*test + 40%*attendance for a total of 85 at this time
    
    score = (1 / 85) * (25 * Sheets("Class Roster").Cells(irow + 1, 15).Value _
            + 20 * Sheets("Class Roster").Cells(irow + 1, 14).Value _
            + 40 * Sheets("Class Roster").Cells(irow + 1, 13).Value)
        
    'convert score to a percentage
    score = 100 * score / 85
    'save the result on the spreadsheet
    Sheets("Class Roster").Cells(irow + 1, 17) = score
    
    'grade
    If score >= 90 Then
        grade = "A"
    ElseIf score >= 80 And score < 90 Then
        grade = "B"
    ElseIf score >= 70 And score < 80 Then
        grade = "C"
    ElseIf score >= 60 And score < 70 Then
        grade = "D"
    ElseIf score < 60 Then
        grade = "F"
    End If
    
    'save the result on the spreadsheet
    Sheets("Class Roster").Cells(irow + 1, 18) = grade
    
Next    'go to next student



End Sub
Sub Clearing()

'clear previously calculated figures
Sheets("Class Roster").Activate
Range(Sheets("Class Roster").Cells(3, 13), Sheets("Class Roster").Cells(25, 18)).ClearContents

'Sheets("mathcad").Activate
'Sheets("mathcad").Cells(2, 15).Select
'Range(Sheets("mathcad").Cells(2, 23), Sheets("mathcad").Cells(20, 23)).ClearContents

'Sheets("matlab").Activate
'Sheets("matlab").Cells(2, 15).Select
'Range(Sheets("matlab").Cells(2, 23), Sheets("matlab").Cells(20, 23)).ClearContents

'Sheets("Class Roster").Activate

MsgBox "Previous data cleared. Click OK to continue"


End Sub