# 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

You should post all the code and state exactly what you want to do, everything, not just what you want to add.

Bruce :cool:

• 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"")"
• 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"""

Bruce :cool:

• Re: Advance filter with 3 variables

Thanks got stuck on the third variable. Nice and clean.

Billyrj

• Re: Advance filter with 3 variables

My pleasure, thanks for the feedback. :cool:

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"""

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:

• Re: Advance filter with 3 variables

My pleasure, thanks for the feedback. :cool:

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"")"