Ranking using VBA

[MrTeeny]

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]

Resorted to using small in the end

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