• 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. :-)

Thanks,
John

``````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
strtDate.Copy
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
``````
0
gabrielPennyback
Asked:
2 Solutions

Commented:
Hi

Try

``````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
``````

Kris
0

Commented:
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")
Loop
``````
0

Author 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
0

## Featured Post

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