Solved

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

Posted on 2012-09-19
231 Views
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
Question by:gabrielPennyback

LVL 18

Accepted Solution

Hi

Try

``````Dim a   As String
With Workbooks(wb).Sheets(ws).Range(Workbooks(wb).Sheets(ws).[A6], Workbooks(wb).Sheets(ws).[A10000].End(xlUp))
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

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

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
0

## Featured Post

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…