vba 查找范围内的最高值和后续值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/44551470/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Finding highest and subsequent values in a range
提问by sbagnato
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.
它目前非常基础，我让它在 MsgBox 中提供值，以便我可以确认它正在工作。
However, it only finds the highest and second highest values. The third and fourth values are returned back as 0. What am I missing?
但是，它只找到最高值和第二高值。第三个和第四个值返回为 0。我错过了什么？
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
回答by Scott Craner
Use Application.WorksheetFunction.Large():
使用 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
回答by sbagnato
You have a better method suggested by Scott Cranerabove. However, to answer your question, you are only returning a limited number of values because you are overwriting the values without shifting the original values to a lower rank.
您有上面Scott Craner建议的更好方法。但是，为了回答您的问题，您只返回有限数量的值，因为您正在覆盖这些值，而没有将原始值移到较低的等级。
Dim myVALs As Variant
myVALs = Array(0, 0, 0, 0, 0)
For Each cell In rng
Select Case True
Case cell.Value2 > myVALs(0)
myVALs(4) = myVALs(3)
myVALs(3) = myVALs(2)
myVALs(2) = myVALs(1)
myVALs(1) = myVALs(0)
myVALs(0) = cell.Value2
Case cell.Value2 > myVALs(1)
myVALs(4) = myVALs(3)
myVALs(3) = myVALs(2)
myVALs(2) = myVALs(1)
myVALs(1) = cell.Value2
Case cell.Value2 > myVALs(2)
myVALs(4) = myVALs(3)
myVALs(3) = myVALs(2)
myVALs(2) = cell.Value2
Case cell.Value2 > myVALs(3)
myVALs(4) = myVALs(3)
myVALs(3) = cell.Value2
Case cell.Value2 > myVALs(4)
myVALs(4) = cell.Value2
Case Else
'do nothing
End Select
Next cell
Debug.Print "first: " & myVALs(0)
Debug.Print "second: " & myVALs(1)
Debug.Print "third: " & myVALs(2)
Debug.Print "fourth: " & myVALs(3)
Debug.Print "fifth: " & myVALs(4)
