none
What is the negation of Criteria2:= ? RRS feed

  • Question

  • Hello,

    I am using autofilter to filter a column in Excel:

                                       

    '// Option Button "Non-Store Items"
    '// Non-Store Items contain the  . plus 8  spaces plus .# string. The * are wildcards.



    ElseIf Option_NonStore = True Then
    Worksheets("Sheet1").Range("A2:A1000"). _
                       AutoFilter Field:=1, _
                       Criteria1:="*" & txtSearch.Value & "*", _
                       Operator:=xlAnd, _
                       Criteria2:=("*.        .#*")

    The above works well.

    Now, I want to display Store Items, when the Option Button "Store Items" is clicked

    The only difference is that Criteria2 should be the negation of above.

    Store Items don't contain this string-->             .        .#

    How do we do the negation?

    ----

    I found this solution on the web:

    Range.AutoFilter Field:=1, Criteria1:="=<>A", Operator:=xlOr, Criteria2:="=<>B"

    -------

    I tried the idea and modified my codes to:

    '// Option Button "Store Items"
    '// Store Items DON'T CONTAIN the . plus 8spaces plus .# string. The * are wildcards.
    ElseIf Option_NonStore = True Then
    Worksheets("Sheet1").Range("A2:A1000"). _
                       Criteria1:="*" & txtSearch.Value & "*", _
                       Operator:=xlAnd, _
                       Criteria2:=   " = <>     ("*.        .#*")          "   

    '// instead of:  

     '//               Criteria2:=                    ("*.        .#*")

    But it does not work:

    Compile Error:

    Expected: identifier or bracketed expression

    Any help much appreciated.

    Thanks

    Leon



    • Edited by Leon Lai Friday, November 29, 2019 9:24 AM
    Friday, November 29, 2019 8:36 AM

Answers

  • Hello, everybody

    I found the solution. Seems to work well:

    Criteria2:="  <>   *.        .#*              "

    Closing!

    Leon

    • Marked as answer by Leon Lai Friday, November 29, 2019 10:42 AM
    Friday, November 29, 2019 10:41 AM