查找范围内的最高值和后续值 [英] Finding highest and subsequent values in a range

问题描述

我有以下代码，应该可以找到一个范围内的第1，第2，第3和第4个最高值.

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

它目前非常基础，我已经在MsgBox中提供了值，因此我可以确认它是否正常工作.

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

但是，它只能找到最高和第二最高的值.第三个和第四个值返回为0.我缺少什么?

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

推荐答案

使用Application.WorksheetFunction.Large():

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

这篇关于查找范围内的最高值和后续值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！