Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hunt for date 2 months back
Currently this code goes back 1 month to put rows from Information to
Statistics that have dates which are from the previous month. Do you know how I can make it go only 2 actual months back, so if I were to activate this macro today, it would only grab dates from November instead of November and December? Sub HuntDate() Dim Cell As Range Dim CheckDate As Date Dim DstRng As Range Dim NextRow As Long Dim Rng As Range Dim RngEnd As Range Dim SrcRng As Range currentMonth = Month(Date) Set SrcRng = Worksheets("Information").Range("AS2") Set DstRng = Worksheets("Statistics").Range("A2") Set RngEnd = SrcRng.Parent.Cells(Rows.Count, SrcRng.Column).End(xlUp) Set SrcRng = IIf(RngEnd.Row < SrcRng.Row, SrcRng, SrcRng.Parent.Range(SrcRng, RngEnd)) Set RngEnd = DstRng.Parent.Cells(Rows.Count, DstRng.Column).End(xlUp) Set DstRng = IIf(RngEnd.Row < DstRng.Row, DstRng, RngEnd.Offset(1, 0)) For Each Cell In SrcRng If Cell = CheckDate And Cell <= Int(Now()) Then If Rng Is Nothing Then Set Rng = Cell Set Rng = Union(Rng, Cell) Cell.EntireRow.Copy DstRng.Offset(NextRow, 0) NextRow = NextRow + 1 End If Next Cell If Not Rng Is Nothing Then Rng.EntireRow.Delete End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Rolling chart to show 13 months back from current month | Charts and Charting in Excel | |||
3 months prior and 3 months post a date | Excel Worksheet Functions | |||
Todays date plus 3 months & less 3 months | Excel Programming | |||
Months going back from present | New Users to Excel | |||
Getting a date six months back | Excel Worksheet Functions |