' TICUA Enrollment data checking program
' Version: 1.1.2
' Checks Excel files for proper data formatting / values for submission to TICUA
' written in VBA for Office 2003/XP - should work fine on Office 2000
' Last update: 09-9-2007 by M. W. Albert
' Please backup your data before running!
'
' This program is distributed freely, with no guarantees of any kind

Option Explicit

Dim intNumberOfRecords As Integer
' Declare variables to hold the column names containing variables we'll need
Dim strSTDTNU As String
Dim strINTNUM As String
Dim strRACE As String
Dim strFICECODE As String
Dim strTERM As String
Dim strYear As String
Dim strGENDER As String
Dim strCITZEN As String
Dim strRESIDT As String
Dim strBIRTHD As String
Dim strPZIP As String
Dim strPCOUNTY As String
Dim strPCNTRY As String
Dim strPSTATE As String
Dim strHOPEFO As String
Dim strHOPEDU As String
Dim strInTELSRegain As String
Dim strRegainTELS As String
Dim strRepeatTELS As String
Dim strSLEVEL As String
Dim strREGTYPE As String
Dim strFirstTR As String
Dim strTFICE As String
Dim strMAJOR1 As String
Dim strATMHME As String
Dim strATMTELS As String
Dim strERNHME As String
Dim strCURHRS As String
Dim strRemDevHrs As String
Dim strGPAHME As String
Dim strGPATELS As String
Dim strInTELS As String
Dim strLostTELS As String
Dim strHSCODE As String
Dim strGRADTYPE As String
Dim strGRADDATE As String
Dim strHSGPA As String
Dim strUNWTGPA As String
Dim strNUMAP As String
Dim strACT As String
Dim strSAT As String
Dim strPELL As String
Dim strSTFSUB As String
Dim strSTFUSB As String
Dim strTSAAG As String
Dim strHOPEGR As String
Dim strHOPESC As String
Dim strHOPESU As String
Dim strGMERIT As String
Dim strFEDAID As String
Dim strSTAAID As String
Dim strPCAID As String
Dim strINSAID As String
Dim strTOTLOA As String
Dim strERROR As String

Sub CheckSTDTNULowAndHigh()
    ' If STDTNU begins with 000- or at/above 790- check to see
    ' that they are CITZEN = N or PR - if not, flag. STDTNU's in this range are normally for non-citizens and permanent residents
    Dim intCounter As Integer
    Worksheets("Enrollment").Select
    
    For intCounter = 2 To (intNumberOfRecords + 1)
        If Range(strSTDTNU & intCounter).Value <> "" Then
            If (Val(Range(strSTDTNU & intCounter).Value)) < 1000000 Or (Val(Range(strSTDTNU & intCounter).Value)) >= 790000000 Then
                If Range(strCITZEN & intCounter).Value <> "PR" And Range(strCITZEN & intCounter).Value <> "N" Then
                    Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Student with STDTNUs of 000- or >= 790 are normally not US citizens - please verify. "
                End If
            End If
        End If
    Next intCounter
End Sub

Sub CheckRACE1s()
    ' If a student has a RACE code of 1, check to make sure that their PCNTRY is not listed as US, and CITZEN is not listed as Y
    Dim intCounter As Integer
    Worksheets("Enrollment").Select
    For intCounter = 2 To (intNumberOfRecords + 1)
        If Range(strRACE & intCounter).Value = 1 Then
            If Range(strPCNTRY & intCounter).Value = "US" Then
                Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Student cannot have RACE = 1 and have PCNTRY = US. "
            End If
            If Range(strCITZEN & intCounter).Value = "Y" Then
                Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Student cannot have RACE = 1 and have CITZEN = Y. "
            End If
        End If
    Next intCounter
End Sub

Sub CheckCITZENNo()
    ' IF a student has a CITZEN value of N, check to make sure their PCNTRY is not listed as US and RACE code is 1
    Dim intCounter As Integer
    Worksheets("Enrollment").Select
    For intCounter = 2 To (intNumberOfRecords + 1)
        If Range(strCITZEN & intCounter).Value = "N" Then
            If Range(strPCNTRY & intCounter).Value = "US" Then
                Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Student cannot have CITZEN = N and have PCNTRY = US. "
            End If
            If Range(strRACE & intCounter).Value <> 1 Then
                Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Student cannot have CITZEN = N and a RACE other than 1. "
            End If
        End If
        Next intCounter
End Sub


Sub CheckBirthDT()
    ' Check to see that each student has a BirthD value
    Dim intCounter As Integer
    Worksheets("Enrollment").Select
    ' Check to see if blank
    For intCounter = 2 To (intNumberOfRecords + 1)
        If Range(strBIRTHD & intCounter).Value = "" Then
            Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Missing BirthD. "
        End If
        If Range(strBIRTHD & intCounter).Value <> "" Then
            If IsDate(Range(strBIRTHD & intCounter).Value) = False Then
                Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "BirthD must be a date. "
            Else
                ' Check that BirthD is not too close to the current date
                If DateDiff("yyyy", Range(strBIRTHD & intCounter).Value, Now) < 16 Then
                    Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "BirthD is too close to current date - please check. "
                End If
            End If
        End If
    Next intCounter
End Sub

Sub CheckFICECODE()
    ' Check to see that each student has a FICECODE value
    Dim intCounter As Integer
    
    Worksheets("Enrollment").Select
    ' Check to see if blank
    For intCounter = 2 To (intNumberOfRecords + 1)
        If Range(strFICECODE & intCounter).Value = "" Then
            Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Missing FICECODE. "
        End If
    Next intCounter
End Sub

Sub CheckTERM()
    ' Check to see that each student has a TERM value and that the length of TERM is 2
    Dim intCounter As Integer
    
    Worksheets("Enrollment").Select
    ' Check to see if blank
    For intCounter = 2 To (intNumberOfRecords + 1)
        If Range(strTERM & intCounter).Value = "" Then
            Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Missing TERM. "
        Else
            If Len(Range(strTERM & intCounter).Value) <> 2 Then
                Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "TERM value is an invalid length. "
            End If
        End If
    Next intCounter
    
End Sub

Sub CheckYEAR()
    ' Check to see that each student has a Year value
    Dim intCounter As Integer
    
    Worksheets("Enrollment").Select
    ' Check to see if blank
    For intCounter = 2 To (intNumberOfRecords + 1)
        If Range(strYear & intCounter).Value = "" Then
            Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Missing YEAR. "
        Else
            If Len(Range(strYear & intCounter).Value) <> 4 Then
                Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "YEAR value is an invalid length. "
            End If
        End If
    Next intCounter
    
End Sub

Sub CheckRACE()
    ' Check to see each student has a RACE value and that it is valid
    ' Valid RACE values are 1, 2, 3, 4, 5, 6, 7
    Dim intCounter As Integer
    
    Worksheets("Enrollment").Select
    For intCounter = 2 To (intNumberOfRecords + 1)
        ' Check to see if blank
        If Range(strRACE & intCounter).Value = "" Then
            Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Missing RACE. "
        Else
            ' If not blank, check to see if numeric
            If Not (IsNumeric(Range(strRACE & intCounter).Value)) Then
                Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Invalid RACE (must be a numeric code). "
            Else
            ' If numeric, check to see if 1 - 7
                If (Range(strRACE & intCounter).Value < 1) Or (Range(strRACE & intCounter).Value > 7) Then
                    Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Invalid RACE (out of valid range). "
                End If
            End If
        End If
    Next intCounter
    
End Sub

Sub CheckGENDER()
    ' Check to see that each student record has a GENDER value and that it is valid
    ' Valid GENDER values are M, F, and U uppercase only
    Dim intCounter As Integer
    
    Worksheets("Enrollment").Select
    For intCounter = 2 To (intNumberOfRecords + 1)
        ' Check to see if missing
        If Range(strGENDER & intCounter).Value = "" Then
            Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Missing GENDER. "
        End If
        ' if not missing, check to see if valid value
        If Range(strGENDER & intCounter).Value <> "" And Range(strGENDER & intCounter).Value <> "F" And Range(strGENDER & intCounter).Value <> "M" And Range(strGENDER & intCounter).Value <> "U" Then
            Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Invalid GENDER. "
        End If
    Next intCounter
End Sub

Sub CheckCITZEN()
    ' Check to see that each student record has a CITZEN value and that it is valid
    ' Valid CITZEN values are Y and N, PR, uppercase only
    Dim intCounter As Integer
    
    Worksheets("Enrollment").Select
    For intCounter = 2 To (intNumberOfRecords + 1)
        ' Check to see if blank
        If Range(strCITZEN & intCounter).Value = "" Then
            Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Missing CITZEN. "
        End If
        ' If not blank, check to see if matches valid values
        If Range(strCITZEN & intCounter).Value <> "" And (Range(strCITZEN & intCounter).Value <> "Y" And Range(strCITZEN & intCounter).Value <> "N" And Range(strCITZEN & intCounter).Value <> "PR") Then
            Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Invalid CITZEN. "
        End If
    Next intCounter
End Sub

Sub CheckRESIDT()
    ' Check to see that each student record has a RESIDT value and that it is valid
    ' Valid RESIDT values are I and O, uppercase only
    Dim intCounter As Integer
    
    Worksheets("Enrollment").Select
    For intCounter = 2 To (intNumberOfRecords + 1)
        ' Check to see if blank
        If Range(strRESIDT & intCounter).Value = "" Then
            Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Missing RESIDT. "
        Else
            If Range(strRESIDT & intCounter).Value = "I" Then
                If Range(strPSTATE & intCounter).Value <> "TN" And Range(strPSTATE & intCounter).Value <> "AL" And Range(strPSTATE & intCounter).Value <> "MS" And Range(strPSTATE & intCounter).Value <> "GA" And Range(strPSTATE & intCounter).Value <> "KY" And Range(strPSTATE & intCounter).Value <> "MO" And Range(strPSTATE & intCounter).Value <> "NC" And Range(strPSTATE & intCounter).Value <> "VA" And Range(strPSTATE & intCounter).Value <> "AR" Then
                    Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "RESIDT = I, but PSTATE is not equal to TN or surrounding states. "
                End If
            End If
        End If
        ' If not blank, check to see if matches valid values
        If Range(strRESIDT & intCounter).Value <> "" And Range(strRESIDT & intCounter).Value <> "I" And Range(strRESIDT & intCounter).Value <> "O" Then
            Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Invalid RESIDT. "
        End If
    Next intCounter
    
End Sub

Sub CheckPZIP()
    ' Check to see that each US resident has a PZIP value
    Dim intCounter As Integer
    
    Worksheets("Enrollment").Select
    For intCounter = 2 To (intNumberOfRecords + 1)
        If Range(strPCNTRY & intCounter).Value = "US" Then
            If Range(strPZIP & intCounter).Value = "" Then
                Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Has PCNTRY of US but missing PZIP. "
            Else
            ' If not blank, check to see the value is numeric
                If Not (IsNumeric(Range(strPZIP & intCounter).Value)) Then
                    Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Invalid PZIP. "
                End If
             End If
        End If
    Next intCounter
    
End Sub

Sub CheckPCOUNTY()
    Dim intCounter As Integer
    ' Check to see that if a student has a PCOUNTY value that it is numeric (as opposed to the name of the county)
    For intCounter = 2 To (intNumberOfRecords + 1)
        If Range(strPCOUNTY & intCounter).Value <> "" Then
            If IsNumeric(Range(strPCOUNTY & intCounter).Value) <> True And Range(strPCOUNTY & intCounter).Value <> "TN" Then
                Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "PCOUNTY is non-numeric. PCOUNTY must be a 3-digit numeric code or TN. "
            End If
        End If
    Next intCounter
    
    ' Check to see that each student that has a RESIDT=I has a PCOUNTY value (if you are In-State as classified by the lottery, we need PCOUNTY
    
    Worksheets("Enrollment").Select
    For intCounter = 2 To (intNumberOfRecords + 1)
        If Range(strRESIDT & intCounter).Value = "I" Then
            If Range(strPCOUNTY & intCounter).Value = "" Then
                Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Students with RESIDT=I must have PCOUNTY. "
            End If
        End If
    Next intCounter
End Sub

Sub CheckPCNTRY()
     ' Check to see that each student has a PCNTRY value
    Dim intCounter As Integer
    
    Worksheets("Enrollment").Select
    For intCounter = 2 To (intNumberOfRecords + 1)
        ' Check blank
        If Range(strPCNTRY & intCounter).Value = "" Then
            Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Missing PCNTRY. "
        Else
        ' if a student does have a PCNTRY value, check to see that the length of the value is 2
            If Len(Range(strPCNTRY & intCounter).Value) <> 2 Then
                Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "PCNTRY is an invalid length (<> 2). "
            End If
            ' Check that if student has a PCNTRY value of other than US, they do not have a PSTATE, PZIP or PCOUNTY
            If Range(strPCNTRY & intCounter).Value <> "US" Then
                If Range(strPSTATE & intCounter).Value <> "" Then
                    Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Students with PCNTRY other than US should not have a PSTATE value. "
                End If
                If Range(strPZIP & intCounter).Value <> "" Then
                    Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Students with PCNTRY other than US should not have a PZIP value. "
                End If
                If Range(strPCOUNTY & intCounter).Value <> "" Then
                    Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Students with PCNTRY other than US should not have a PCOUNTY value. "
                End If
            End If
        End If
    Next intCounter
    

    
End Sub

Sub CheckSLEVEL()
    ' Check to see that each student has an SLEVEL value and that it is valid
    ' Valid values are 1 - 18
    Dim intCounter As Integer
   
    Worksheets("Enrollment").Select
    For intCounter = 2 To (intNumberOfRecords + 1)
        If Range(strSLEVEL & intCounter).Value = "" Then
            Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Missing SLEVEL. "
        Else
            If Not (IsNumeric(Range(strSLEVEL & intCounter).Value)) Then
                Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Invalid SLEVEL. "
            Else
                If (Range(strSLEVEL & intCounter).Value < 1) Or (Range(strSLEVEL & intCounter).Value > 18) Then
                    Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Invalid SLEVEL. "
                End If
            End If
        End If
    Next intCounter
End Sub
    
Sub CheckFirstTR()
    'Check to see that each student has a valid FirstTR value, either Y or N
    Dim intCounter As Integer
    
    Worksheets("Enrollment").Select
    For intCounter = 2 To (intNumberOfRecords + 1)
    ' if FirstTR = 'Yes' change to 'Y'
        If Range(strFirstTR & intCounter).Value = "Yes" Then
            Range(strFirstTR & intCounter).Value = "Y"
        End If
    ' if FirstTR = 'No' then change to 'N'
        If Range(strFirstTR & intCounter).Value = "No" Then
            Range(strFirstTR & intCounter).Value = "N"
        End If
        ' Check to see if blank
        If Range(strFirstTR & intCounter).Value = "" Then
            Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Missing FirstTR. "
        End If
        ' If not blank, check to see if matches valid values
        If Range(strFirstTR & intCounter).Value <> "" And Range(strFirstTR & intCounter).Value <> "Y" And Range(strFirstTR & intCounter).Value <> "N" Then
            Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Invalid FirstTR. "
        End If
    Next intCounter
    
End Sub

Sub CheckFirstTRandTFICE()
    ' Check to see that each student having FirstTR value = "Yes" also has a TFICE code
    Dim intCounter As Integer
    
    Worksheets("Enrollment").Select
    For intCounter = 2 To (intNumberOfRecords + 1)
        If Range(strFirstTR & intCounter).Value = "Y" Then
            If Range(strTFICE & intCounter).Value = "" Then
                Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Listed as FirstTR but missing TFICE. "
            End If
        End If
    Next intCounter
End Sub

Sub CheckTFICE()
    ' Check to see that each student having a TFICE value is listed as a FirstTR
    Dim intCounter As Integer

    Worksheets("Enrollment").Select
    For intCounter = 2 To (intNumberOfRecords + 1)
        If Range(strTFICE & intCounter).Value <> "" And Range(strFirstTR & intCounter).Value <> "Y" Then
            Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Has value in TFICE but not listed as FirstTR. "
        End If
    Next intCounter
End Sub

Sub CheckMAJOR1()
    ' Check that students having SLEVEL of 3 or greater have a MAJOR1
    Dim intCounter As Integer
    Worksheets("Enrollment").Select
    For intCounter = 2 To (intNumberOfRecords + 1)
        If Range(strMAJOR1 & intCounter).Value = "" Then
            Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Missing MAJOR1. "
        Else
            If IsNumeric(Range(strMAJOR1 & intCounter).Value) = False Then
                Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "MAJOR1 is non-numeric. "
            End If
        End If
    Next intCounter
    
    For intCounter = 2 To (intNumberOfRecords + 1)
        If (Range(strSLEVEL & intCounter).Value > 2 And Range(strSLEVEL & intCounter).Value < 6) Or Range(strSLEVEL & intCounter).Value > 7 Then
                If Range(strMAJOR1 & intCounter).Value = "" Or Range(strMAJOR1 & intCounter).Value = 0 Then
                    Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Students with this SLEVEL must have a MAJOR1. "
                End If
        End If
    Next intCounter
End Sub

Sub CheckATMHME()
    ' Check that all students have a value in ATMHME
    Dim intCounter As Integer
    Worksheets("Enrollment").Select
    For intCounter = 2 To (intNumberOfRecords + 1)
            If Range(strATMHME & intCounter).Value = "" Then
                Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Missing ATMHME. "
            End If
    Next intCounter
    
End Sub

Sub CheckATMTELS()
    ' Check that all TELS students have a value in ATMTELS
    Dim intCounter As Integer
    
    Worksheets("Enrollment").Select
    For intCounter = 2 To (intNumberOfRecords + 1)
        ' Check to see if there is an InTELS date - if so, check for ATMTELS
        If Range(strInTELS & intCounter).Value <> "" Then
            If Range(strATMTELS & intCounter).Value = "" Then
                Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Missing ATMTELS. "
            End If
        End If
    Next intCounter
End Sub

Sub CheckPSTATE()
    ' Check that all students have a PSTATE if US resident
    Dim intCounter As Integer
    
    Worksheets("Enrollment").Select
    For intCounter = 2 To (intNumberOfRecords + 1)
    If Range(strPSTATE & intCounter).Value <> "" Then
        If Len(Range(strPSTATE & intCounter).Value) <> 2 Then
            Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Invalid PSTATE. Must be a 2-letter abbreviation. "
        End If
    End If
    If Range(strPCNTRY & intCounter).Value = "US" Then
        If Range(strPSTATE & intCounter).Value = "" Then
            Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Has PCNTRY = US but missing PSTATE. "
        End If
    End If
    Next intCounter
End Sub

Sub CheckERNHME()
    ' Check that all students have a ERNHME value
    Dim intCounter As Integer
    
    Worksheets("Enrollment").Select
    For intCounter = 2 To (intNumberOfRecords + 1)
        If Range(strERNHME & intCounter).Value = "" Then
           Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Missing ERNHME. "
        End If
    Next intCounter
    
End Sub

Sub CheckCURHRS()
    ' Check CURHRS is less than or equal to 0 or greater than 24, flag as suspect
    Dim intCounter As Integer
    
    Worksheets("Enrollment").Select
    For intCounter = 2 To (intNumberOfRecords + 1)
        If Range(strCURHRS & intCounter).Value = "" Then
           Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Missing CURHRS. "
        Else
           If Not (IsNumeric(Range(strCURHRS & intCounter).Value)) Then
                Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Invalid CURHRS. "
           Else
             If Range(strCURHRS & intCounter).Value = 0 Then
               Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Suspect value in CURHRS (0) - Confirm accuracy. "
             End If
             If Range(strCURHRS & intCounter).Value >= 24 Then
                  Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Suspect value in CURHRS (>= 24) - double check. "
             End If
           End If
        End If
    Next intCounter
End Sub

Sub CheckREGTYPE()

    Dim intCounter As Integer
    
    Worksheets("Enrollment").Select
    ' Check to see that all students have a REGTYPE
    For intCounter = 2 To (intNumberOfRecords + 1)
        If Range(strREGTYPE & intCounter).Value = "" Then
            Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Missing REGYPE. "
        End If
    Next intCounter
    
    ' Check that if FirstTR = Y then REGTYPE = 3 and vice versa
    Worksheets("Enrollment").Select
     For intCounter = 2 To (intNumberOfRecords + 1)
      If Range(strFirstTR & intCounter).Value = "Y" Then
         If Range(strREGTYPE & intCounter).Value <> 3 Then
                Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "If FirstTR, then REGTYPE must be 3. "
         End If
        End If
    Next intCounter

    Worksheets("Enrollment").Select
     For intCounter = 2 To (intNumberOfRecords + 1)
      If Range(strREGTYPE & intCounter).Value = 3 Then
         If Range(strFirstTR & intCounter).Value <> "Y" Then
                Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "If REGTYPE = 3, FirstTR must be Y. "
         End If
        End If
    Next intCounter

    ' Check that if REGTYPE = 1, SLEVEL = 1
    Worksheets("Enrollment").Select
     For intCounter = 2 To (intNumberOfRecords + 1)
      If Range(strREGTYPE & intCounter).Value = 1 Then
         If Range(strSLEVEL & intCounter).Value <> 1 Then
                Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Please verify REGTYPE and SLEVEL. If REGTYPE = 1, SLEVEL will usually be 1. "
         End If
        End If
    Next intCounter

    ' Check that if REGTYPE = 2, then SLEVEL > 6
    Worksheets("Enrollment").Select
     For intCounter = 2 To (intNumberOfRecords + 1)
      If Range(strREGTYPE & intCounter).Value = 2 Then
         If Range(strSLEVEL & intCounter).Value < 7 Then
                Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "If REGTYPE = 2, SLEVEL must be > 6. "
         End If
        End If
    Next intCounter
    
End Sub

Sub CheckRemDevHrs()
    ' Check that all TELS students have a value in RemDevHrs
    Dim intCounter As Integer
    
    Worksheets("Enrollment").Select
    For intCounter = 2 To (intNumberOfRecords + 1)
        ' Check to see if there is an InTELS date - if so, check for RemDevHrs
        If Range(strInTELS & intCounter).Value <> "" Then
            If Range(strRemDevHrs & intCounter).Value = "" Then
                Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Missing RemDevHrs. "
            End If
        End If
    Next intCounter
        
End Sub

Sub CheckGPAHME()
    ' Check that all TELS students have a value in GPAHME
    Dim intCounter As Integer
    
    Worksheets("Enrollment").Select
    For intCounter = 2 To (intNumberOfRecords + 1)
        ' Check to see if there is an InTELS date - if so, check for GPAHME
        If Range(strInTELS & intCounter).Value <> "" Then
            If Range(strGPAHME & intCounter).Value = "" Then
                Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Missing GPAHME. "
            End If
        End If
    Next intCounter
End Sub

Sub CheckGPATELS()
    ' Check that all TELS students have a value in GPATELS
    Dim intCounter As Integer
    
    Worksheets("Enrollment").Select
    For intCounter = 2 To (intNumberOfRecords + 1)
        ' Check to see if there is an InTELS date - if so, check for GPATELS
        If Range(strInTELS & intCounter).Value <> "" Then
            If Range(strGPATELS & intCounter).Value = "" Then
                Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Missing GPATELS. "
            End If
        End If
    Next intCounter
End Sub

Sub CheckInTELS()
    ' Check to see that students with a TELS award (HOPESC, HOPEGR, GMERIT, HOPEQU, HOPEDU, HOPEFO) have an InTELS date
    Dim intCounter As Integer
    Worksheets("Enrollment").Select
    For intCounter = 2 To (intNumberOfRecords + 1)
        If Range(strHOPEDU & intCounter).Value > 0 Or Range(strHOPEFO & intCounter).Value > 0 Or Range(strHOPEGR & intCounter).Value > 0 Or Range(strHOPESC & intCounter).Value > 0 Or Range(strHOPESU & intCounter).Value > 0 Or Range(strGMERIT & intCounter).Value > 0 Then
            If Range(strInTELS & intCounter).Value = "" Then
                Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Has TELS award but missing InTELS date. "
            End If
        End If
        If Range(strInTELS & intCounter).Value <> "" Then
            If IsDate(Range(strInTELS & intCounter).Value) = False Then
                Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "InTELS must be a date. "
            End If
        End If
    Next intCounter
 
    ' Check that all students with an InTELS date have a TELS award amount in either HOPESC or HOPEGR (a student may have an Access Grant, but no main TELS award)
    Worksheets("Enrollment").Select
    For intCounter = 2 To (intNumberOfRecords + 1)
            If Range(strInTELS & intCounter).Value <> "" Then
                If Range(strLostTELS & intCounter).Value = "" Then
                    If Val(Range(strHOPESC & intCounter).Value) = 0 And Val(Range(strHOPEGR & intCounter).Value) = 0 And Range(strLostTELS & intCounter).Value = "" Then
                        Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Has InTELS date but no amount in either HOPESC or HOPEGR. "
                    End If
                End If
            End If
    Next intCounter
 
 
    For intCounter = 2 To (intNumberOfRecords + 1)
        If Range(strInTELS & intCounter).Value <> "" And Range(strLostTELS & intCounter).Value = "" Then
            ' If a student has an InTELS date, check for SSN
            If Range(strSTDTNU & intCounter).Value = "" Then
                Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "TELS student missing STDTNU (Required for TELS students). "
            End If
            ' Flag if student has InTELS date, but is RESIDT <> I - students must be RESIDT = I to qualify for TELS
            If Range(strRESIDT & intCounter).Value <> "I" And Range(strRESIDT & intCounter).Value <> "" Then
                Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "TELS students must be RESIDT = I. "
            End If
            ' Flag if student has InTELS date, but PCNTRY is not equal to US
            If Range(strPCNTRY & intCounter).Value <> "" And Range(strPCNTRY & intCounter).Value <> "US" Then
                Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "TELS students must have PCNTRY = US. "
            End If
            ' Flag if student has InTELS date, but RACE = 1
            If Range(strPCNTRY & intCounter).Value <> "" And Val(Range(strRACE & intCounter).Value) = 1 Then
                Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "TELS students cannot have RACE = 1. "
            End If
            ' Flag if student has InTELS date, but CITZEN = N
            If Range(strPCNTRY & intCounter).Value <> "" And Range(strCITZEN & intCounter).Value = "N" Then
                Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "TELS students cannot have CITZEN = N. "
            End If
        End If
    Next intCounter
    
End Sub

Sub CheckLostTELS()
 ' If a student has a LostTELS reason, check that it is one of the valid codes - A - F or O
 Dim intCounter As Integer
    For intCounter = 2 To (intNumberOfRecords + 1)
        If Range(strLostTELS & intCounter).Value <> "" Then
            If Range(strLostTELS & intCounter).Value <> "A" And Range(strLostTELS & intCounter).Value <> "B" And Range(strLostTELS & intCounter).Value <> "C" And Range(strLostTELS & intCounter).Value <> "D" And Range(strLostTELS & intCounter).Value <> "E" And Range(strLostTELS & intCounter).Value <> "F" And Range(strLostTELS & intCounter).Value <> "O" Then
                    Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Invalid LostTELS reason code. "
            End If
        End If
    Next intCounter
    
 ' if a student has a LostTELS reason, check to see that they do not have any TELS award amounts
    For intCounter = 2 To (intNumberOfRecords + 1)
        If Range(strLostTELS & intCounter).Value <> "" And Range(strRegainTELS & intCounter).Value = "N" Then
                  If Range(strHOPEDU & intCounter).Value > 0 Or Range(strHOPEFO & intCounter).Value > 0 Or Range(strHOPEGR & intCounter).Value > 0 Or Range(strHOPESC & intCounter).Value > 0 Or Range(strHOPESU & intCounter).Value > 0 Or Range(strGMERIT & intCounter).Value > 0 Then
                                    Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Has LostTELS reason code but has TELS award. "
                  End If
        End If
    Next intCounter

End Sub

Sub CheckHSCODE()
    ' Check that all TELS students have a value in HSCODE
    Dim intCounter As Integer
    
    Worksheets("Enrollment").Select
    For intCounter = 2 To (intNumberOfRecords + 1)
        ' Check to see if there is an InTELS date - if so, check for HSCODE
        If Range(strInTELS & intCounter).Value <> "" Then
            If Range(strHSCODE & intCounter).Value = "" Then
                Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Missing HSCODE. "
            End If
        End If
    Next intCounter
End Sub

Sub CheckGRADTYPE()
    ' Check that all TELS students have a value in GRADTYPE
    Dim intCounter As Integer
    
    Worksheets("Enrollment").Select
    For intCounter = 2 To (intNumberOfRecords + 1)
        ' Check to see if there is an InTELS date - if so, check for GRADTYPE
        If Range(strInTELS & intCounter).Value <> "" Then
            If Range(strGRADTYPE & intCounter).Value = "" Then
                Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Missing GRADTYPE. "
            End If
        End If
    Next intCounter
End Sub

Sub CheckGRADDATE()
    ' Check that all TELS students have a value in GRADDATE
    Dim intCounter As Integer
    
    Worksheets("Enrollment").Select
    For intCounter = 2 To (intNumberOfRecords + 1)
        ' Check to see if there is an InTELS date - if so, check for GRADDATE
        If Range(strInTELS & intCounter).Value <> "" Then
            If Range(strGRADDATE & intCounter).Value = "" Then
                Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Missing GRADDATE. "
            End If
        End If
        ' if a student has a GRADDATE value, check that it is actually a date
        If Range(strGRADDATE & intCounter).Value <> "" Then
            If IsDate(Range(strGRADDATE & intCounter).Value) = False Then
                Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "GRADDATE must be a date. "
            End If
        End If
    Next intCounter
End Sub

Sub CheckHSGPA()
    ' Check that all TELS students have a value in HSGPA
    Dim intCounter As Integer
    
    Worksheets("Enrollment").Select
    For intCounter = 2 To (intNumberOfRecords + 1)
        ' Check to see if there is an InTELS date - if so, check for HSGPA
        If Range(strInTELS & intCounter).Value <> "" Then
            If Range(strHSGPA & intCounter).Value = "" Then
                Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Missing HSGPA. "
            End If
        End If
    Next intCounter
    
' If a student has an HSGPA and it is above 5, flag.
    For intCounter = 2 To (intNumberOfRecords + 1)
        ' Check to see if there is an InTELS date - if so, check HSGPA
        If Range(strInTELS & intCounter).Value <> "" Then
            If Range(strHSGPA & intCounter).Value <> "" Then
                If Range(strHSGPA & intCounter).Value > 5 Then
                    Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Suspect HSGPA (above 5.0)- please verify. "
                End If
            End If
        End If
    Next intCounter
    
' If a student has an HSGPA and it is below 2.0, flag.
    For intCounter = 2 To (intNumberOfRecords + 1)
            ' Check to see if there is an InTELS date - if so, check HSGPA
        If Range(strInTELS & intCounter).Value <> "" Then
          If Range(strHSGPA & intCounter).Value <> "" Then
            If Range(strHSGPA & intCounter).Value < 2 Then
                Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Suspect HSGPA (below 2.0)- please verify. "
            End If
          End If
        End If
    Next intCounter

End Sub

Sub CheckRegainTELS()
    ' Check that all TELS students have a value in RegainTELS
    Dim intCounter As Integer
    
    Worksheets("Enrollment").Select
    For intCounter = 2 To (intNumberOfRecords + 1)
        ' Check to see if there is an InTELS date - if so, check for RegainTELS
        If Range(strInTELS & intCounter).Value <> "" Then
            If Range(strRegainTELS & intCounter).Value = "" Then
                Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Missing RegainTELS. "
            Else
                If Range(strRegainTELS & intCounter).Value <> "Y" And Range(strRegainTELS & intCounter).Value <> "N" Then
                    Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Invalid RegainTELS. "
                End If
            End If
        End If
    Next intCounter

End Sub

Sub CheckRepeatTELS()
   ' Check that all TELS students have a value in RepeatTELS
    Dim intCounter As Integer
    
    Worksheets("Enrollment").Select
    For intCounter = 2 To (intNumberOfRecords + 1)
        ' Check to see if there is an InTELS date - if so, check for RepeatTELS
        If Range(strInTELS & intCounter).Value <> "" Then
            If Range(strRepeatTELS & intCounter).Value = "" Then
                Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Missing RepeatTELS. "
            Else
                If Range(strRepeatTELS & intCounter).Value <> "Y" And Range(strRepeatTELS & intCounter).Value <> "N" Then
                    Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Invalid RepeatTELS. "
                End If
            End If
        End If
    Next intCounter
End Sub

Sub CheckUNWTGPA()
    ' Check that all TELS students have a value in UNWTGPA
    Dim intCounter As Integer
    
    Worksheets("Enrollment").Select
    For intCounter = 2 To (intNumberOfRecords + 1)
        ' Check to see if there is an InTELS date - if so, check for UNWTGPA
        If Range(strInTELS & intCounter).Value <> "" Then
            If Range(strUNWTGPA & intCounter).Value = "" Then
                Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Missing UNWTGPA. "
            End If
        End If
    Next intCounter
    
    ' If a student has an UNWTGPA and it is below 2.0, flag.
    For intCounter = 2 To (intNumberOfRecords + 1)
        ' Check to see if there is an InTELS date - if so, check UNWTGPA
        If Range(strInTELS & intCounter).Value <> "" Then
                   If Range(strUNWTGPA & intCounter).Value <> "" Then
                        If Range(strUNWTGPA & intCounter).Value < 2 Then
                            Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Suspect UNWTGPA (below 2.0)- please verify. "
                        End If
                   End If
                End If
    Next intCounter

    
End Sub
Sub CheckNUMAP()
    ' Check that all TELS students have a value in NUMAP
    Dim intCounter As Integer
    
    Worksheets("Enrollment").Select
    For intCounter = 2 To (intNumberOfRecords + 1)
        ' Check to see if there is an InTELS date - if so, check for NUMAP
        If Range(strInTELS & intCounter).Value <> "" Then
            If Range(strNUMAP & intCounter).Value = "" Then
                Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Missing NUMAP. "
            End If
        End If
    Next intCounter
End Sub

Sub CheckHasACTorSAT()
    ' Check that all TELS students have either an SAT or ACT score listed
    Dim intCounter As Integer
    
    Worksheets("Enrollment").Select
    For intCounter = 2 To (intNumberOfRecords + 1)
        ' Check to see if there is an InTELS date - if so, check for ACT/SAT
        If Range(strInTELS & intCounter).Value <> "" Then
            If Range(strACT & intCounter).Value = "" And Range(strSAT & intCounter).Value = "" Then
                Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "TELS students must have either an SAT or ACT score. "
            End If
        End If
    Next intCounter
End Sub

Sub CheckACT()
    ' Check that all TELS students that have an ACT score listed have a valid value of 0 to 36
    Dim intCounter As Integer
    
    Worksheets("Enrollment").Select
    For intCounter = 2 To (intNumberOfRecords + 1)
        ' Check to see if there is an InTELS date - if so, check ACT
        If Range(strInTELS & intCounter).Value <> "" Then
            If Range(strACT & intCounter).Value <> "" Then
                If Range(strACT & intCounter).Value > 36 Or Range(strACT & intCounter).Value <= 0 Then
                    Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Invalid ACT score. "
                End If
            End If
        End If
    Next intCounter
End Sub

Sub CheckSAT()
    ' Check that all TELS students that have a SAT score listed have a valid value of 0 to 2400
    Dim intCounter As Integer
    
    Worksheets("Enrollment").Select
    For intCounter = 2 To (intNumberOfRecords + 1)
        ' Check to see if there is an InTELS date - if so, check SAT
        If Range(strInTELS & intCounter).Value <> "" Then
            If Range(strSAT & intCounter).Value <> "" Then
                If Range(strSAT & intCounter).Value > 2400 Or Range(strSAT & intCounter).Value <= 0 Then
                    Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Invalid SAT score. "
                End If
            End If
        End If
    Next intCounter
    
    ' If there is an SAT score, and it is below 275
    For intCounter = 2 To (intNumberOfRecords + 1)
            If Range(strInTELS & intCounter).Value <> "" Then
            If Range(strSAT & intCounter).Value <> "" Then
                If Range(strSAT & intCounter).Value < 250 Then
                    Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Suspect SAT score - please verify. "
                End If
            End If
        End If
    Next intCounter
    
End Sub

Sub CheckFEDAID()
    ' Check that all students have a FEDAID value
    Dim intCounter As Integer

    Worksheets("Enrollment").Select
    For intCounter = 2 To (intNumberOfRecords + 1)
        If Range(strFEDAID & intCounter).Value = "" Then
            Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Missing FEDAID amount. "
        Else
        
        
        End If
    Next intCounter

End Sub


Sub CheckPELL()
    ' Check to see that the student has a PELL grant value and that it is no more than the semester max
    ' of $2300
    Dim intCounter As Integer
    
    Worksheets("Enrollment").Select
    For intCounter = 2 To (intNumberOfRecords + 1)
            If Range(strPELL & intCounter).Value <> "" Then
                If Range(strPELL & intCounter).Value > 2300 Then
                    Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "PELL grant amount too high (Max $2155/semester). "
                End If
                If Range(strPELL & intCounter).Value < 0 Then
                    Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Invalid PELL grant amount. "
                End If
            Else
                Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Missing PELL grant amount. "
            End If
    Next intCounter
End Sub


Sub CheckSTFSUB()
    ' Check that all students have a STFSUB amount listed
    Dim intCounter As Integer
    
    Worksheets("Enrollment").Select
    For intCounter = 2 To (intNumberOfRecords + 1)
        If Range(strSTFSUB & intCounter).Value = "" Then
            Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Missing STFSUB amount. "
        End If
    Next intCounter
End Sub

Sub CheckSTFUSB()
    ' Check that all students have a STFUSB amount listed
    Dim intCounter As Integer
    
    Worksheets("Enrollment").Select
    For intCounter = 2 To (intNumberOfRecords + 1)
        If Range(strSTFUSB & intCounter).Value = "" Then
            Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Missing STFUSB amount. "
        End If
    Next intCounter
End Sub

Sub CheckSTAAID()
    ' Check that all students have a STAAID amount listed
    Dim intCounter As Integer
    
    Worksheets("Enrollment").Select
    For intCounter = 2 To (intNumberOfRecords + 1)
        If Range(strSTAAID & intCounter).Value = "" Then
            Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Missing STAAID amount. "
        End If
    Next intCounter
End Sub

Sub CheckTSAAG()
    ' Check that all students have a TSAAG amount listed
    Dim intCounter As Integer
    
    Worksheets("Enrollment").Select
    For intCounter = 2 To (intNumberOfRecords + 1)
        If Range(strTSAAG & intCounter).Value = "" Then
            Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Missing TSAAG amount. "
        End If
    Next intCounter
End Sub

Sub CheckHOPEGR()
    ' Check to see that all students have a HOPEGR amount listed and that it is no more than $1375/semester and that it is a valid amount
    Dim intCounter As Integer
    
    Worksheets("Enrollment").Select
    For intCounter = 2 To (intNumberOfRecords + 1)
            If Range(strHOPEGR & intCounter).Value <> "" Then
                If Range(strHOPEGR & intCounter).Value > 1375 Then
                    Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "HOPEGR amount too high (Max $1375/semester). "
                End If
                If Range(strHOPEGR & intCounter).Value < 0 Then
                    Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Invalid HOPEGR amount (Less than $0). "
                End If
                ' Check to see if the school enrollment being checked is one of our 2-year schools
                If Right(Range(strFICECODE & intCounter).Value, 4) = 8859 Or Right(Range(strFICECODE & intCounter).Value, 4) = 3494 Then
                ' Check to see that HOPEGR is one of the valid prorated award amounts for 2-year institutions - $875, $656.25, $437.50
                    If Range(strHOPEGR & intCounter).Value <> 875 And Range(strHOPEGR & intCounter).Value <> 656.25 And Range(strHOPEGR & intCounter).Value <> 437.50 And Range(strHOPEGR & intCounter).Value <> 0 Then
                        Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Invalid HOPEGR amount for a 2-year institution. "
                    End If
                Else
                ' If the school is not a 2-year, must be one of the 4-year schools
                ' Check to see that HOPEGR is one of the valid prorated award amounts for 4-year schools - $1,375, $1031.25, $687.50
                 If Range(strHOPEGR & intCounter).Value <> 1375 And Range(strHOPEGR & intCounter).Value <> 1031.25 And Range(strHOPEGR & intCounter).Value <> 687.50 And Range(strHOPEGR & intCounter).Value <> 0 Then
                        Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Invalid HOPEGR amount for a 4-year institution. "
                 End If
                
                End If
            Else
                Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Missing HOPEGR amount. "
            End If
    Next intCounter
End Sub

Sub CheckHOPESC()
    ' Check to see that all students has a HOPESC amount listed and that it is no more than $2000/semester and is a valid amount
   Dim intCounter As Integer
    
    Worksheets("Enrollment").Select
    For intCounter = 2 To (intNumberOfRecords + 1)
            If Range(strHOPESC & intCounter).Value <> "" Then
                If Range(strHOPESC & intCounter).Value > 2000 Then
                    Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "HOPESC amount too high (Max $2000/semester). "
                End If
                If Range(strHOPESC & intCounter).Value < 0 Then
                    Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Invalid HOPESC amount (Less than $0). "
                End If
                ' Check to see if the school enrollment being checked is one of our 2-year schools
                If Right(Range(strFICECODE & intCounter).Value, 4) = 8859 Or Right(Range(strFICECODE & intCounter).Value, 4) = 3494 Then
                ' Check to see that HOPESC is one of the valid prorated award amounts for 2-year institutions - $1000, $750, $500
                    If Range(strHOPESC & intCounter).Value <> 1000 And Range(strHOPESC & intCounter).Value <> 750 And Range(strHOPESC & intCounter).Value <> 500 And Range(strHOPESC & intCounter).Value <> 0 Then
                        Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Invalid HOPESC amount for a 2-year institution. "
                    End If
                Else
                ' If the school is not a 2-year, must be one of the 4-year schools
                ' Check to see that HOPESC is one of the valid prorated award amounts for 4-year schools - $2,000, $1,500, or $1000
                 If Range(strHOPESC & intCounter).Value <> 2000 And Range(strHOPESC & intCounter).Value <> 1500 And Range(strHOPESC & intCounter).Value <> 1000 And Range(strHOPESC & intCounter).Value <> 0 Then
                        Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Invalid HOPESC amount for a 4-year institution. "
                 End If
                End If
            Else
                Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Missing HOPESC amount. "
            End If
    Next intCounter
End Sub

Sub CheckHOPESU()
    ' Check that all students have a HOPESU award amount (Aspire Award) and that it is no more than the max of $750/semester and it is a valid amount
    Dim intCounter As Integer
    
    Worksheets("Enrollment").Select
    For intCounter = 2 To (intNumberOfRecords + 1)
            If Range(strHOPESU & intCounter).Value <> "" Then
                If Range(strHOPESU & intCounter).Value > 750 Then
                    Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "HOPESU amount too high (Max $750/semester). "
                End If
                If Range(strHOPESU & intCounter).Value < 0 Then
                    Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Invalid HOPESU amount. "
                End If
                ' Check to see that HOPESU is one of the valid prorated award amounts - $750, $563, $375 (amounts are same for 2- and 4-year colleges)
                If Range(strHOPESU & intCounter).Value <> 750 And Range(strHOPESU & intCounter).Value <> 563 And Range(strHOPESU & intCounter).Value <> 562 And Range(strHOPESU & intCounter).Value <> 375 And Range(strHOPESU & intCounter).Value <> 0 Then
                    Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Invalid HOPESU amount. "
                End If
            Else
                Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Missing HOPESU amount. "
            End If
    Next intCounter

End Sub

Sub CheckGMERIT()
    'Check to see all students have a GMERIT value and that it is no greater than $500/semester
    Dim intCounter As Integer
    
    Worksheets("Enrollment").Select
    For intCounter = 2 To (intNumberOfRecords + 1)
        If Range(strGMERIT & intCounter).Value <> "" Then
                If Range(strGMERIT & intCounter).Value > 500 Then
                    Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "GMERIT grant amount too high (Max $500/semester). "
                End If
                If Range(strGMERIT & intCounter).Value < 0 Then
                    Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Invalid GMERIT grant amount (Less than $0). "
                End If
                ' Check to see that GMERIT is one of the valid prorated award amounts - $500, $375, or $250 (amounts are same for 2- and 4-year colleges)
                If Range(strGMERIT & intCounter).Value <> 500 And Range(strGMERIT & intCounter).Value <> 375 And Range(strGMERIT & intCounter).Value <> 250 And Range(strGMERIT & intCounter).Value <> 0 Then
                    Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Invalid GMERIT amount. "
                End If
        Else
            Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Missing GMERIT amount. "
        End If
    Next intCounter

End Sub

Sub CheckHOPEDU()
    ' Check that the student has a HOPEDU amount 
    Dim intCounter As Integer
    
    Worksheets("Enrollment").Select
    For intCounter = 2 To (intNumberOfRecords + 1)
        If Range(strHOPEDU & intCounter).Value = "" Then
            Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Missing HOPEDU amount. "
        End If
    Next intCounter
End Sub

Sub CheckHOPEFO()
    ' Check that the student has a HOPEFO amount - amount of award varies
    Dim intCounter As Integer
    
    Worksheets("Enrollment").Select
    For intCounter = 2 To (intNumberOfRecords + 1)
        If Range(strHOPEFO & intCounter).Value = "" Then
            Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Missing HOPEFO amount. "
        End If
    Next intCounter
End Sub

Sub CheckINSAID()
' Check that the student has an INSAID amount
    Dim intCounter As Integer
    
    Worksheets("Enrollment").Select
    For intCounter = 2 To (intNumberOfRecords + 1)
        If Range(strINSAID & intCounter).Value = "" Then
            Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Missing INSAID amount. "
        End If
    Next intCounter
End Sub

Sub CheckPCAID()
    ' Check that the student has a PCAID amount
    Dim intCounter As Integer
    
    Worksheets("Enrollment").Select
    For intCounter = 2 To (intNumberOfRecords + 1)
        If Range(strPCAID & intCounter).Value = "" Then
            Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Missing PCAID amount. "
        End If
    Next intCounter
End Sub

Sub CheckTOTLOA()
    ' Check that student has a TOTLOA amount
    Dim intCounter As Integer
    
    Worksheets("Enrollment").Select
    For intCounter = 2 To (intNumberOfRecords + 1)
        If Range(strTOTLOA & intCounter).Value = "" Then
            Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Missing TOTLOA amount. "
        End If
    Next intCounter
End Sub

Sub FormatWorksheet()
' Select all cells and set their values to uppercase except for the TERM columns



' With all cells, left justify, no fill color, no italic, Arial 10, autofit column width
    Cells.Select
    With Selection
        .Interior.ColorIndex = xlNone
        .Borders(xlDiagonalDown).LineStyle = xlNone
        .Borders(xlDiagonalUp).LineStyle = xlNone
        .Borders(xlEdgeLeft).LineStyle = xlNone
        .Borders(xlEdgeTop).LineStyle = xlNone
        .Borders(xlEdgeBottom).LineStyle = xlNone
        .Borders(xlEdgeRight).LineStyle = xlNone
        .Borders(xlInsideVertical).LineStyle = xlNone
        .Borders(xlInsideHorizontal).LineStyle = xlNone
    End With
    With Selection
        .HorizontalAlignment = xlLeft
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection.Font
        .Name = "Arial"
        .Size = 10
        .Italic = False
    End With
    Cells.EntireColumn.AutoFit
End Sub

Sub FormatColumns()
    ' Remove all dashes from Social Security numbers so that the numeric checks will run correctly
    Columns(strSTDTNU & ":" & strSTDTNU).Select
    Selection.Replace What:="-", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
    ' Format the columns correctly in case they've been changed
    Columns(strGPAHME & ":" & strGPATELS).Select
    Selection.NumberFormat = "0.00"
    Columns(strHSGPA).Select
    Selection.NumberFormat = "0.00"
    Columns(strPZIP).Select
    Selection.NumberFormat = "00000"
    Columns(strMAJOR1).Select
    Selection.NumberFormat = "000000"
    Columns(strHSCODE).Select
    Selection.NumberFormat = "000000"
    Columns(strTFICE).Select
    Selection.NumberFormat = "000000"
    Columns(strPCOUNTY).Select
    Selection.NumberFormat = "000"
    Columns(strFEDAID & ":" & strTOTLOA).Select
    Selection.NumberFormat = "0.00"
    Columns(strSTDTNU).Select
    Selection.NumberFormat = "000-00-0000"
End Sub

Sub CheckRESIDTCounties()
    ' If a student is listed as in-state in respect to the lottery, we need the county of residence (for border states)
    Dim intCounter As Integer
    Worksheets("Enrollment").Select
    For intCounter = 2 To (intNumberOfRecords + 1)
        If Range(strRESIDT & intCounter).Value = "I" And Range(strPCOUNTY & intCounter).Value = "" Then
            Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "If a student is RESIDT = I, PCOUNTY must not be blank. "
        End If
    Next intCounter
End Sub

Sub SetColumnVariables()
 strERROR = "A"
 strFICECODE = "B"
 strINTNUM = "C"
 strSTDTNU = "D"
 strTERM = "E"
 strYear = "F"
 strRACE = "G"
 strGENDER = "H"
 strCITZEN = "I"
 strRESIDT = "J"
 strBIRTHD = "K"
 strPZIP = "L"
 strPCOUNTY = "M"
 strPSTATE = "N"
 strPCNTRY = "O"
 strSLEVEL = "P"
 strREGTYPE = "Q"
 strFirstTR = "R"
 strTFICE = "S"
 strMAJOR1 = "T"
 strATMHME = "U"
 strATMTELS = "V"
 strERNHME = "W"
 strCURHRS = "X"
 strRemDevHrs = "Y"
 strGPAHME = "Z"
 strGPATELS = "AA"
 strInTELS = "AB"
 strLostTELS = "AD"
 strInTELSRegain = "AC"
 strRegainTELS = "AE"
 strRepeatTELS = "AF"
 strHSCODE = "AG"
 strGRADTYPE = "AH"
 strGRADDATE = "AI"
 strHSGPA = "AJ"
 strNUMAP = "AK"
 strACT = "AL"
 strSAT = "AM"
 strFEDAID = "AN"
 strPELL = "AO"
 strSTFSUB = "AP"
 strSTFUSB = "AQ"
 strSTAAID = "AR"
 strTSAAG = "AS"
 strHOPEGR = "AT"
 strHOPESC = "AU"
 strHOPESU = "AV"
 strGMERIT = "AW"
 strHOPEDU = "AX"
 strHOPEFO = "AY"
 strINSAID = "AZ"
 strPCAID = "BA"
 strTOTLOA = "BB"
End Sub

Sub CheckEnrollment()
    ' Initialize variable to contain number of student records
    SetColumnVariables
    intNumberOfRecords = 0
    DetermineintNumberOfRecords
    TrimSpaces
    InsertErrorColumn
    CheckColumnNames
    ' Format Worksheet for ease of visual inspection
    FormatWorksheet
    FormatColumns
    StripZipPlus4s
    CheckSTDTNUandINTNU
    CheckSTDTNULowAndHigh
    CheckFICECODE
    CheckTERM
    CheckYEAR
    CheckRACE
    CheckGENDER
    CheckCITZEN
    CheckRESIDT
    CheckBirthDT
    CheckPZIP
    CheckPZIPRanges
    CheckPSTATE
    CheckPCOUNTY
    CheckPCNTRY
    CheckSLEVEL
    CheckFirstTR
    CheckFirstTRandTFICE
    CheckTFICE
    CheckMAJOR1
    CheckREGTYPE
    CheckATMHME
    CheckATMTELS
    CheckERNHME
    CheckCURHRS
    CheckRemDevHrs
    CheckGPAHME
    CheckGPATELS
    CheckInTELS
    CheckLostTELS
    CheckHSCODE
    CheckGRADTYPE
    CheckGRADDATE
    CheckHSGPA
    CheckNUMAP
    CheckHasACTorSAT
    CheckACT
    CheckSAT
    CheckFEDAID
    CheckPELL
    CheckSTFSUB
    CheckSTFUSB
    CheckSTAAID
    CheckTSAAG
    CheckHOPEGR
    CheckRegainTELS
    CheckRepeatTELS
    CheckInTELSRegain
    CheckHOPESC
    CheckHOPESU
    CheckHOPEDU
    CheckHOPEFO
    CheckGMERIT
    CheckINSAID
    CheckPCAID
    CheckTOTLOA
    CheckRACE1s
    CheckCITZENNo
    CheckFinAidSums
    CheckRESIDTCounties
    CheckHOPEGRNoHOPESC
    MsgBox "Record Checking Completed.", vbOKOnly, "Checking Complete"
End Sub

Sub DetermineintNumberOfRecords()
    ' Determine number of student enrollment records by evaluating several columns
    ' If all of the columns are not blank, assume the column contains a student record
    
    ' Flag to determine when to quit looping
    Dim blnFlag As Boolean
    
    blnFlag = True
    intNumberOfRecords = 2
    
    Do Until blnFlag = False
        If Range(strERROR & intNumberOfRecords).Value <> "" Or Range("B" & intNumberOfRecords).Value <> "" Or Range("C" & intNumberOfRecords).Value <> "" Or Range("D" & intNumberOfRecords).Value <> "" Or Range("E" & intNumberOfRecords).Value <> "" Then
            intNumberOfRecords = intNumberOfRecords + 1
        Else
            blnFlag = False
        End If
               
    Loop
    ' Subtract 2 to get the correct number of records, minus the heading row
    intNumberOfRecords = intNumberOfRecords - 2
End Sub

Sub InsertErrorColumn()

    ' Inserts a column titled Errors at the beginning of the Enrollment worksheet for recording errors
    ' Does not insert if a column titled Errors already exists
    Worksheets("Enrollment").Select
    If Range("A1").Value <> "Questions" Then
        Range("A1").Select
        Selection.EntireColumn.Insert
        Range("A1").Value = "Questions"
    End If
End Sub

Sub CheckSTDTNUandINTNU()
    ' For each student record, checks to see if record has at least a STDTNU or a INTNU
    ' If both are blank, records error

    Dim intCounter As Integer
    
    Worksheets("Enrollment").Select
    For intCounter = 2 To (intNumberOfRecords + 1)
        If Range(strSTDTNU & intCounter).Value = "" And Range(strINTNUM & intCounter).Value = "" Then
            Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Missing both STDTNU and INTNUM. "
    End If
    Next intCounter
        
End Sub

Sub RemoveApostrophes()
' Remove any cell that might have a leading apostrophe (number stored as text)
Dim MyCell As Range
Cells.Select

For Each MyCell In Selection.Cells
    If MyCell.PrefixCharacter = "'" Then
        MyCell.NumberFormat = "General"
        MyCell.Value = MyCell.Value
    End If
Next

End Sub

Sub StripZipPlus4s()
    ' If a student has a ZIP+4, drop the "-" and the last 4 digits
    ' Be careful - if the zips have anything in front of them, like an apostrophe, this will eat the end of the zip code
    Dim intCounter As Integer
    Worksheets("Enrollment").Select
    For intCounter = 2 To (intNumberOfRecords + 1)
        If Len(Range(strPZIP & intCounter)) > 5 Then
                Range(strPZIP & intCounter).Value = Left(Range(strPZIP & intCounter), 5)
        End If
    Next intCounter
End Sub

Sub CheckFinAidSums()
    ' Check that the financial aid columns that contain the values of other columns (ie: FEDAID contains PELL, STFSUB, STFUSB at a minimum)
    ' contain the minimum values
    Dim intCounter As Integer
    Dim curFEDAIDCheck As Currency
    Dim curTOTLOACheck As Currency
    Dim curSTAAIDCheck As Currency
    Dim curFEDAID As Currency
    Dim curSTFSUB As Currency
    Dim curSTFUSB As Currency
    Dim curPELL As Currency
    Dim curSTAAID As Currency
    Dim curTOTLOA As Currency
    Dim curPCAID As Currency
    Dim curHOPEFO As Currency
    Dim curHOPEDU As Currency
    Dim curGMERIT As Currency
    Dim curHOPESC As Currency
    Dim curHOPESU As Currency
    Dim curHOPEGR As Currency
    Dim curTSAAG As Currency
    Dim curINSAID As Currency
    
    Worksheets("Enrollment").Select
    For intCounter = 2 To (intNumberOfRecords + 1)
    ' First, fill proper variables
    curFEDAID = Val(Range(strFEDAID & intCounter))
    curPELL = Val(Range(strPELL & intCounter))
    curSTFSUB = Val(Range(strSTFSUB & intCounter))
    curSTFUSB = Val(Range(strSTFUSB & intCounter))
    curSTAAID = Val(Range(strSTAAID & intCounter))
    curTSAAG = Val(Range(strSTAAID & intCounter))
    curHOPEGR = Val(Range(strHOPEGR & intCounter))
    curHOPESC = Val(Range(strHOPESC & intCounter))
    curHOPESU = Val(Range(strHOPESU & intCounter))
    curGMERIT = Val(Range(strGMERIT & intCounter))
    curHOPEDU = Val(Range(strHOPEDU & intCounter))
    curHOPEFO = Val(Range(strHOPEFO & intCounter))
    curINSAID = Val(Range(strINSAID & intCounter))
    curPCAID = Val(Range(strPCAID & intCounter))
    curTOTLOA = Val(Range(strTOTLOA & intCounter))
    ' Calculate sums to check
    curFEDAIDCheck = Val(Range(strPELL & intCounter)) + Val(Range(strSTFSUB & intCounter)) + Val(Range(strSTFUSB & intCounter))
    curSTAAIDCheck = Val(Range(strTSAAG & intCounter)) + Val(Range(strHOPEGR & intCounter)) + Val(Range(strHOPESC & intCounter)) + Val(Range(strHOPESU & intCounter)) + Val(Range(strGMERIT & intCounter)) + Val(Range(strHOPEDU & intCounter)) + Val(Range(strHOPEFO & intCounter))
    curTOTLOACheck = Val(Range(strSTFSUB & intCounter)) + Val(Range(strSTFUSB & intCounter))
    ' Check that FEDAID is not less than the sum of STFSUB, PELL, and STFUSB
    If curFEDAIDCheck > curFEDAID Then
        Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "FEDAID is not equal to or greater than the sum of STFSUB, STFUSB and PELL. "
    End If
    ' Check that STAAID is not less than the sum of its minimum components
    If curSTAAIDCheck > curSTAAID Then
        Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "STAAID is not equal to or greater than the sum of TSAAG, HOPEGR, HOPESC, HOPESU, GMERIT, HOPEDU, and HOPEFO. "
    End If
    ' Check that TOTLOA is not less than the sum of its minimum components
    If curTOTLOACheck > curTOTLOA Then
        Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "TOTLOA is not equal to or greater than the sum of STFSUB and STFUSB. "
    End If
    Next intCounter
    
End Sub

Sub CheckHOPEGRNoHOPESC()
' Check that any students recieving a HOPE Access Grant are not also recieving a HOPE Base Award
    Dim intCounter As Integer
    
    Worksheets("Enrollment").Select
    For intCounter = 2 To (intNumberOfRecords + 1)
            If Range(strHOPEGR & intCounter).Value > 0 Then
                If Range(strHOPESC & intCounter).Value > 0 Then
                    Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Student cannot have both a HOPE Access Grant and a HOPE Base award. "
                End If
            End If
    Next intCounter
End Sub

Sub CheckPZIPRanges()
' Check that any TN PZIP reported matches the PSTATE reported along with it
    Dim intCounter As Integer
    Worksheets("Enrollment").Select
    For intCounter = 2 To (intNumberOfRecords + 1)
        If Range(strPSTATE & intCounter).Value <> "" And IsNumeric(Range(strPZIP & intCounter).Value) Then
            If Range(strPSTATE & intCounter).Value = "TN" And (Range(strPZIP & intCounter).Value < 37010 Or Range(strPZIP & intCounter).Value > 38599) Then
            Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Reported PZIP does not match PSTATE. "
            End If
        End If
    Next intCounter
    
End Sub

Sub CheckColumnNames()
' Check that the column names agree with the template
    Dim intCounter As Integer
    Dim strColumnHeadings() As Variant
    Dim intErrorCatch As Integer
    
    intErrorCatch = 0
    
    strColumnHeadings = Array("FICECODE", "INTNUM", "STDTNU", "TERM", "YEAR", "RACE", "GENDER", "CITZEN", "RESIDT", "BIRTHD", "PZIP", "PCOUNTY", "PSTATE", "PCNTRY", "SLEVEL", "REGTYPE", "FirstTR", "TFICE", "MAJOR1", "ATMHME", "ATMTELS", "ERNHME", "CURHRS", "RemDevHrs", "GPAHME", "GPATELS", "InTELS", "InTELSRegain", "LostTELS", "RegainTELS", "RepeatTELS", "HSCODE", "GRADTYPE", "GRADDATE", "HSGPA", "NUMAP", "ACT", "SAT", "FEDAID", "PELL", "STFSUB", "STFUSB", "STAAID", "TSAAG", "HOPEGR", "HOPESC", "HOPESU", "GMERIT", "HOPEDU", "HOPEFO", "INSAID", "PCAID", "TOTLOA")
    If Range(strFICECODE & "1").Value <> strColumnHeadings(0) Then
         Range(strERROR & "2").Value = Range(strERROR & "2").Value & "Column heading mismatch in column " & strFICECODE & ". "
         intErrorCatch = 1
    End If
    If Range(strINTNUM & "1").Value <> strColumnHeadings(1) Then
         Range(strERROR & "2").Value = Range(strERROR & "2").Value & "Column heading mismatch in column " & strINTNUM & ". "
         intErrorCatch = 1
    End If
    If Range(strSTDTNU & "1").Value <> strColumnHeadings(2) Then
         Range(strERROR & "2").Value = Range(strERROR & "2").Value & "Column heading mismatch in column " & strSTDTNU & ". "
         intErrorCatch = 1
    End If
    If Range(strTERM & "1").Value <> strColumnHeadings(3) Then
         Range(strERROR & "2").Value = Range(strERROR & "2").Value & "Column heading mismatch in column " & strTERM & ". "
         intErrorCatch = 1
    End If
    If Range(strYear & "1").Value <> strColumnHeadings(4) Then
         Range(strERROR & "2").Value = Range(strERROR & "2").Value & "Column heading mismatch in column " & strYear & ". "
         intErrorCatch = 1
    End If
    If Range(strRACE & "1").Value <> strColumnHeadings(5) Then
         Range(strERROR & "2").Value = Range(strERROR & "2").Value & "Column heading mismatch in column " & strRACE & ". "
         intErrorCatch = 1
    End If
    If Range(strGENDER & "1").Value <> strColumnHeadings(6) Then
         Range(strERROR & "2").Value = Range(strERROR & "2").Value & "Column heading mismatch in column " & strGENDER & ". "
         intErrorCatch = 1
    End If
    If Range(strCITZEN & "1").Value <> strColumnHeadings(7) Then
         Range(strERROR & "2").Value = Range(strERROR & "2").Value & "Column heading mismatch in column " & strCITZEN & ". "
         intErrorCatch = 1
    End If
    If Range(strRESIDT & "1").Value <> strColumnHeadings(8) Then
         Range(strERROR & "2").Value = Range(strERROR & "2").Value & "Column heading mismatch in column " & strRESIDT & ". "
         intErrorCatch = 1
    End If
    If Range(strBIRTHD & "1").Value <> strColumnHeadings(9) Then
         Range(strERROR & "2").Value = Range(strERROR & "2").Value & "Column heading mismatch in column " & strBIRTHD & ". "
         intErrorCatch = 1
    End If
    If Range(strPZIP & "1").Value <> strColumnHeadings(10) Then
         Range(strERROR & "2").Value = Range(strERROR & "2").Value & "Column heading mismatch in column " & strPZIP & ". "
         intErrorCatch = 1
    End If
    If Range(strPCOUNTY & "1").Value <> strColumnHeadings(11) Then
         Range(strERROR & "2").Value = Range(strERROR & "2").Value & "Column heading mismatch in column " & strPCOUNTY & ". "
         intErrorCatch = 1
    End If
    If Range(strPSTATE & "1").Value <> strColumnHeadings(12) Then
         Range(strERROR & "2").Value = Range(strERROR & "2").Value & "Column heading mismatch in column " & strPSTATE & ". "
         intErrorCatch = 1
    End If
    If Range(strPCNTRY & "1").Value <> strColumnHeadings(13) Then
         Range(strERROR & "2").Value = Range(strERROR & "2").Value & "Column heading mismatch in column " & strPCNTRY & ". "
         intErrorCatch = 1
    End If
    If Range(strSLEVEL & "1").Value <> strColumnHeadings(14) Then
         Range(strERROR & "2").Value = Range(strERROR & "2").Value & "Column heading mismatch in column " & strSLEVEL & ". "
         intErrorCatch = 1
    End If
    If Range(strREGTYPE & "1").Value <> strColumnHeadings(15) Then
         Range(strERROR & "2").Value = Range(strERROR & "2").Value & "Column heading mismatch in column " & strREGTYPE & ". "
         intErrorCatch = 1
    End If
    If Range(strFirstTR & "1").Value <> strColumnHeadings(16) Then
         Range(strERROR & "2").Value = Range(strERROR & "2").Value & "Column heading mismatch in column " & strFirstTR & ". "
         intErrorCatch = 1
    End If
    If Range(strTFICE & "1").Value <> strColumnHeadings(17) Then
         Range(strERROR & "2").Value = Range(strERROR & "2").Value & "Column heading mismatch in column " & strTFICE & ". "
         intErrorCatch = 1
    End If
    If Range(strMAJOR1 & "1").Value <> strColumnHeadings(18) Then
         Range(strERROR & "2").Value = Range(strERROR & "2").Value & "Column heading mismatch in column " & strMAJOR1 & ". "
         intErrorCatch = 1
    End If
    If Range(strATMHME & "1").Value <> strColumnHeadings(19) Then
         Range(strERROR & "2").Value = Range(strERROR & "2").Value & "Column heading mismatch in column " & strATMHME & ". "
         intErrorCatch = 1
    End If
    If Range(strATMTELS & "1").Value <> strColumnHeadings(20) Then
         Range(strERROR & "2").Value = Range(strERROR & "2").Value & "Column heading mismatch in column " & strATMTELS & ". "
         intErrorCatch = 1
    End If
    If Range(strERNHME & "1").Value <> strColumnHeadings(21) Then
         Range(strERROR & "2").Value = Range(strERROR & "2").Value & "Column heading mismatch in column " & strATMHME & ". "
         intErrorCatch = 1
    End If
    If Range(strCURHRS & "1").Value <> strColumnHeadings(22) Then
         Range(strERROR & "2").Value = Range(strERROR & "2").Value & "Column heading mismatch in column " & strCURHRS & ". "
         intErrorCatch = 1
    End If
    If Range(strRemDevHrs & "1").Value <> strColumnHeadings(23) Then
         Range(strERROR & "2").Value = Range(strERROR & "2").Value & "Column heading mismatch in column " & strRemDevHrs & ". "
         intErrorCatch = 1
    End If
    If Range(strGPAHME & "1").Value <> strColumnHeadings(24) Then
         Range(strERROR & "2").Value = Range(strERROR & "2").Value & "Column heading mismatch in column " & strGPAHME & ". "
         intErrorCatch = 1
    End If
    If Range(strGPATELS & "1").Value <> strColumnHeadings(25) Then
         Range(strERROR & "2").Value = Range(strERROR & "2").Value & "Column heading mismatch in column " & strGPATELS & ". "
         intErrorCatch = 1
    End If
    If Range(strInTELS & "1").Value <> strColumnHeadings(26) Then
         Range(strERROR & "2").Value = Range(strERROR & "2").Value & "Column heading mismatch in column " & strInTELS & ". "
         intErrorCatch = 1
    End If
    If Range(strLostTELS & "1").Value <> strColumnHeadings(28) Then
         Range(strERROR & "2").Value = Range(strERROR & "2").Value & "Column heading mismatch in column " & strLostTELS & ". "
         intErrorCatch = 1
    End If
    If Range(strInTELSRegain & "1").Value <> strColumnHeadings(27) Then
         Range(strERROR & "2").Value = Range(strERROR & "2").Value & "Column heading mismatch in column " & strInTELSRegain & ". "
         intErrorCatch = 1
    End If
    If Range(strRegainTELS & "1").Value <> strColumnHeadings(29) Then
         Range(strERROR & "2").Value = Range(strERROR & "2").Value & "Column heading mismatch in column " & strRegainTELS & ". "
         intErrorCatch = 1
    End If
    If Range(strRepeatTELS & "1").Value <> strColumnHeadings(30) Then
         Range(strERROR & "2").Value = Range(strERROR & "2").Value & "Column heading mismatch in column " & strRepeatTELS & ". "
         intErrorCatch = 1
    End If
    If Range(strHSCODE & "1").Value <> strColumnHeadings(31) Then
         Range(strERROR & "2").Value = Range(strERROR & "2").Value & "Column heading mismatch in column " & strHSCODE & ". "
         intErrorCatch = 1
    End If
    If Range(strGRADTYPE & "1").Value <> strColumnHeadings(32) Then
         Range(strERROR & "2").Value = Range(strERROR & "2").Value & "Column heading mismatch in column " & strGRADTYPE & ". "
         intErrorCatch = 1
    End If
    If Range(strGRADDATE & "1").Value <> strColumnHeadings(33) Then
         Range(strERROR & "2").Value = Range(strERROR & "2").Value & "Column heading mismatch in column " & strGRADDATE & ". "
         intErrorCatch = 1
    End If
    If Range(strHSGPA & "1").Value <> strColumnHeadings(34) Then
         Range(strERROR & "2").Value = Range(strERROR & "2").Value & "Column heading mismatch in column " & strHSGPA & ". "
         intErrorCatch = 1
    End If
    If Range(strNUMAP & "1").Value <> strColumnHeadings(35) Then
         Range(strERROR & "2").Value = Range(strERROR & "2").Value & "Column heading mismatch in column " & strNUMAP & ". "
         intErrorCatch = 1
    End If
    If Range(strACT & "1").Value <> strColumnHeadings(36) Then
         Range(strERROR & "2").Value = Range(strERROR & "2").Value & "Column heading mismatch in column " & strACT & ". "
         intErrorCatch = 1
    End If
    If Range(strSAT & "1").Value <> strColumnHeadings(37) Then
         Range(strERROR & "2").Value = Range(strERROR & "2").Value & "Column heading mismatch in column " & strSAT & ". "
         intErrorCatch = 1
    End If
    If Range(strFEDAID & "1").Value <> strColumnHeadings(38) Then
         Range(strERROR & "2").Value = Range(strERROR & "2").Value & "Column heading mismatch in column " & strFEDAID & ". "
         intErrorCatch = 1
    End If
    If Range(strPELL & "1").Value <> strColumnHeadings(39) Then
         Range(strERROR & "2").Value = Range(strERROR & "2").Value & "Column heading mismatch in column " & strPELL & ". "
         intErrorCatch = 1
    End If
    If Range(strSTFSUB & "1").Value <> strColumnHeadings(40) Then
         Range(strERROR & "2").Value = Range(strERROR & "2").Value & "Column heading mismatch in column " & strSTFSUB & ". "
         intErrorCatch = 1
    End If
    If Range(strSTFUSB & "1").Value <> strColumnHeadings(41) Then
         Range(strERROR & "2").Value = Range(strERROR & "2").Value & "Column heading mismatch in column " & strSTFUSB & ". "
         intErrorCatch = 1
    End If
    If Range(strSTAAID & "1").Value <> strColumnHeadings(42) Then
         Range(strERROR & "2").Value = Range(strERROR & "2").Value & "Column heading mismatch in column " & strSTAAID & ". "
         intErrorCatch = 1
    End If
    If Range(strTSAAG & "1").Value <> strColumnHeadings(43) Then
         Range(strERROR & "2").Value = Range(strERROR & "2").Value & "Column heading mismatch in column " & strTSAAG & ". "
         intErrorCatch = 1
    End If
    If Range(strHOPEGR & "1").Value <> strColumnHeadings(44) Then
         Range(strERROR & "2").Value = Range(strERROR & "2").Value & "Column heading mismatch in column " & strHOPEGR & ". "
         intErrorCatch = 1
    End If
    If Range(strHOPESC & "1").Value <> strColumnHeadings(45) Then
         Range(strERROR & "2").Value = Range(strERROR & "2").Value & "Column heading mismatch in column " & strHOPESC & ". "
         intErrorCatch = 1
    End If
    If Range(strHOPESU & "1").Value <> strColumnHeadings(46) Then
         Range(strERROR & "2").Value = Range(strERROR & "2").Value & "Column heading mismatch in column " & strHOPESU & ". "
         intErrorCatch = 1
    End If
    If Range(strGMERIT & "1").Value <> strColumnHeadings(47) Then
         Range(strERROR & "2").Value = Range(strERROR & "2").Value & "Column heading mismatch in column " & strGMERIT & ". "
         intErrorCatch = 1
    End If
    If Range(strHOPEDU & "1").Value <> strColumnHeadings(48) Then
         Range(strERROR & "2").Value = Range(strERROR & "2").Value & "Column heading mismatch in column " & strHOPEDU & ". "
         intErrorCatch = 1
    End If
    If Range(strHOPEFO & "1").Value <> strColumnHeadings(49) Then
         Range(strERROR & "2").Value = Range(strERROR & "2").Value & "Column heading mismatch in column " & strHOPEFO & ". "
         intErrorCatch = 1
    End If
    If Range(strINSAID & "1").Value <> strColumnHeadings(50) Then
         Range(strERROR & "2").Value = Range(strERROR & "2").Value & "Column heading mismatch in column " & strINSAID & ". "
         intErrorCatch = 1
    End If
    If Range(strPCAID & "1").Value <> strColumnHeadings(51) Then
         Range(strERROR & "2").Value = Range(strERROR & "2").Value & "Column heading mismatch in column " & strPCAID & ". "
         intErrorCatch = 1
    End If
    If Range(strTOTLOA & "1").Value <> strColumnHeadings(52) Then
         Range(strERROR & "2").Value = Range(strERROR & "2").Value & "Column heading mismatch in column " & strTOTLOA & ". "
         intErrorCatch = 1
    End If
    
    If intErrorCatch = 1 Then
        MsgBox "Column heading mistmatches - aborting!", vbCritical
    End
    End If
    
End Sub

Sub CheckInTELSRegain()
' If a student has a RegainTELS value of 'Y' then check for valid value in InTELSRegain
    Dim intCounter As Integer
    
    Worksheets("Enrollment").Select
    For intCounter = 2 To (intNumberOfRecords + 1)
        ' if student has a value of 'Y' in RegainTELS, check for valid InTELSRegain values
        If Range(strRegainTELS & intCounter).Value = "Y" Then
            If Range(strInTELSRegain & intCounter).Value = "" Then
                Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "Has RegainTELS = Y but no value in InTELSRegain. "
            Else
                If IsDate(Range(strInTELSRegain & intCounter).Value) = False Then
                    Range(strERROR & intCounter).Value = Range(strERROR & intCounter).Value & "InTELSRegain must be a date. "
                End If
            End If
        End If
    Next intCounter

End Sub

Sub TrimSpaces()
' Trim any leading spaces or spaces on the end of cells



End Sub