Hi everyone,
First post so if I get it wrong feel free to educate me. I know enough VBA to be dangerous but not skilled by any means.
I have a Macro I created that filters dates by values from a "Due Date" column. If I run the Macro the results are coded from the current date. so Date-1 gives me 30 days late, Date-31 gives me 60 days late and Date-61 gives me 90 days late. It works just fine.
I'm trying to do the same thing for a Date Range so that if you run it on Monday it will pull all of the above but the program needs to do it for Sunday, Saturday, and Friday, or whatever the date range is.
I think I need to loop through each of the days but I can't find the solution.
Can anyone assist me?
Here is what I have to capture the date range:
Dim W1Startdate As Date, W1Enddate As Date
Selection.NumberFormat = "mm/dd/yyyy"
W1Startdate = Application.InputBox("Enter the Start Date:")
W1Enddate = Application.InputBox("Enter the End Date:")
ActiveSheet.Range("A1").AutoFilter Field:=6, Criteria1:=">=W1Startdate", Operator:=xlAnd, Criteria2:="<=W1Enddate"
Here is what I have for the SINGLE DATE that works but I need it to do the same thing for whatever dates are in the range:
'This pulls data for 30 Days Late
Dim rng30 As Range
Dim autofiltrng30 As Range
With ActiveSheet
.Range("A1").AutoFilter Field:=3, Criteria1:= _
"=Overdue", Operator:=xlOr, Criteria2:="=Registration Flagged"
.Range("A1").AutoFilter Field:=6, Criteria1:="=" & Date - 1 'This is where I need the code to pull 3 dates
'This pulls data for 60 Days Late
Dim rng60 As Range
Dim autofiltrng60 As Range
With ActiveSheet
.Range("A1").AutoFilter Field:=3, Criteria1:= _
"=Overdue", Operator:=xlOr, Criteria2:="=Registration Flagged"
.Range("A1").AutoFilter Field:=6, Criteria1:="=" & Date - 31 ' This is where I need the code to pull 3 dates
'This pulls data for 90 Days Late
Dim rng90 As Range
Dim autofiltrng90 As Range
With ActiveSheet
.Range("A1").AutoFilter Field:=3, Criteria1:= _
"=Overdue", Operator:=xlOr, Criteria2:="=Registration Flagged"
.Range("A1").AutoFilter Field:=6, Criteria1:="=" & Date - 61 'This is where I need the code to pull 3 dates
First post so if I get it wrong feel free to educate me. I know enough VBA to be dangerous but not skilled by any means.
I have a Macro I created that filters dates by values from a "Due Date" column. If I run the Macro the results are coded from the current date. so Date-1 gives me 30 days late, Date-31 gives me 60 days late and Date-61 gives me 90 days late. It works just fine.
I'm trying to do the same thing for a Date Range so that if you run it on Monday it will pull all of the above but the program needs to do it for Sunday, Saturday, and Friday, or whatever the date range is.
I think I need to loop through each of the days but I can't find the solution.
Can anyone assist me?
Here is what I have to capture the date range:
Dim W1Startdate As Date, W1Enddate As Date
Selection.NumberFormat = "mm/dd/yyyy"
W1Startdate = Application.InputBox("Enter the Start Date:")
W1Enddate = Application.InputBox("Enter the End Date:")
ActiveSheet.Range("A1").AutoFilter Field:=6, Criteria1:=">=W1Startdate", Operator:=xlAnd, Criteria2:="<=W1Enddate"
Here is what I have for the SINGLE DATE that works but I need it to do the same thing for whatever dates are in the range:
'This pulls data for 30 Days Late
Dim rng30 As Range
Dim autofiltrng30 As Range
With ActiveSheet
.Range("A1").AutoFilter Field:=3, Criteria1:= _
"=Overdue", Operator:=xlOr, Criteria2:="=Registration Flagged"
.Range("A1").AutoFilter Field:=6, Criteria1:="=" & Date - 1 'This is where I need the code to pull 3 dates
'This pulls data for 60 Days Late
Dim rng60 As Range
Dim autofiltrng60 As Range
With ActiveSheet
.Range("A1").AutoFilter Field:=3, Criteria1:= _
"=Overdue", Operator:=xlOr, Criteria2:="=Registration Flagged"
.Range("A1").AutoFilter Field:=6, Criteria1:="=" & Date - 31 ' This is where I need the code to pull 3 dates
'This pulls data for 90 Days Late
Dim rng90 As Range
Dim autofiltrng90 As Range
With ActiveSheet
.Range("A1").AutoFilter Field:=3, Criteria1:= _
"=Overdue", Operator:=xlOr, Criteria2:="=Registration Flagged"
.Range("A1").AutoFilter Field:=6, Criteria1:="=" & Date - 61 'This is where I need the code to pull 3 dates