Results 1 to 9 of 9

Thread: Due activity list including next 5 days

  1. #1
    Acolyte Rahul Malhotra's Avatar
    Join Date
    Apr 2018
    Posts
    43
    Articles
    0
    Excel Version
    2013, 2016

    Due activity list including next 5 days



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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
    Attached Files Attached Files
    Last edited by p45cal; 2021-10-14 at 05:24 PM. Reason: removed highlighting tags from code

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    2,030
    Articles
    0
    Excel Version
    365
    Seems fine here, this is what I get if I run it 'out of the box' (on 14th Oct 2021):
    Click image for larger version. 

Name:	2021-10-14_171909.png 
Views:	10 
Size:	13.1 KB 
ID:	10664
    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/thread...-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 by p45cal; 2021-10-14 at 05:38 PM.

  3. #3
    Acolyte Rahul Malhotra's Avatar
    Join Date
    Apr 2018
    Posts
    43
    Articles
    0
    Excel Version
    2013, 2016
    Dear Experts,

    Can you please help me on the same.

    Regards,

    Rahul

  4. #4
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    2,030
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by Rahul Malhotra View Post
    Can you please help me on the same.
    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.

  5. #5
    Acolyte Rahul Malhotra's Avatar
    Join Date
    Apr 2018
    Posts
    43
    Articles
    0
    Excel Version
    2013, 2016
    Quote Originally Posted by p45cal View Post
    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.
    Hi,

    Apologies for the same. I have marked as unwatch to the raised concern at https://www.mrexcel.com/board/thread...-days.1184543/.

    Since i have not get any responses yet so raised here. Would like to request please do help on the same.

    Regards,

    Rahul

  6. #6
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    2,030
    Articles
    0
    Excel Version
    365
    You've missed the point, I was talking about the screenshot; this:
    Click image for larger version. 

Name:	2021-10-14_171909.png 
Views:	10 
Size:	13.1 KB 
ID:	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")
    ?

  7. #7
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    2,030
    Articles
    0
    Excel Version
    365
    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"")"


  8. #8
    Acolyte Rahul Malhotra's Avatar
    Join Date
    Apr 2018
    Posts
    43
    Articles
    0
    Excel Version
    2013, 2016
    Quote Originally Posted by p45cal View Post
    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"")"

    Thank you so much dear expert. Worked perfectly. Thank you once again.

    Regards,

    Rahul

  9. #9
    Neophyte Fail1's Avatar
    Join Date
    Nov 2021
    Posts
    1
    Articles
    0
    Excel Version
    10
    I am just looking for this code. Thanks guys

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •