CorrectRow

'Function that returns the correct row to input data . If valid row isn't
'found based on previous entries, function creates a new row and returns Range
Function CorrectRow(unit As String, lastName As String, _
                    rank As String, testType As String) As Range
    'Declare variables
    Dim ws As Worksheet
    Dim resultsTable As String
    Dim lastRow As Long
    Dim lastColumn As Long
    Dim nameCell As Range

    'Assign correct worksheet to variable based on selected unit
    Select Case unit
        Case "HHB"
            Set ws = Sheets("HHB Table IV Results")
            resultsTable = "HHB_Results"
        Case "A-BTRY"
            Set ws = Sheets("A-BTRY Table IV Results")
            resultsTable = "A_Results"
        Case "B-BTRY"
            Set ws = Sheets("B-BTRY Table IV Results")
            resultsTable = "B_Results"
        Case "C-BTRY"
            Set ws = Sheets("C-BTRY Table IV Results")
            resultsTable = "C_Results"
        Case "D-BTRY"
            Set ws = Sheets("D-BTRY Table IV Results")
            resultsTable = "D_Results"
        Case Else
            End
    End Select
    
    'Select correct worksheet (MUST BE ACTIVE for later subroutine calls)
    ws.Select
    
    'Find last row and column with information
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    lastColumn = Cells(1, Columns.Count).End(xlToLeft).Column

    'Select correct row based on last name, rank and test type
    For Each nameCell In Range("C2:C" & lastRow)
        If lastName = nameCell.Value Then
            If rank = nameCell.Offset(0, -1).Value Then
                If testType = nameCell.Offset(0, 1) Then
                   Set CorrectRow = Range("A" & nameCell.Row & ":" & _
                        ColumnLetter(lastColumn) & nameCell.Row)
                    Exit Function
                End If
            End If
        End If
    Next nameCell
    
    'If previous entry isn't found and there isn't already a blank row, create new row
    If Not Range("A" & lastRow).Value = "" Then
        ws.ListObjects(resultsTable).ListRows.Add AlwaysInsert:=True
        lastRow = lastRow + 1
    End If
    
    'Return new/empty row as range
    Set CorrectRow = Range("A" & lastRow & ":" & _
        ColumnLetter(lastColumn) & lastRow)
End Function