+ Reply to Thread
Results 1 to 8 of 8

Not getting output after filtering

  1. #1
    Forum Contributor
    Join Date
    06-29-2014
    Location
    India
    MS-Off Ver
    2010 Windows 10 64 bits
    Posts
    1,269

    Not getting output after filtering

    Hello friends,

    I want to filter the birthdays in the month from today.
    If there are no birthdays in the month from today to the end of month then I want to exit the sub with message.
    If it is a last day of month then I want to filter the birthdays of next month from today.
    If there are no birthdays in the next month from today to the end of month then I want to exit the sub with message.

    Problem:

    It's not filtering the birthdays and exiting the sub.

    Any help will be highly appreciated.

    Thanking you.
    Attached Files Attached Files
    Sincerely,

    mso3

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Not getting output after filtering

    Hi,

    It seems an unnecessarily complicated way of filtering a set of records when a straightforward single line Advanced Data Filter macro with a criteria range comprising two cells for >Today and <=end_of_month will do the job. See attached.

    The macro is in Module1

    Please Login or Register  to view this content.
    and I've added two range names for the Data and Criteria ranges.

    All you need to do is add a trivial bit of code to give you a message if no records are filtered.
    Attached Files Attached Files
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    12-14-2012
    Location
    Czech Republic
    MS-Off Ver
    Excel 2010
    Posts
    92

    Re: Not getting output after filtering

    Hi guys,

    Ok. Heres another approach (based on the origin). My code is in module 1. Just hit the button to run filter.
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    06-29-2014
    Location
    India
    MS-Off Ver
    2010 Windows 10 64 bits
    Posts
    1,269

    Re: Not getting output after filtering

    Hi Richard,

    Thank you. It's working fine but I didn't understand how to remove filter to get original data. There is a lot of data inn my workbook and I didn't understand how to modify it to meet my requirements.

    Will you please do the necessary amendments in my code post1 for my better understanding?

    If the data is found then I want message
    Please Login or Register  to view this content.
    and if the data is not found I don't want any action. At present the code auto filters and show 0 output.

    Sorry for trouble.

    Thank you and have nice day.

  5. #5
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Not getting output after filtering

    Here's the latest. It uses the following macro

    Please Login or Register  to view this content.
    You can run it by clicking ten Filter Data button. Click the other button to remove the filter.
    The important cells are P1:Q2 which are the selection criteria cells and S2 which counts the number of forthcoming birthdays in either the current or next month if today is the last day of a month.
    Attached Files Attached Files

  6. #6
    Forum Contributor
    Join Date
    06-29-2014
    Location
    India
    MS-Off Ver
    2010 Windows 10 64 bits
    Posts
    1,269

    Re: Not getting output after filtering

    Hi Richard,

    Thank you. I have made a little amendment in the code and names to meet my requirement. Now it's working fine but I didn't understand the difference between 'case1 and else'. Which range is for 'case1 and which range is for 'else'?
    If there are no birthdays then I want message 'which is working fine' and want to remove filter from 'birthday' column. Now it removes the filter but there is a dropdown arrow 'p7' 'birthdates'. How to remove it automatically?

    Thanking you.
    Attached Files Attached Files

  7. #7
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Not getting output after filtering

    Hi,

    You've changed my original. My original name "no_bdays" (number of birthdays) was a cell which worked out how many forthcoming birthdays there were irrespective of the filtered state of the list.

    The case 1 message was for when there was only 1 future birthday and hence said "There is 1 birthday in the current or next month".

    The case Else message was slightly different it used the word 'are' (plural) rather than 'is'. i.e. "There are " 3 birthdays in the current or next month"

    The arrow gets removed when you click the Remove Filter button.

  8. #8
    Forum Contributor
    Join Date
    06-29-2014
    Location
    India
    MS-Off Ver
    2010 Windows 10 64 bits
    Posts
    1,269

    Re: Not getting output after filtering

    Hi Richard,

    Thanks for explanation. Now I will use the code provided by you without any problem.


    Thank you and have a nice day.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Compare values and output cell titles, able to output multiple results
    By TMG2016 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 05-07-2016, 11:42 AM
  2. [SOLVED] Cell reference, output dates to numeric. Should output as text
    By lifeseeker1019 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-02-2015, 05:51 PM
  3. [SOLVED] 2003 macro to output several files thru filtering Pivot Table
    By tdbaileyco in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 02-21-2013, 10:28 AM
  4. Replies: 2
    Last Post: 12-15-2011, 11:49 PM
  5. Problems with filtering and output
    By floep in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-28-2006, 07:49 AM
  6. Problems with filtering and output
    By floep in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-27-2006, 10:50 AM
  7. Problems with filtering and output
    By floep in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-27-2006, 10:50 AM

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