Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
2.1k views
in Technique[技术] by (71.8m points)

excel - Finding highest and subsequent values in a range

I have the below code which is supposed to find the 1st, 2nd, 3rd, and 4th highest values in a range.

It is currently very basic, and I have it providing the values in a MsgBox so I can confirm it is working.

However, it only finds the highest and second highest values. The third and fourth values are returned back as 0. What am I missing?

Sub Macro1()

Dim rng As Range, cell As Range
Dim firstVal As Double, secondVal As Double, thirdVal As Double, fourthVal As Double

Set rng = [C4:C16]

For Each cell In rng
    If cell.Value > firstVal Then firstVal = cell.Value
    If cell.Value > secondVal And cell.Value < firstVal Then secondVal = 
    cell.Value
    If cell.Value > thirdVal And cell.Value < secondVal Then thirdVal = 
    cell.Value
    If cell.Value > fourthVal And cell.Value < thirdVal Then fourthVal = 
    cell.Value
Next cell

MsgBox "First Highest Value is " & firstVal
MsgBox "Second Highest Value is " & secondVal
MsgBox "Third Highest Value is " & thirdVal
MsgBox "Fourth Highest Value is " & fourthVal

End Sub
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

Use Application.WorksheetFunction.Large():

Sub Macro1()

Dim rng As Range, cell As Range
Dim firstVal As Double, secondVal As Double, thirdVal As Double, fourthVal As Double

Set rng = [C4:C16]


firstVal = Application.WorksheetFunction.Large(rng,1)
secondVal = Application.WorksheetFunction.Large(rng,2)        
thirdVal = Application.WorksheetFunction.Large(rng,3)
fourthVal = Application.WorksheetFunction.Large(rng,4)

MsgBox "First Highest Value is " & firstVal
MsgBox "Second Highest Value is " & secondVal
MsgBox "Third Highest Value is " & thirdVal
MsgBox "Fourth Highest Value is " & fourthVal

End Sub

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...