More Date problems

[Ken G]

I have a macro that deletes returned records older than 7 days. It first sorts the data into returned date order then checks to see if the first cell is empty (no records returned) and if so, re-sorts the data into the original order (a different date) and quits. If the first cell contains a date less than 7 days old ie date >today()-7, again it re-sorts and quits, otherwise it deletes the data older than 7 days, re-sorts the remaining data and quits. At least that what is supposed to happen

The line to check the age of the date in the macro below doesn't work. G6 contains a control date displayed in the worksheet being "today()-7" If I do the comparison in the worksheet itself with a yes/no cell to store the result, then have the macro check for yes/no, it works. It also doesn't work if I use "If selection >= today()-7

This is the macro

Sub Clean_up(

' Clean_up Macr
' Macro recorded 7/06/2004 by ken

Dim ans As Strin

ans = MsgBox("Deletion of returned files is permanent and irreversible. Do you wish to proceed?", vbYesNo
If ans = vbYes Then GoTo SortMod Else Exit Su

SortMod
Range("A7ffset(A6,G1-6,5)").Selec
Selection.Sort Key1:=Range("F7"), Order1:=xlAscending, Header:=xlNo,
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBotto

Range("F7").Selec

If Selection = "" Then GoTo DateSort Els

If Selection >= G6 Then GoTo DateSort Else [This is the line that doesn't work

Range("A7ffset(A6,G5,5)").Selec
Selection.Delet

DateSort
Run ["Date_Sort"

End Su

Thanks
Ken G.

 

[JE McGimpsey]

I can't follow exactly what you're doing, but just cleaning up your code
a bit produces

Public Sub Clean_up()
Dim ans As String
ans = MsgBox("Deletion of returned files is permanent and " & _
"irreversible. Do you wish to proceed?", vbYesNo)
If ans = vbNo Then Exit Sub
Range("A7:F" & Range("G1").Value).Sort _
Key1:=Range("F7"), _
Order1:=xlAscending, _
Header:=xlNo, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom
With Range("F7")
If IsNumeric(.Value) Then _
If .Value < Range("G6").Value Then _
Range("A7:F" & Range("G5").Value + 6).Delete
End With
Run ["Date_Sort"]
End Sub

This at least compiles, whereas Range("A7ffset(A6,G1-6,5))").Select
doesn't. Note that you almost never need to select a range in order to
work with it.

 

[Ken G]

Thanks for this. I actually got my original macro to work. Yes it is a bit
messy, but I'm learning!

Thanks again.
Ken G.

I can't follow exactly what you're doing, but just cleaning up your code
a bit produces

Public Sub Clean_up()
Dim ans As String
ans = MsgBox("Deletion of returned files is permanent and " & _
"irreversible. Do you wish to proceed?", vbYesNo)
If ans = vbNo Then Exit Sub
Range("A7:F" & Range("G1").Value).Sort _
Key1:=Range("F7"), _
Order1:=xlAscending, _
Header:=xlNo, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom
With Range("F7")
If IsNumeric(.Value) Then _
If .Value < Range("G6").Value Then _
Range("A7:F" & Range("G5").Value + 6).Delete
End With
Run ["Date_Sort"]
End Sub

This at least compiles, whereas Range("A7ffset(A6,G1-6,5))").Select
doesn't. Note that you almost never need to select a range in order to
work with it.

I have a macro that deletes returned records older than 7 days. It first
sorts the data into returned date order then checks to see if the first cell
is empty (no records returned) and if so, re-sorts the data into the original
order (a different date) and quits. If the first cell contains a date less
than 7 days old ie date >today()-7, again it re-sorts and quits, otherwise it
deletes the data older than 7 days, re-sorts the remaining data and quits.
At least that what is supposed to happen.

The line to check the age of the date in the macro below doesn't work. G6
contains a control date displayed in the worksheet being "today()-7" If I do
the comparison in the worksheet itself with a yes/no cell to store the
result, then have the macro check for yes/no, it works. It also doesn't work
if I use "If selection >= today()-7"

This is the macro:

Sub Clean_up()
'
' Clean_up Macro
' Macro recorded 7/06/2004 by keng
'
Dim ans As String

ans = MsgBox("Deletion of returned files is permanent and irreversible. Do
you wish to proceed?", vbYesNo)
If ans = vbYes Then GoTo SortMod Else Exit Sub

SortMod:
Range("A7ffset(A6,G1-6,5)").Select
Selection.Sort Key1:=Range("F7"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Range("F7").Select

If Selection = "" Then GoTo DateSort Else

If Selection >= G6 Then GoTo DateSort Else [This is the line that
doesn't work]

Range("A7ffset(A6,G5,5)").Select
Selection.Delete

DateSort:
Run ["Date_Sort"]

End Sub


Thanks,
Ken G.