• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 259
  • Last Modified:

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

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

2 Solutions


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

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

gabrielPennybackAuthor Commented:
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now