Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2002: Any formulas to look up unsorted column ? | Excel Discussion (Misc queries) | |||
Difference from highest in unsorted column | Excel Discussion (Misc queries) | |||
Percentage calculations from an unsorted two column list | Excel Discussion (Misc queries) | |||
Finding the closest number in column A and take the value in column B | Excel Worksheet Functions | |||
Find the closest match to a reference number in a row of unsorted | Excel Worksheet Functions |