Announcement

Collapse
No announcement yet.

Autofilter Macro according to TRUE / FALSE criteria

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Autofilter Macro according to TRUE / FALSE criteria



    Hi,

    I have written a macro which is supposed to look at sheet12 and at certain cells and depending on the value - true or false, then turns on an autofilter - code as below:

    Code:
    Sub FilterCriteria()
    With ActiveWorkbook
    
    If Sheet12.Range("C6").Value = "TRUE" Then Sheet2.Range("A1:AI1").AutoFilter Field:=1, Criteria1:="Y"
    If Sheet12.Range("C7").Value = "FALSE" Then Sheet2.Range("A1:AI1").AutoFilter Field:=11, Criteria1:="="
    
    End With
    End Sub
    'IF SHEET12, CELL C6 VALUE IS "TRUE" THEN FILTER "Y" FROM SHEET2, COLUMN 1
    'IF SHEET12, CELL C7 VALUE IS "FALSE" THEN FILTER BLANKS FROM SHEET2, COLUMN 11

    When trying to run this macro, it doesnt work. the part - Sheet2.Range("A1:AI1").AutoFilter Field:=1, Criteria1:="Y" works when I seperate it, but it doesnt seem to work with the If statement.

    When I try to upload my file, nothing happens. I will try again later

    Any help appreciated!
    Ross
    Last edited by Rossce; April 28th, 2012, 06:27. Reason: My file attachment wont upload - its well within its size

  • #2
    Re: Autofilter Macro according to TRUE / FALSE criteria

    here is my file
    Attached Files

    Comment


    • #3
      Re: Autofilter Macro according to TRUE / FALSE criteria

      Hi Rossco

      Use the following. It seemed to work OK when I added the compare text line at the top and the option explicit should be on your workbook as well to help with error trapping and to ensure all variables are declared.

      Take care

      Smallman

      Code:
      Option Explicit
      Option Compare Text
      
      Sub FilterCriteriasm()
               Sheet2.AutoFilterMode = False 'Clear filter
      
              If Sheet12.Range("C6").Value = "TRUE" Then
                  Sheet2.Range("A1:AI1").AutoFilter 1, "Y"
              ElseIf Sheet12.Range("C7").Value = "FALSE" Then
                  Sheet2.Range("A1:AI1").AutoFilter 11, "="
              End If
      End Sub
      sigpicthesmallman.com .......... Excel Dashboards ............ Excel Infographicsg..........k

      Comment


      • #4
        Re: Autofilter Macro according to TRUE / FALSE criteria

        Hi Smallman, Thanks for your reply.

        I have tried the new code but I'm not sure what I could be doing wrong as I only got it to run corretly once?
        If I change some of the cells in Sheet2, column 11 to "Y", the macro didnt filter them out with the blanks? Also when I changed TRUE to FALSE on Sheet12, cell C6, the macro then just removed the filter in row 1 rather than apply what the macro asked - which would be to identify that the cell value is not TRUE and move on...

        Any ideas?

        Im wondering if its to do with the else if? I need to macro to look at the first criteria (Sheet12, cell C6) and say if this is TRUE then filter the Y on column 1, if not then do nothing, then move on to look at Sheet12 cell C7 and say if this is FALSE, then filter column 11 to blanks, if not then do nothing.

        Im only working with these 2 criteria, which could change from true to false, also I may need to add some more filter criteria into this as per Sheet12 cel C8, C9 and possibly C10 over the nex month.

        I want to use this file at work in 2003 but am currently using it on 2010 - maybe thats why its not filtering right?

        Thanks in advance

        Rossce

        Comment


        • #5
          Re: Autofilter Macro according to TRUE / FALSE criteria

          You've cross posted this.
          Hope that Helps

          Roy

          New users should read the Forum Rules before posting

          For free Excel tools & articles visit my web site

          If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

          RoyUK's Web Site

          royUK's Database Form

          Where to paste code from the Forum

          About me.

          Comment


          • #6
            Re: Autofilter Macro according to TRUE / FALSE criteria

            Have I? Well,I tried to find my original post but on the latest started thread kept taking me to a post in January I started so I thought a moderator may have deleted my post as I didnt get my attachment on quick enough - hence this post.

            Comment


            • #7
              Re: Autofilter Macro according to TRUE / FALSE criteria

              Hi Smallman,

              I made a small adjustment taking out the Else if to just if and it runs fine now :-) Thanks!

              Code:
              Option Explicit
              Option Compare Text
              Sub FilterCriteriasm()
                  Sheet2.AutoFilterMode = False 'Clear filter
                   
                  If Sheet12.Range("C6").Value = "TRUE" Then
                      Sheet2.Range("A1:AI1").AutoFilter 1, "Y"
                  End If
                  If Sheet12.Range("C7").Value = "FALSE" Then
                      Sheet2.Range("A1:AI1").AutoFilter 11, "="
                  End If
              End Sub

              Comment


              • #8
                Re: Autofilter Macro according to TRUE / FALSE criteria

                If you had read the Forum Rules at either Forum then you would understand about crossposting

                Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

                Cross posted here
                Hope that Helps

                Roy

                New users should read the Forum Rules before posting

                For free Excel tools & articles visit my web site

                If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

                RoyUK's Web Site

                royUK's Database Form

                Where to paste code from the Forum

                About me.

                Comment


                • #9
                  Re: Autofilter Macro according to TRUE / FALSE criteria

                  Hi Rosco

                  I don't know how you managed to get the code I provided not to work and to make two if statements where one is adequate. Here is the code unaltered on your file.

                  Take care

                  Smallman
                  Attached Files
                  sigpicthesmallman.com .......... Excel Dashboards ............ Excel Infographicsg..........k

                  Comment


                  • #10
                    Re: Autofilter Macro according to TRUE / FALSE criteria

                    Originally posted by royUK View Post
                    If you had read the Forum Rules at either Forum then you would understand about crossposting

                    Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

                    Cross posted here
                    Im not quite sure what you are trying to tell me. So cross posting is where you post the question on other parts of the forums. I understand that but Ive posted this here...only here? So I dont understand why im being targetted?
                    Ive checked back through my "find recent threads started by me" and as I meantioned before, the original posted thread for this question had been removed henc why I posted it back on.

                    Rossce

                    Comment


                    • #11
                      Re: Autofilter Macro according to TRUE / FALSE criteria

                      Originally posted by Smallman View Post
                      Hi Rosco

                      I don't know how you managed to get the code I provided not to work and to make two if statements where one is adequate. Here is the code unaltered on your file.

                      Take care

                      Smallman
                      Hi Smallman,

                      I have downloaded your attachment, but still cannot get this to work. If I put TRUE in sheet12, cell C6, it should filter out column 1, the "Y". Then with that filter still on, will search sheet12, cell C7, see it says FALSE, then go back and filter out the blanks in column 11.

                      For example, if you type in "Y" in column 11 - rows 2 - 9, and type TRUE in Sheet12, cell C6, when you run the macro, it should first filter column 1 "Y" then go filter column 11, Blanks...you should be left with1 visible row.

                      Hope that makes sense

                      Rossce

                      Comment


                      • #12
                        Re: Autofilter Macro according to TRUE / FALSE criteria

                        Hi Rosco

                        I thought your problem was an either or. If it was true in C6 then apply one filter otherwise apply another. You are talking about adding an AND condition and this will include both conditions. The following will cater for both conditions. Paste over the top of prior workbook code.

                        Take care

                        Smallman

                        Code:
                        Sub FilterCriteriasm()
                            Sheet2.AutoFilterMode = False 'Clear filter
                             
                            If Sheet12.Range("C6").Value = "TRUE" And Sheet12.Range("C7").Value = "FALSE" Then
                                Sheet2.Range("A1:AI1").AutoFilter 1, "Y"
                                Sheet2.Range("A1:AI1").AutoFilter 11, "="
                            End If
                        End Sub
                        sigpicthesmallman.com .......... Excel Dashboards ............ Excel Infographicsg..........k

                        Comment


                        • #13


                          Re: Autofilter Macro according to TRUE / FALSE criteria

                          Thanks Smallman,

                          Yep, that has worked treat - thank you for your time and help :-)

                          Rossce

                          Comment

                          Working...
                          X