Using Vlookup depending on a Case nested in an IF all reading from an array

[bdmichael09]

Let me start by saying I know that this could be done very easily just using the built in functions in the UI but I am trying to teach myself VB and this is just a practice exercise.

I'm still very new to VBA and, in an attempt to create a project trying to practice using arrays, I seem to have bit off more than I can chew. Essentially what I am trying to do is create code that will allow the user to input raw numbers and output final scores for a mock Army Physical Fitness Test. For anyone unfamiliar, an APFT has 3 events: pushups, situps and a 2 mile run. Based on how many pushups or situps you do or how fast you run you get a particular 0-100 score for each category. Each score combined is the total score.

The first part of my code, which is simply entering in all the raw scores for each "soldier" and having them entered into the table, I got to work flawlessly (but I will not complain if anyone knows a way to more efficiently code it).

Sub dataentry()
    
    Range("a3", Range("i3").End(xlDown)).ClearContents
    Dim soldier() As String, ssn() As String, gender() As String, age() As Integer, pushup() As Integer, situp() As Integer
    Dim twomilemin() As Single, twomilesec() As Single  ' declare my arrays
    Dim max As Integer ' declare variable for my maximum number for the arrays
    Dim i As Integer 'loop counter
    max = InputBox("How many soldiers participated?") 'size of the array
    ReDim soldier(1 To max), ssn(1 To max), gender(1 To max), age(1 To max), pushup(1 To max), situp(1 To max)
    ReDim twomilemin(1 To max), twomilesec(1 To max), twomilerawseconds(1 To max) ' dynamic array sizing
        
    For i = 1 To max 'start of loop to enter all soldier information
    
    soldier(i) = InputBox("enter soldier name")
    ssn(i) = InputBox("enter ssn")
    gender(i) = InputBox("Male(m) or Female(f)?")
    age(i) = InputBox("What is the soldier's age?")
    pushup(i) = InputBox("enter push up raw score")
    situp(i) = InputBox("enter sit up raw score")
    twomilemin(i) = InputBox("How many minutes for the 2 mile")
    twomilesec(i) = InputBox("and how many seconds?")
    Cells(i + 2, 1) = soldier(i) ' soldier name
    Cells(i + 2, 2) = ssn(i) ' entered ssn value
    Cells(i + 2, 3) = gender(i) ' entered gender value
    Cells(i + 2, 4) = age(i) 'entered age value
    Cells(i + 2, 5) = pushup(i) ' entered pushup raw score
    Cells(i + 2, 6) = situp(i) ' entered situp raw score
    Cells(i + 2, 7) = twomilemin(i) ' entered two mile minute total
    Cells(i + 2, 8) = twomilesec(i) ' entered two mile additional seconds total
    Cells(i + 2, 9) = (twomilemin(i) * 60) + (twomilesec(i)) ' calculate total seconds ran
    
            
    Next
    
End Sub

The second half of what I am trying to do I just can't get. What I need my code to do now is to read the data from each of those arrays to output a final score. The catch is that the way an APFT works means that a score for each event is based on both gender and age. This means the code has to A) read if the soldier is male or female, and B) how old are they, before it can then use the raw score to look up the final score for that test category. What made sense to me was to use an IF statement to identify gender, then a Case statement nested inside the IF to identify age, and then use the vlookup function to find the score that corresponds to the raw score previously entered. I can't get it to work though and I've spent hours scouring the internet and forums. I have this set as a different module right now. Below is kind of what I had in mind that could work.

For Each cell In genderrange
    gender = cell.Value
    If gender = "m" Then
            Select Case age
                Case Is >= 57
                    pushupscore = Application.WorksheetFunction.VLookup(pushupraw, pushupchart.Range("A5:U81"), 18, 0)
                Case Is >= 52
                    pushupscore = Application.WorksheetFunction.VLookup(pushupraw, pushupchart.Range("A5:U81"), 16, 0)
                Case Is >= 47
                    pushupscore = Application.WorksheetFunction.VLookup(pushupraw, pushupchart.Range("A5:U81"), 14, 0)
                Case Is >= 42
                    pushupscore = Application.WorksheetFunction.VLookup(pushupraw, pushupchart.Range("A5:U81"), 12, 0)
                Case Is >= 37
                    pushupscore = Application.WorksheetFunction.VLookup(pushupraw, pushupchart.Range("A5:U81"), 10, 0)
                Case Is >= 32
                    pushupscore = Application.WorksheetFunction.VLookup(pushupraw, pushupchart.Range("A5:U81"), 8, 0)
                Case Is >= 27
                    pushupscore = Application.WorksheetFunction.VLookup(pushupraw, pushupchart.Range("A5:U81"), 6, 0)
                Case Is >= 22
                    pushupscore = Application.WorksheetFunction.VLookup(pushupraw, pushupchart.Range("A5:U81"), 4, 0)
                Case Is >= 17
                    pushupscore = Application.WorksheetFunction.VLookup(pushupraw, pushupchart.Range("A5:U81"), 2, 0)
                End Select
                pushupscore = cell.Offset(0, 5)