next closest value in unsorted column
 

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

next closest value in unsorted column
 

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

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

next closest value in unsorted column
 

You can give this a try... This assumes that the ranges that you are
searching are all numeric values...

Sub Test()
MsgBox HiValue(15, Range("A2:A7"))
MsgBox LoValue(15, Range("A2:A7"))

End Sub

Public Function HiValue(ByVal dblInput As Double, ByVal Target As Range) As
Double
Dim rng As Range
Dim dblMargin As Double

HiValue = Target(1).Value
dblMargin = Target(1).Value - dblInput
For Each rng In Target
If rng.Value = dblInput Then
HiValue = rng.Value
Exit For
ElseIf rng.Value dblInput Then
If dblInput - rng.Value < dblMargin Then
dblMargin = rng.Value - dblInput
HiValue = rng.Value
End If
End If
Next rng
End Function

Public Function LoValue(ByVal dblInput As Double, ByVal Target As Range) As
Double
Dim rng As Range
Dim dblMargin As Double

LoValue = Target(1).Value
dblMargin = dblInput - Target(1).Value
For Each rng In Target
If rng.Value = dblInput Then
LoValue = rng.Value
Exit For
ElseIf rng.Value < dblInput Then
If dblInput - rng.Value < dblMargin Then
dblMargin = dblInput - rng.Value
LoValue = rng.Value
End If
End If
Next rng
End Function

--
HTH...

Jim Thomlinson


"Greg" wrote:

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

next closest value in unsorted column
 

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