Excel VBA - 查找范围内的最高值和后续值

问题描述：

我有下面的代码，应该找到范围中的第1,2,3个和第4个最高值。Excel VBA - 查找范围内的最高值和后续值

它目前是非常基本的，我有它提供了一个MsgBox的值，所以我可以确认它正在工作。

但是，它只找到最高值和第二高值。第三个和第四个值返回为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

另一种方法将排序范围，然后拿起你的价值:) –

你真的需要在VBA中做到这一点？ –

答

使用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

+++该死的该死！ –

是的，这很好。谢谢！ – sbagnato

@Jeeped。斯科特是对的。 Excel公式标记不是为此目的:) –

答

你必须通过上述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)

Excel VBA - 查找范围内的最高值和后续值

相关推荐