Re: finding next smallest value in a column
- From: "Bernard Liengme" <bliengme@xxxxxxxxxxxxxxxxx>
- Date: Thu, 2 Feb 2006 13:48:47 -0400
This funtion which is called with, for example, =NEARTO(A1:A17, 50) or
seems to work. Test it for yourself
Function nearto(rng, num)
nearto = "none"
' copy range to table
k = 1
For Each cell In rng
mytable(k) = cell
k = k + 1
k = k - 1
' sort table ascending
For j = 1 To k - 1
For n = j + 1 To k
If mytable(n) > mytable(j) Then
temp = mytable(j)
mytable(j) = mytable(n)
mytable(n) = temp
For j = 1 To k
If num >= mytable(j) Then
nearto = mytable(j)
Bernard V Liengme
remove caps from email
"Greg" <Greg@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
I have a column of UNsorted numbers and need VBA to find the closest value
that is smaller than input value. So, if column contains: 89, 1, 5, 65,
19, 9. and the given number x is 50, then the code should return 19.
The column cannot be sorted.
- Prev by Date: There a simple way to do this?
- Next by Date: Re: File size keeps multiplying
- Previous by thread: There a simple way to do this?
- Next by thread: Re: File size keeps multiplying