Advance filter with 3 variables

  • I've been using some code for years and am struggling with adding the third variable.
    I work with 4 digit codes 7200 or 6200. The code I use looked previously worked well looking at the two middle digits. I now need to include the first digit.


    Code
    rngCriteria.Cells(2, 6).Formula = "=AND(mid(C5,2,1)=""2"",mid(C5,3,1)=""0"")"
  • Re: Advance filter with 3 variables


    Thanks below is the complete code. I currently filter and distribute based on the digits 2 and 3. I would like to filter and distribute via the digits 1, 2 and 3. The current code posted shows filter by digits "2" and "0". My desire is to filter by digits "6", "2" and "0".


    Code
    strSht = "6200"
        Sheets(strSht).Range("A2:N1000").ClearContents
        Set wsDest = Sheets(strSht)
        Set rngDest = wsDest.Range("A1:N1")
        rngCriteria.Cells(2, 6).Formula = "=AND(mid(C5,2,1)=""2"",mid(C5,3,1)=""0"")"
        rngToFilter.AdvancedFilter xlFilterCopy, rngCriteria, rngDest, False
  • Re: Advance filter with 3 variables


    It's currently looking for the 2nd. digit to be 2 and the 3rd. digit to be 0.


    So I assume from your first post this will stay the same, but you want to check for 6 as the 1st. digit?


    So 620 or any number that begins with 620?


    Maybe this:

    Code
    strSht = "6200"
    Sheets(strSht).Range("A2:N1000").ClearContents
    Set wsDest = Sheets(strSht)
    Set rngDest = wsDest.Range("A1:N1")
    rngCriteria.Cells(2, 6).Formula = "=LEFT(C5,3)=""620"""
    rngToFilter.AdvancedFilter xlFilterCopy, rngCriteria, rngDest, False

    Bruce :cool:

  • Re: Advance filter with 3 variables


    I found that I may have created and additional problem. so trying to modify to and if OR statement. The code below is clean but need to include an additional statement.


    Code
    strSht = "6200" 
    Sheets(strSht).Range("A2:N1000").ClearContents 
    Set wsDest = Sheets(strSht) 
    Set rngDest = wsDest.Range("A1:N1") 
    rngCriteria.Cells(2, 6).Formula = "=LEFT(C5,3)=""620""" 
    rngToFilter.AdvancedFilter xlFilterCopy, rngCriteria, rngDest, False


    I modified but am missing something.

    Code
    rngCriteria.Cells(2, 6).Formula = "=IF(OR(LEFT(C5,3)=""620"",LEFT(C5,3)=""720"""


    Appreciate the help

  • Re: Advance filter with 3 variables


    If you want the results of the the formula to simply be TRUE or FALSE, then we don't need the IF part.


    We want TRUE if C5 has anything that begins with 620 or 720 so we simply use OR.


    Code
    rngCriteria.Cells(2, 6).Formula = "=OR(LEFT(C5,3)=""620"",LEFT(C5,3)=""720"")"

    Bruce :cool:

  • I am attemptiong to modify and existing code and have ran into some problems. I want to look into D5 and I have 3 or statements.
    If D5 CAT move to kennel
    if D5 DOGS move to kennel
    IF D5 BIRD and B5 is S* move to kennel
    The existing formula has worked for a while and desired to utilize the third variable with not success. Thanks


    HTML
    strSht = "Kennel"
        Sheets(strSht).Range("A2:S65536").ClearContents
        Set wsDest = Sheets(strSht)
        Set rngDest = wsDest.Range("A1:S1")
        rngCriteria.Cells(2, 6).Formula = "=OR(LEFT(D5,3)=""CAT"",LEFT(D5,4)=""DOGS"")"
        rngToFilter.AdvancedFilter xlFilterCopy, rngCriteria, rngDest, False

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!