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 ...
  • Re: Date abbreviations / extensions
    ... I copied your code and pasted it into the worksheet ... code and then copied the formula into a cell. ... Function OrdinalNumber(ByVal Num As Long) As String ... Gord Dibben MS Excel MVP ...