thanks Tom, very elegant, I liked it done using a variation of bisection
method should be always convergent

--
______
Regards,
Greg


"Tom Ogilvy" wrote:

l = cell - 15

should be

l = cell - x

--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote in message
...
Sub getvalues()
Dim x As Long, rng As Range
Dim ldiff As Long, hdiff As Long
Dim cell As Range, l As Long
Dim hi As Long, lo As Long
x = 15
Set rng = Range(Range("B1"), Range("B1").End(xlDown))
ldiff = -10000

hdiff = 10000
For Each cell In rng
l = cell - 15
If l < 0 Then
If l ldiff Then
ldiff = l
lo = cell
End If
ElseIf l = 0 Then
Else
If l < hdiff Then
hdiff = l
hi = cell
End If
End If
Next
MsgBox "LO: " & lo & vbNewLine & _
"HI: " & hi
End Sub


--
Regards,
Tom Ogilvy





"Greg" wrote in message
...
Hi,

I have a column with numbers that cant be sorted. There is an input x
that
does not have an exact much in any of the values. I need VBA to return
the
interval values for x.

2, 5, 76, 14, -7, 32.

For x = 15, i want lo = 14 and hi = 32.

Thanks!

--
______
Regards,
Greg