It would be so cool for a "2nd highest" designation or key in vba, but I can't find one.
If each cell was just a number, I'd write each to a new Sheet, sort and get the value in A2.
But because the cell format is like this 89(SP) I'm not clear how. Of course Excel won't recognize this format as a number.
Code
'OUTPUT
TotalScore(0) = 0
RceNo = 0
MaxScore = 0
For b = 1 To 4
TotalScore(b) = FirstNameScore(b) + LastNameScore(b)
If TotalScore(b) > MaxScore Then
MaxScore = TotalScore(b)
RceNo = b
End If
Next b
If MaxScore = 0 Then
Sheets("Sheet1").Cells(DataRows, 35).Value = "0(AN)"
End If
If RceNo = 1 Then
Sheets("Sheet1").Cells(DataRows, 35).Value = MaxScore & "(AN)"
ElseIf RceNo = 2 Then
Sheets("Sheet1").Cells(DataRows, 35).Value = MaxScore & "(AF)"
ElseIf RceNo = 3 Then
Sheets("Sheet1").Cells(DataRows, 35).Value = MaxScore & "(AP)"
ElseIf RceNo = 4 Then
Sheets("Sheet1").Cells(DataRows, 35).Value = MaxScore & "(SP)"
End If
'delete Scores
For Z = 1 To 4
FirstNameScore(Z) = 0
LastNameScore(Z) = 0
Next Z
Next DataRows
Display More