Days array giving headache

[Chris_Li]

Hi Excel Gurus,

I hope I can find help here.

Every day I sent mails to customer to give them a heads up, that invoices are due in 10 days.
That works fine. But as I'm not working during the weekend, all invoices that will be due in 10 days counted from a Saturday or Sunday are not reported to the customer.
So I need to sent on Friday a heads up for invoices due in 10, 11 and 12 days which would be a Tuesday (11 days) or a Wednesday (12 days).

I tried to create a macro that covers all option:
Show me on Monday, Tuesday, Wednesday and Thursday invoice numbers due in 10 days and
on Fridays show me invoice numbers due in 10, 11, 12 days.

I tried everything with creating a helper column, showing all Invoiced due on a Tuesday and Wednesday, filtering for an array of 10 to 12 days but:

I miserably fail...I get all kind of error messages from excel

Here the code that is working fine:

Sub Filtern_der_Daten()

Worksheets("Raw").Range("A1").AutoFilter

With Worksheets("Raw").Range("A1")
.AutoFilter Field:=6, Criteria1:="-10"
End With

'Copy findings in column A and B in a new sheet named Check

Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets.Add(After:= _
ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
ws.Name = "Check"

Sheets("Raw").Select
Columns("A:B").Select
Selection.copy
Sheets("Check").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, transpose:=False


'Formular to show Invoices in processable design


Range("I1").Formula = "=RC[-7]&IF(RC[-8]=R[1]C[-8],""|""&R[1]C,"""")"

Range("I1").Select
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("I1:I691"), Type:=xlFillDefault
Range("I1:I691").Select
ActiveWindow.ScrollRow = 1
Columns("I:I").Select
Selection.copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, transpose:=False
Columns("A:I").Select
Application.CutCopyMode = False
ActiveSheet.Range("$A$1:$I$1018156").RemoveDuplicates Columns:=1, Header:= _
xlNo


End Sub

Can you please give me a hand to amend above code so it covers my needs?

 

[jasonb75]

I tried everything with creating a helper column,

With a helper column, it will work if you subtract 9 actual days, then 1 working day.

Book1
	A	B	C	D
1	Due Date		Reminder
2	01-Mar-21	Mon	19-Feb-21	Fri
3	02-Mar-21	Tue	19-Feb-21	Fri
4	03-Mar-21	Wed	19-Feb-21	Fri
5	04-Mar-21	Thu	22-Feb-21	Mon
6	05-Mar-21	Fri	23-Feb-21	Tue
7	06-Mar-21	Sat	24-Feb-21	Wed
8	07-Mar-21	Sun	25-Feb-21	Thu
9	08-Mar-21	Mon	26-Feb-21	Fri
10	09-Mar-21	Tue	26-Feb-21	Fri
Sheet1

Cell Formulas
Range	Formula
C2:C10	C2	=WORKDAY(A2-9,-1)

 

[Chris_Li]

Hey Jasonb75, thank you very much for your help. Again something learned. I will try to go on from there and see if I can create one macro that covers the Mon-Thu task and on Fri the Fri task.

Thank you very much again