# Find closest date in sorted vba collection

Hello,

I have a sorted Collection containing dates (Excel VBA).  I need to find the closest date in the collection to a date 12 months prior to the current date.  Although the data is in a collection it can be put into another object such as an Array if that makes more sense to do this.

I'd appreciate any thoughts on the best way to do this.

Thanks.
Asked:
###### Who is Participating?

Commented:
Try the below function. To find the closest date to a date 12 months prior to today, you would use it like this....

Dim dt As Date
dt = FindClosestDate(DateAdd("m", -12, Date), coll)

Wayne
Function FindClosestDate(dt, coll As Collection) As Date
Dim diff As Long: diff = 99999
Dim closest As Date, d As Long
For d = 1 To coll.Count
If Abs(DateDiff("d", coll(d), dt)) < diff Then
closest = coll(d)
diff = Abs(DateDiff("d", coll(d), dt))
End If
Next
FindClosestDate = closest
End Function
0

Author Commented:
Perfect. Thanks very much Wayne.
0

Commented:
Hi

90        Do While ie.busy Or Not ie.ReadyState = IE_READYSTATE.Complete: DoEvents: Loop

htmlStr = ie.document.body.innerHTML

pointer = InStr(1, htmlStr, "Future Events")
pointer = InStr(pointer, htmlStr, "vAlign")
htmlStr = Mid(htmlStr, pointer + 18, 9)
Range("B5").Value = htmlStr

100       Set doc = ie.document

The additional lines between 90 & 100 passed the test on your first sample (A5), did not test it further, but I think it should work for all.
0

Commented:
Apologies, wrong question, disregard my comment.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

## Already a member? Login.

All Courses

From novice to tech pro — start learning today.