[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Find closest date in sorted vba collection

Posted on 2010-08-30
4
Medium Priority
?
1,056 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
[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
  • Learn & ask questions
  • 2
4 Comments
 
LVL 48

Accepted Solution

by:
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

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

656 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