Ranking using VBA

MrTeeny

Board Regular
Joined
Jul 26, 2017
Messages
231
I'm trying to rank a list of values using VBA rather than a formula as the numbers will be continually changing. I'm basically trying to run the code at a set point to rank the lowest 3 numbers on the sheet and leave them set rather than recalculating with a formula.

Thanks
 

MrTeeny

Board Regular
Joined
Jul 26, 2017
Messages
231
Resorted to using small in the end

Code:
Sub somesub()
Dim i As Integer, rank As Integer
lrow = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
For rank = 1 To 3
For i = 1 To lrow
If Range("B" & i).Value = Application.WorksheetFunction.Small(Range("B1:B" & lrow), rank) Then Range("c" & i).Value = rank
Next i
Next rank
End Sub
 
Last edited:

Forum statistics

Threads
1,078,285
Messages
5,339,296
Members
399,292
Latest member
Bdbd55

Some videos you may like

This Week's Hot Topics

Top