Days array giving headache

Chris_Li

New Member
Joined
Mar 1, 2021
Messages
17
Office Version
  1. 365
Platform
  1. Windows
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?
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,761
Office Version
  1. 365
Platform
  1. Windows

Chris_Li

New Member
Joined
Mar 1, 2021
Messages
17
Office Version
  1. 365
Platform
  1. Windows
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,619
Messages
5,838,394
Members
430,546
Latest member
CometOz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top