Saturday, April 7, 2018

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

No comments:

Post a Comment

plot explanation - Why did Peaches&#39; mom hang on the tree? - Movies &amp; TV

In the middle of the movie Ice Age: Continental Drift Peaches' mom asked Peaches to go to sleep. Then, she hung on the tree. This parti...