Help with 30, 60 , 90 Days overdue using date range

[ma3kBurns]

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

 

[DanteAmor]

Hi and welcome to MrExcel.

The date variable (starDate, endDate) must be outside the quotation marks.

It is difficult to work with dates and filters, sometimes it must be string, sometimes date and even double type.
I tested the following and it works for me.

Sub test()
  Dim sDate As Date, eDate As Date
  sDate = Application.InputBox("Enter the Start Date: ")
  eDate = Application.InputBox("Enter the End Date: ")
  ActiveSheet.Range("A1").AutoFilter 6, ">=" & Format(sDate, "mm/dd/yyyy"), xlAnd, "<=" & Format(eDate, "mm/dd/yyyy")
End Sub

 

[ma3kBurns]

DanteAmor,
Thank you for the assistance. This works for today's date because my input boxes' end date is the day before the current date so 30 days late is today -1 or what I was using, Date - 1.

Later in my code I have a sheet created that also does the same thing, or it's supposed to, but it is Date -31, for 60 days late and even further in my code it is Date - 61 for 90 days late.
Entering a single date it calculates just fine for 30, 60, and 90 Days late.
Your code is capturing ALL the 30 Days late because it's grabbing everything between the start date and the end date.
MY code is capturing the 60 and 90 Days late for ONLY the eDate because my code simply reads Date -31 and Date -61 for the output. How do I I incorporate 30, 60, and 90 days late for ALL the dates entered from sDate to EDate?


With ActiveSheet
.Range("A1").AutoFilter Field:=3, Criteria1:= _
"=Overdue", Operator:=xlOr, Criteria2:="=Registration Flagged"
.Range("A1").AutoFilter Field:=6, Criteria1:="=" & Date - 31 <------- 'HERE IS WHERE I PUT YOUR CODE BUT I NEED IT "-31", AND "-61" TO CAPTURE ALL OF THE DATES BUT I CAN'T GET IT TO WORK

I hope that makes sense to you.

 

[DanteAmor]

I hope to understand.
Assuming you have this data before filtering.

varios 14sep2020.xlsm
	A		C			F		H	I
1	A		C			DATE		Days overdue
2	A7		Overdue			21-jun		-86
3	A8		Overdue			25-jun		-82
4	A9		Overdue			29-jun		-78
5	A10		Overdue			03-jul		-74
6	A11		Overdue			07-jul		-70
7	A12		Overdue			11-jul		-66
8	A13		Overdue			15-jul		-62
9	A14		Overdue			19-jul		-58	>= - 61
10	A15		Overdue			23-jul		-54
11	A16		Overdue			27-jul		-50
12	A17		Overdue			31-jul		-46
13	A18		Overdue			04-ago		-42
14	A19		Overdue			08-ago		-38
15	A20		Overdue			12-ago		-34	<= - 31
16	A21		Overdue			16-ago		-30
17	A22		Overdue			20-ago		-26
18	A23		Overdue			24-ago		-22
19	A24		Overdue			28-ago		-18
20	A25		Overdue			01-sep		-14
21	A26		Overdue			15-sep		0	<-- today
Sheet1a

After filtering:

varios 14sep2020.xlsm
	A		C			F		H	I
1	A		C			DATE		Days overdue
9	A14		Overdue			19-jul		-58	>= - 61
10	A15		Overdue			23-jul		-54
11	A16		Overdue			27-jul		-50
12	A17		Overdue			31-jul		-46
13	A18		Overdue			04-ago		-42
14	A19		Overdue			08-ago		-38
15	A20		Overdue			12-ago		-34	<= - 31
Sheet1a

If the above is correct then:

Sub test2()
  With ActiveSheet
    .Range("A1").AutoFilter 3, "=Overdue", xlOr, "=Registration Flagged"
    .Range("A1").AutoFilter 6, ">=" & Format(Date - 61, "mm/dd/yyyy"), xlAnd, "<=" & Format(Date - 31, "mm/dd/yyyy")
  End With
End Sub

The macro above works for me.
If the above is not what you need, then you could show with examples what you need. Use the XL2BB tool to paste ranges from your sheet here. (see my signature)