Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 45
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Rolling chart to show 13 months back from current month PBcorn Charts and Charting in Excel 2 February 7th 09 11:34 AM
3 months prior and 3 months post a date renee Excel Worksheet Functions 2 May 2nd 08 05:46 PM
Todays date plus 3 months & less 3 months Les Stout[_2_] Excel Programming 4 November 1st 06 05:55 PM
Months going back from present DavidS New Users to Excel 4 October 9th 06 10:24 AM
Getting a date six months back Daniel Bonallack Excel Worksheet Functions 4 August 4th 05 12:20 AM


All times are GMT +1. The time now is 07:48 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"