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