Due activity list including next 5 days

R

Rahul Malhotra

New member
Joined
Apr 18, 2018
Messages
43
Reaction score
0
Points
0
Excel Version(s)
2013, 2016
Dear Experts,

I am trying to use the VBA for getting a filtered list of due activities based on due dates for the next approaching 5 days includes today but could not able to do so and with using the current script I am able to get only todays due activities and not for next 5 approaching days. I am not sure where is the disconnect. Request to you please do help me on the same.
Code: 
Sub due()
Application.ScreenUpdating = False
With Sheets("Expected Output")
    .[A3].CurrentRegion.Offset(1).Clear
    .[Z1:Z4].Value = Application.Transpose(Array("Due Date", "Daily", "As Needed", "As Received"))
    .[Z5:Z10].Value = Evaluate("TEXT(TODAY()+ROW(1:6)-1,""dd.mm.yyyy"")")
    Sheets("Details").[A3].CurrentRegion.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=.[Z1:Z10], CopyToRange:=.[A3:G3]
    .[Z1:Z10].Clear
End With
Application.ScreenUpdating = True
End Sub
 

Attachments

  • Due Activity List Sample Workbook.xlsm
    23.8 KB · Views: 10
Last edited by a moderator:
Seems fine here, this is what I get if I run it 'out of the box' (on 14th Oct 2021):
2021-10-14_171909.png
If you're not in a locale which recognises dd.mm.yyyy (in Germany it would be tt.mm.jjjj) it could be the values in cells Z5:Z10 are not what you expect them to be. Check this and get back, we can get round this easily.

cross posted without links:
https://www.mrexcel.com/board/threads/due-activity-list-including-next-5-days.1184543/
Rahul Malhotra, for your information, you should always provide links to your cross posts.
This is a requirement, not just a request.
If you have cross posted at other places, please add links to them too.
Why? Have a read of http://www.excelguru.ca/content.php?184
 
Last edited:
Dear Experts,

Can you please help me on the same.

Regards,

Rahul
 
Rahul Malhotra said:
Can you please help me on the same.
Click to expand...
You've not commented on my last screenshot; is there something wrong there?
It's very difficult to put right something which is not wrong.
 
You've missed the point, I was talking about the screenshot; this:
View attachment 10664
[Click on it if it's too small] Is there something wrong? (There didn't seem to be anything wrong, so how can I fix something that is already right?)

What do you get if you enter this formula into a cell in the same Excel that you get incorrect results in):
=TEXT(TODAY(),"dd.mm.yyyy")
?
 
try changing:
.[Z5:Z10].Value = Evaluate("TEXT(TODAY()+ROW(1:6)-1,""dd.mm.yyyy"")")
to:
.[Z5:Z10].FormulaArray = "=TEXT(TODAY()+ROW(R1:R6)-1,""dd.mm.yyyy"")"

 
p45cal said:
try changing:
.[Z5:Z10].Value = Evaluate("TEXT(TODAY()+ROW(1:6)-1,""dd.mm.yyyy"")")
to:
.[Z5:Z10].FormulaArray = "=TEXT(TODAY()+ROW(R1:R6)-1,""dd.mm.yyyy"")"
Click to expand...
Thank you so much dear expert. Worked perfectly. Thank you once again.

Regards,

Rahul
 
I am just looking for this code. Thanks guys
 
You must log in or register to reply here.
Back
Top