# 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

=NEARTO(A1:A17, B1)

seems to work. Test it for yourself

Function nearto(rng, num)

Dim mytable(255)

nearto = "none"

' copy range to table

k = 1

For Each cell In rng

mytable(k) = cell

k = k + 1

Next

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

End If

Next n

Next j

For j = 1 To k

If num >= mytable(j) Then

nearto = mytable(j)

Exit For

End If

Next j

End Function

--

Bernard V Liengme

www.stfx.ca/people/bliengme

remove caps from email

"Greg" <Greg@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message

news:84E6D81F-B14F-40BB-AB05-2A88EE5D7BD2@xxxxxxxxxxxxxxxx

Hi,

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,

14,

19, 9. and the given number x is 50, then the code should return 19.

The column cannot be sorted.

Thank you!

--

______

Regards,

Greg

.

- 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** - Index(es):