An excel macro to find the overall score in a spreadsheet given a students test results

FIND_OVERALL_SCORE.vba

Function FIND_OVERALL_SCORE(rng As range) As Integer
    Dim score
    Dim searchRange, scores As range
    Set scores = Worksheets("Master Scores").UsedRange
    Set searchRange = scores.Resize(scores.Rows.Count + 1, 4)
    
    For score = 0 To 4
        For Each row In searchRange.Rows
            If row.Cells(1, 1).Value <> "" Then
                If row.Cells(1).Value = rng.Cells(1).Value And row.Cells(2).Value = rng.Cells(2).Value And row.Cells(3).Value = rng.Cells(3).Value And row.Cells(4).Value = rng.Cells(4).Value Then
                    FIND_OVERALL_SCORE = 4 - score
                    Exit Function
                End If
            End If
        Next row
        Set searchRange = searchRange.Offset(0, 4)
    Next
End Function

Sub test()
    'Worksheets("Master Scores").UsedRange.Resize(1, 4).Offset(0, 4).Select
    MsgBox FIND_OVERALL_SCORE(Worksheets("Grading").range("B4:E4"))
End Sub