Solved

Find closest date in sorted vba collection

Posted on 2010-08-30
4
873 Views
Last Modified: 2012-05-10
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
Comment
Question by:tim_chamberlain
  • 2
4 Comments
 
LVL 47

Accepted Solution

by:
Wayne Taylor (webtubbs) earned 200 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

Open in new window

0
 

Author Closing Comment

by:tim_chamberlain
ID: 33557858
Perfect. Thanks very much Wayne.
0
 
LVL 17

Expert Comment

by:calacuccia
ID: 33557878
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
 
LVL 17

Expert Comment

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

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now