Re: finding next smallest value in a column

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
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
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.

Thank you!


Relevant Pages

  • Re: #num Error index, match
    ... Select the formula cell. ... What does the #NUM mean. ... I want the formula to look in the range name alabamasort find TAX ...
  • Re: finding records
    ... Dim rng1 as Range, rng2 as Range ... Dim cell as Range, num as Long ... worksheet 2 has the 50,000 rows with equip ids also. ...
  • Re: Loop through ranges
    ... For Each cell In Rows.Columns ... Dim arr1, num ... Set rng = Nothing ... Exit For ...
  • RE: Another Easy Question
    ... I don't want to assign it back to the cell. ... of each F in the range and assign this number to the name Num and use Num as ... When competing hypotheses are otherwise equal, ... Dim F As Range ...
  • match or lookup?
    ... I'll assume you do and they're on Sheet2 in col A. I'll ... On Sheet2 cell B1 enter this array formula using the key ... To get rid of the #NUM! ... >row3 450 Mouse Scanner Server CDROM ...