finding the closest matching date in a range

[dinotom]

Hello,

What is the fastest way to search a column of dates to find the closest date to a date passed as a parameter to the routine?

I am doing it by looping thru each cell in the range but Im sure there is a faster way

 

[sulakvea]

hi, is sorting the column an option in the macro?

 

[dinotom]

the dates are all ready in order

 

[sulakvea]

but still - is resorting an option?

 

[sulakvea]

and if sorting the data is an option - here it is

replace DateVal = 25 line with whatever procedure you use to pass the value along. DateVal is that number (date in your case) that you wanna find the closest match for.
assumes data sits in Col A1:A1000

Sub FindClosestMatch()

DateVal = 25

Range("A1:A1000").Sort Key1:=Range("A1"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
MinVal = Cells(Application.WorksheetFunction.Match(DateVal, Range("A1:A1000"), -1), 1)

Range("A1:A1000").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
MaxVal = Cells(Application.WorksheetFunction.Match(DateVal, Range("A1:A1000"), 1), 1)

If MinVal - DateVal > DateVal - MaxVal Then
 FinalVal = MaxVal
Else
 FinalVal = MinVal
End If
MsgBox (FinalVal)

End Sub

 

[dinotom]

thank you