Defining a variable as the highest date that is equal to or greater than a specified date

Posted on 2012-09-19
Last Modified: 2012-10-04
In this code, how do I define the variable 'endDate' so that it looks not for 'Day7' but rather it looks for  'Day7' and if it fails to find it, then it looks for the lowest date that is higher than 'Day7'?

In other words, if 'strtDate' is "2-Jan-12", I'm hoping to find the first cell in my target range containing "9-Jan-12"  to be my 'endDate'; but if there is no "9-Jan-12," then I want the first date higher than that. Could be "10-Jan-12" or "22-Jan-12" or whatever.

I hope I'm expressing myself clearly. :-)


Sub ImportData()
Range([N6], [O10000].End(xlUp)).NumberFormat = "d-mmm-yy"
If [F9] <> "" Then Range([A6], [F10000].End(xlUp).Offset(0, 45)).Clear
Dim top As Range, btm As Range, dt1 As Long, dt2 As Long, _
    Day1 As String, Day7 As String, wb As String, ws As String
    wb = "QTR Aggregator_Jan-Aug 2012.xlsm"
    ws = "All Incidents"
dt1 = [K1]
Day1 = Format(dt1, "d-mmm-yy")
dt2 = dt1 + 7
Day7 = Format(dt2, "d-mmm-yy")
    Range([A6], [A10000].End(xlUp)).NumberFormat = "d-mmm-yy"
    Dim strtDate As Range, endDate As Range, targRng As Range
    Set strtDate = Workbooks(wb).Sheets(ws).Columns(1).Find(what:=Day1, after:=[A5], LookIn:=value's) 'strtDate.Select
    Set endDate = Workbooks(wb).Sheets(ws).Columns(1).Find(what:=Day7, after:=[A5], LookIn:=xlValues).Offset(-1, 0) 'endDate.Select
    Set targRng = Range(strtDate, endDate)
    targRng.Copy [N6]
    targRng.Copy [O6]
    targRng.Offset(0, 7).Copy [S6]
    targRng.Offset(0, 3).Copy [T6]
    targRng.Offset(0, 4).Copy [U6]
    targRng.Offset(0, 8).Copy [F6]
    Range([N6], [O10000].End(xlUp)).NumberFormat = "d-mmm-yy"
    Range([A6], [S10000].End(xlUp)).HorizontalAlignment = xlCenter
    Range([A6], [F10000].End(xlUp).Offset(0, 45)).VerticalAlignment = xlCenter
    Range([T6], [F10000].End(xlUp)).WrapText = True
    Range([A6], [F10000].End(xlUp).Offset(0, 45)).Borders.Color = RGB(210, 210, 255)
End Sub

Open in new window

Question by:gabrielPennyback
    LVL 18

    Accepted Solution



    Dim a   As String
        With Workbooks(wb).Sheets(ws).Range(Workbooks(wb).Sheets(ws).[A6], Workbooks(wb).Sheets(ws).[A10000].End(xlUp))
            a = .Address(external:=1)
            endDate = Evaluate("min(if(" & a & ">=""" & dt2 & """+0," & a & "))")
        End With
        Set endDate = Workbooks(wb).Sheets(ws).Columns(1).Find(what:=endDate, after:=[A5], LookIn:=xlValues).Offset(-1, 0) 'endDate.Select

    Open in new window

    LVL 29

    Assisted Solution

    Try modifying line 16 this way:
    Do While endDate Is Nothing
        Set endDate = Workbooks(wb).Sheets(ws).Columns(1).Find(what:=Day7, after:=[A5], LookIn:=xlValues).Offset(-1, 0) 'endDate.Select
        dt2 = dt2 + 1
        Day7 = Format(dt2, "d-mmm-yy")    

    Open in new window

    LVL 1

    Author Closing Comment

    The urgency to solve this has gone away and when i have time I think I might be able to make one or both of your solutions to work, which is why I'm awarding you the points now.<br /><br />Thanks,<br />John

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    I've recently been in need of an Excel macro that could add a letter before the text on multiple cells in an Excel document. My English is as it is, so I will try explain what it does diffrently. If you have an excel document with 2000 rows an…
    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…
    The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

    745 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

    16 Experts available now in Live!

    Get 1:1 Help Now