T
Teddy
Sub SearchDate()
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
CheckDate = Int(Now()) - 30
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
I have two worksheets “Information†and “Statisticsâ€. I would like to
search every row in Column AS of worksheet “Information†for dates. If any
date in Column AS is not a date within the current month I would like to cut
out the row from worksheet “Information†and insert it into worksheet
“Statisticsâ€.
This code above looks in every row of sheet “Information†/Column AS for a
date that is less than or equal to 30 days from today’s date. When a date in
Column AS matches that criterion then the entire row that the date is in is
transferred to a new row in worksheet “Statisticsâ€. I don’t want to do that,
I want the criterion to be based on the current month only. For example, if
I scanned Column AS in sheet “Information†and dates from November appeared,
I would like those rows to be cut from sheet “Information†and inserted into
sheet “Statistics†since the current month is December. Can you help me
modify the code?
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
CheckDate = Int(Now()) - 30
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
I have two worksheets “Information†and “Statisticsâ€. I would like to
search every row in Column AS of worksheet “Information†for dates. If any
date in Column AS is not a date within the current month I would like to cut
out the row from worksheet “Information†and insert it into worksheet
“Statisticsâ€.
This code above looks in every row of sheet “Information†/Column AS for a
date that is less than or equal to 30 days from today’s date. When a date in
Column AS matches that criterion then the entire row that the date is in is
transferred to a new row in worksheet “Statisticsâ€. I don’t want to do that,
I want the criterion to be based on the current month only. For example, if
I scanned Column AS in sheet “Information†and dates from November appeared,
I would like those rows to be cut from sheet “Information†and inserted into
sheet “Statistics†since the current month is December. Can you help me
modify the code?