Days array giving headache

Chris_Li

New Member
Joined
Mar 1, 2021
Messages
4
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

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

jasonb75

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

Chris_Li

New Member
Joined
Mar 1, 2021
Messages
4
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,051
Messages
5,639,773
Members
417,112
Latest member
PachRedoc

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