1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Macro to filter conditionally

Discussion in 'Business Applications' started by Pinky2684, May 6, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. Pinky2684

    Pinky2684 Thread Starter

    Joined:
    Feb 13, 2010
    Messages:
    5
    Hi,

    I have a macro that updates status for a set of employees in a file from various reports.

    The macro then compares the value in 2 columns using a certain criteria and returns the value TRUE or FALSE.

    Once True or False is populated, i need to filter out false if any and do some analysis which is working fine.

    However, in case there is no false in that column it should proceed with the next analysis. But I am facing some issues in writing this code. Looking for some help here.. :)

    Have attached a sample file.. Now, Column C gets populated using the macro and the match is done in Column D. As u c, there is no false. So if i give filter criteria as false, it gives me an error.

    Is there some looping I can do, if there is no false then it should automatically go and update status2 column (Column E).

    Please help!!!!
     

    Attached Files:

  2. Pinky2684

    Pinky2684 Thread Starter

    Joined:
    Feb 13, 2010
    Messages:
    5
    I have a macro that updates the status of employees from a report with a vlookup. After this, the data in two columns in compared and a true or false is returned.

    Now, i need to filter out FALSE if any, and do some analysis. If there is a false, the macro is working fine. In case there is no false, it gets stuck there. Is there any way I can loop this.

    Attaching a sample file.

    The macro picks up column D data from a report and does a match in column E. As you can see, there is no false. The macro should filter FALSE, if any, else it should go to the next analysis (Move to column F).

    Please help!!!!!
     

    Attached Files:

  3. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    I see the file, I see the data but I don't get what you want to filter
    Since all the data is TRUE .....

    Can you be more specific?
    Macro? There is no macro in the workbook.
     
  4. turbodante

    turbodante

    Joined:
    Dec 19, 2008
    Messages:
    744
    Pinky, have you tried...
    Code:
    If WorksheetFunction.CountIf(Range("D2:D5"), False) = 0 Then
    'Do test: there is no false, go and update status2 column (Column E).
    End If
     
  5. EAFiedler

    EAFiedler Retired Moderator

    Joined:
    Apr 25, 2000
    Messages:
    14,172
    Hi Pinky2684

    I have merged your threads, please do not post duplicate threads.
    Continue replies for this issue in this thread.
    Thank you
     
  6. Pinky2684

    Pinky2684 Thread Starter

    Joined:
    Feb 13, 2010
    Messages:
    5
    Hi All,

    Thanks for the replies... :)

    Turbodante - Yes, i did try this. But the range is dynamic, it keeps changing everytime i run the macro. So, specifying a range like D2:D5 does not help. Is there anyway i can use a code like ActiveRange here.

    Keebellah - i havent incorportaed the macro in this file. It basically does a vlookup from a diff file and populates the data in column D. Then does a match in column E. Once the match is done, I want the macro to filter out FALSE, if any and continue with some analysis. Now, this is working jus fine.

    There could be cases where the match could return only TRUE and no FALSE. In such a case, I want the macro to proceed with the next analysis in column E. For eg : Goto Status2 (There is a seperate code written for this). Im unable to get this looping done. Hope im clear.
     
  7. turbodante

    turbodante

    Joined:
    Dec 19, 2008
    Messages:
    744
    How about this... it's dynamic

    Code:
    If WorksheetFunction.CountIf(Range("D2:D" & Cells(Rows.Count, 4).End(xlUp).Row), False) = 0 Then
    'Do test: there is no false, go and update status2 column (Column E).
    End If
    
     
  8. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Hi,

    I included turbodante's code and a button.
    It's a 2003 version but thta's no problem for the working.
     

    Attached Files:

  9. turbodante

    turbodante

    Joined:
    Dec 19, 2008
    Messages:
    744
    A nice touch keeballah
     
  10. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    I apologize for my intrusion
     
  11. turbodante

    turbodante

    Joined:
    Dec 19, 2008
    Messages:
    744

    not at all - refinments always welcome.(y)

    Just hope it solved the problem.
     
  12. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    thta's our goal anyway (y)
     
  13. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/921439

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice