next closest value in unsorted column
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
|