none
Macro needed for date filter RRS feed

  • Question

  • Spreadsheet that is used for delivery schedule. Column A is a list of locations, Column B is a list of date, both past due and for the upcoming 12 months.

    Macro Needed:- On any given day one would open the spreadsheet and hit a macro button that can filter column B to show delivery dates that are past due and delivery dates that are 3 weeks out, based on todays date according to time/date of Windows OS.

    I know you can just do a manual filter, but for some reason it proves to be challenge for some of our older salespeople.
    Thank you for your help.
    Saturday, October 15, 2011 11:12 PM

Answers

  • hi jchakkalakal,
     
    Range("B1").AutoFilter Field:=1, Criteria1:="<=" & Now - 21
     --
    isabelle
     
    Le 2011-10-15 19:12, jchakkalakal a écrit :
    > Spreadsheet that is used for delivery schedule. Column A is a list of locations, Column B is a list of date, both past due and for the upcoming 12 months.
    >
    > Macro Needed:- On any given day one would open the spreadsheet and hit a macro button that can filter column B to show delivery dates that are past due and delivery dates that are 3 weeks out, based on todays date according to time/date of Windows OS.
    >
    > I know you can just do a manual filter, but for some reason it proves to be challenge for some of our older salespeople.
    > Thank you for your help.
     
    Sunday, October 16, 2011 1:36 AM
  • I know you can just do a manual filter, but for some reason it proves to be challenge for some of our older salespeople.

    Exactly what do you mean by "dates that are 3 weeks out"? More than 3 weeks old (before today) or due in more than 3 weeks after today or due within the next 3 weeks. I am assuming the latter but need to be sure.

    Do you want "delivery dates that are past due and delivery dates that are 3 weeks out" to be displayed together or will they be displayed with separate filters?

    As you have said that you can do a manual filter, I suggest that you record the code to filter as required. Now depending on the date format in your region, the code might not run as required and produce the same results as when it was recorded but if you post the recorded code then I am sure that I or someone else will be able to edit the code and make it work for you.

    Recording code to do what you want is often a simple way of describing the situation.


    Regards, OssieMac
    Sunday, October 16, 2011 1:43 AM

All replies

  • Spreadsheet that is used for delivery schedule. Column A is a list of locations, Column B is a list of date, both past due and for the upcoming 12 months.

    Macro Needed:- On any given day one would open the spreadsheet and hit a macro button that can filter column B to show delivery dates that are past due and delivery dates that are 3 weeks out, based on todays date according to time/date of Windows OS.

    I know you can just do a manual filter, but for some reason it proves to be challenge for some of our older salespeople.
    Thank you for your help.

    • Edited by jchakkalakal Saturday, October 15, 2011 11:31 PM
    • Moved by Mike Feng Monday, October 17, 2011 11:13 AM VBA (From:Visual Basic General)
    • Merged by danishaniModerator Tuesday, January 31, 2012 12:45 AM duplicate
    Saturday, October 15, 2011 11:31 PM
  • hi jchakkalakal,
     
    Range("B1").AutoFilter Field:=1, Criteria1:="<=" & Now - 21
     --
    isabelle
     
    Le 2011-10-15 19:12, jchakkalakal a écrit :
    > Spreadsheet that is used for delivery schedule. Column A is a list of locations, Column B is a list of date, both past due and for the upcoming 12 months.
    >
    > Macro Needed:- On any given day one would open the spreadsheet and hit a macro button that can filter column B to show delivery dates that are past due and delivery dates that are 3 weeks out, based on todays date according to time/date of Windows OS.
    >
    > I know you can just do a manual filter, but for some reason it proves to be challenge for some of our older salespeople.
    > Thank you for your help.
     
    Sunday, October 16, 2011 1:36 AM
  • I know you can just do a manual filter, but for some reason it proves to be challenge for some of our older salespeople.

    Exactly what do you mean by "dates that are 3 weeks out"? More than 3 weeks old (before today) or due in more than 3 weeks after today or due within the next 3 weeks. I am assuming the latter but need to be sure.

    Do you want "delivery dates that are past due and delivery dates that are 3 weeks out" to be displayed together or will they be displayed with separate filters?

    As you have said that you can do a manual filter, I suggest that you record the code to filter as required. Now depending on the date format in your region, the code might not run as required and produce the same results as when it was recorded but if you post the recorded code then I am sure that I or someone else will be able to edit the code and make it work for you.

    Recording code to do what you want is often a simple way of describing the situation.


    Regards, OssieMac
    Sunday, October 16, 2011 1:43 AM
  • This forum is for VB in Visual Studio Net.

    Try for VBA the VBA forum

    http://social.msdn.microsoft.com/Forums/en-US/isvvba


    Success
    Cor
    Sunday, October 16, 2011 9:06 AM
  • Hi jchakkalakal,

     

    Below code create an Autofilter with an InputBox for a DateRange, change the code to your needs accordingly:

    Sub test()
    
    Dim oWS As Worksheet
    Dim fromDate As Date
    Dim toDate As Date
    
    
    On Error GoTo Err_Filter
    
    Set oWS = ActiveSheet
    
    fromDate = InputBox("fromdate")
    toDate = InputBox("toDate")
    
    oWS.UsedRange.AutoFilter Field:=2, Criteria1:=">=" & fromDate, Operator:=xlAnd, Criteria2:="<=" & toDate
    
    CleanUp:
    
    If Not oWS Is Nothing Then Set oWS = Nothing
    
    Err_Filter:
    
    If Err <> 0 Then
    
    MsgBox Err.Number & " " & Err.Description
    
    Err.Clear
    
    GoTo CleanUp
    
    End If
    
    
    End Sub
    


    Hope this helps,

     


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    Tuesday, January 31, 2012 12:41 AM
    Moderator