Solved

Find closest date in sorted vba collection

Posted on 2010-08-30
4
935 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

808 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