Solved

# Find closest date in sorted vba collection

Posted on 2010-08-30
Medium Priority
1,020 Views
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.
0
Question by:tim_chamberlain
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 2

LVL 47

Accepted Solution

Wayne Taylor (webtubbs) earned 800 total points
ID: 33557802
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 Closing Comment

ID: 33557858
Perfect. Thanks very much Wayne.
0

LVL 17

Expert Comment

ID: 33557878
Hi

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

LVL 17

Expert Comment

ID: 33557889
Apologies, wrong question, disregard my comment.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
###### Suggested Courses
Course of the Month12 days, 15 hours left to enroll