finding next smallest value in a column

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

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@discussions.microsoft.com> wrote in message
news:84E6D81F-B14F-40BB-AB05-2A88EE5D7BD2@microsoft.com...
> 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

Please ignore this post, I cross-posted in Programming section.


"Greg" wrote:

> 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