Private Sub CommandButton1_Click()
Dim rng As Range, cell As Range
Dim highestvalue As Double, secondhighestvalue As Double
Set rng = Selection
highestvalue = 0
secondhighestvalue = 0
'find the highest value
For Each cell In rng
If cell.VALUE > highestvalue Then highestvalue = cell.VALUE
Next cell
'find the second highest value
For Each cell In rng
If cell.VALUE > secondhighestvalue And cell.VALUE < highestvalue Then
secondhighestvalue = cell.VALUE
highestvalue = highestvalue + 1
secondhighestvalue = secondhighestvalue + 1
End If
Next cell
MsgBox "second highest value is" & secondhighestvalue
End Sub
Bookmarks