VBA capture cell address of nth smallest value in a range

powera86 · Sep 6th 2012

Hi there,

Is there a way to capture what the cell address is of the nth smallest value in a specified range?

What I am trying to do is pass in a range to my code, and then fill the cells within that range a certain colour depending if it is the 1st smallest, 2nd smallest, 3rd smallest value etc. in the range?

Any help would be appreciated.

Thanks!

venkat1926 · Sep 7th 2012

Re: VBA capture cell address of nth smallest value in a range

sample data is attached file

the macro is in vba and repeated here

Code

Sub testg()
Dim r As Range, j As Integer, n As Integer, cfind As Range, x
n = 5
Set r = Range(Range("a2"), Range("a2").End(xlDown))
r.Cells.Interior.ColorIndex = xlNone
For j = 1 To n
x = WorksheetFunction.Small(r, j)
Set cfind = r.Find(what:=x, lookat:=xlWhole)
cfind.Interior.ColorIndex = j
Next j


End Sub
' I am using j itself as color index if you want to change modify the macro

Display More

VBA capture cell address of nth smallest value in a range

Participate now!

Share

Users Viewing This Thread