+ Reply to Thread
Results 1 to 7 of 7

Auto Filter on the next business date

  1. #1
    Forum Contributor
    Join Date
    08-12-2010
    Location
    Excel World
    MS-Off Ver
    Excel 2013, 2019 & O365
    Posts
    214

    Talking Auto Filter on the next business date

    Hi All,

    I have a worksheet with huge data, where a column has many dates, and I have to filter & copy the data according to the next working date.
    I've written below codes, but that works for the next date instead of the next business date. What if some other day is official holiday, then the data should be auto filtered as per the next business date/day (Monday to Friday).

    How can I have a userform, which will ask the date in a specific format for auto filtering the data, then perform the written codes on that date in the worksheet? I think this is how I would get the accuracy all the time without any wrong inputs.

    Here below, if I use a date as 05/15/2011 or 05/15/11 instead of 5/15/2011 then auto-filter doesn't work properly. so I need a correct format of the input date.

    Please Login or Register  to view this content.

    Please see the 'userform' in the VBA code window (ALT+F11). I am sure that someone will come up with the solution & help me out.

    Workbook: MS Excel 2007

    Thanks in advance!
    Attached Files Attached Files
    Last edited by SunOffice; 05-23-2011 at 04:09 AM.
    Excelforum is Completely Awesome! True learning with Live Examples & Best Techniques!!

  2. #2
    Valued Forum Contributor Charles's Avatar
    Join Date
    02-10-2004
    Location
    Biloxi
    MS-Off Ver
    Windows 7, Excel 2003,2007 & Mac2011
    Posts
    845

    Re: Auto Filter on the next business date

    Hi,

    Here is some code for the "NextBusDay"
    Pace it in the userform code module.

    Please Login or Register  to view this content.
    For the Holidays you need to know what the Holidays are and in some way reference to it.
    I hope this code will help.
    Charles

    There are other ways to do this, this is but 1 !
    Be Sure you thank those who helped.
    IF YOU'RE SATISFIED BY ANY MEMBERS RESPONSE TO YOUR ISSUE PLEASE USE THE STAR ICON AT THE BOTTOM LEFT OF THE POST UNDER THEIR NAME.

  3. #3
    Forum Contributor
    Join Date
    08-12-2010
    Location
    Excel World
    MS-Off Ver
    Excel 2013, 2019 & O365
    Posts
    214

    Re: Auto Filter on the next business date

    Thanks for ur quick reply!

    If you see the above code, where I get the solution through an InputBox. I have to provide it a specific future date, and it works fine, BUT I need to do run the above activity through a userform. I have to write some additional information on the userform, which can't be displayed on an InputBox msg.

    In the userform, if somebody writes date in any format, then the date format should be the same as the dates in the worksheet for auto-filter


    ...so please help me out. If you see below the attached you can understand the userform.

    Thanks in advance!
    Last edited by SunOffice; 05-15-2011 at 07:10 AM.

  4. #4
    Valued Forum Contributor Charles's Avatar
    Join Date
    02-10-2004
    Location
    Biloxi
    MS-Off Ver
    Windows 7, Excel 2003,2007 & Mac2011
    Posts
    845

    Re: Auto Filter on the next business date

    SunOffice,

    Check this attachment. I removed the Properties for textbox1. and too the code for textbox1 click. I left the code in Module 1 but moved and modified it for the userform "Ok" button. I tested it and it worked for me.

  5. #5
    Forum Contributor
    Join Date
    08-12-2010
    Location
    Excel World
    MS-Off Ver
    Excel 2013, 2019 & O365
    Posts
    214

    Re: Auto Filter on the next business date

    Thanks for your efforts and support for me! You are such a great personality.

    I'm so thankful to you for understanding my need of the vba codes.
    I just tried ur codes, but somewhere it is missing the data. Sometimes it misses the values to pull-out and copy. If you run the my codes(input box method), are running well, BUT I think the DATE format should be taken care before all of the activity.

    You can see, where I am getting problem, If today is 05/20/2011 then if I run the codes via userform for a future date:
    - doesn't perform 'No Response' activity for each defined future date.
    - If I don't provide any dates in the userform, then also it runs on clicking 'OK'.
    - If I write date as 05/21/11, then the codes run, but doesn't work properly.
    - Everytime I would like to display the current date in the userform, so whenever one runs the codes; the userform should be loaded with the current date in the Textbox (like the inputbox shows the current date at the time of asking the future date)

    Request you to please provide me the solutions.

    Thanks in advance!
    Last edited by SunOffice; 05-19-2011 at 07:46 PM.

  6. #6
    Valued Forum Contributor Charles's Avatar
    Join Date
    02-10-2004
    Location
    Biloxi
    MS-Off Ver
    Windows 7, Excel 2003,2007 & Mac2011
    Posts
    845

    Re: Auto Filter on the next business date

    SunOffice,

    I modified the code. It now shows the current date in the text box and too when I ran your code and the code for the Option button the data displayed in the "GetData" sheet are the same. The only thing I changed was set the Text box to todays date and I removed the "Userform.Hide" in the Sub for the CommandButton1 click.

  7. #7
    Forum Contributor
    Join Date
    08-12-2010
    Location
    Excel World
    MS-Off Ver
    Excel 2013, 2019 & O365
    Posts
    214

    Re: Auto Filter on the next business date

    Yeah I just edited the format of dates in the userform code, and it's also working fine for me.... WOW!!

    Thanks a world Mr Charles, you are really a genius.

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1