R/t 448

[jim may]

Named Argument not found; What's wrong here?


Sub Filter_Stuff()
Sheets("Sheet1").AutoFilter Field:=2, Criteria1:=Sheets("Sheet1").Range("A1").Value, _
Field:=7, Criteria2:=Sheets("Sheet1").Range("B1"), Operator:=xlAnd
Sheets("Sheet2").AutoFilter Field:=2, Criteria1:=Sheets("Sheet1").Range("A1").Value, _
Field:=7, Criteria2:=Sheets("Sheet1").Range("B1"), Operator:=xlAnd
Sheets("Sheet3").AutoFilter Field:=2, Criteria1:=Sheets("Sheet1").Range("A1").Value, _
Field:=7, Criteria2:=Sheets("Sheet1").Range("B1"), Operator:=xlAnd
End Sub

 

[Colin Legg]

The autofilter method signature is:

expression.AutoFilter(Field, Criteria1, Operator, Criteria2, VisibleDropDown)

These are all optional parameters.

Your code uses:

expression.AutoFilter(Field, Criteria1, Field,  Criteria2, Operator)

 

[jim may]

Sorry Colin,
I'm unable to process what you provided.
Jim

 

[Colin Legg]

Hi Jim,

Sorry Colin,
I'm unable to process what you provided.
Jim

The autofilter method expects to be given one "field" argument but you have given it two. I am answering your question which was:

What's wrong here?

If you want me to show you how to fix it too then just ask... I assumed you were just looking for a hint as to the problem so you could have a bash at fixing it yourself.

 

[jim may]

"If you want me to show you how to fix it too then just ask"

Can you fix it?

Autofilters (to me) are the "jagged-rocks" along the coast line.

Jim

 

[Colin Legg]

Let's see if we can get some coastal erosion working on those jagged rocks.

The first thing that's being confused is that a Worksheet has an Autofilter property whereas a Range has an Autofilter method. They're quite distinct and do different things. The Worksheet.AutoFilter property returns a reference to an Autofilter object; the Range.AutoFilter method applies filtering.

In your code you are kind of trying to apply the Range.Autofilter method to a Worksheet rather than to a Range so Excel is going beserk. Since you already have the filters in place you could first amend your code to this:

    Sheets("Sheet1").AutoFilter.Range.AutoFilter _
                        Field:=2, _
                        Criteria1:=Sheets("Sheet1").Range("A1").Value, _
                        Field:=7, _
                        Criteria2:=Sheets("Sheet1").Range("B1").Value, _
                        Operator:=xlAnd

Sheets("Sheet1").AutoFilter gives us the autofilter object and we then apply the autofilter method to the autofilter's range.

This will fix the runtime error but there's a further problem....

The deal is that each column in the autofilter range is a "field" and each field has a dropdown box which is a "filter". When you use the Range.Autofilter method, you can apply multiple criteria (see in post #2 there are two criteria parameters called Criteria1 and Criteria2) to each field, but you can only do it to one field at a time (see in post #2 there is only one field criteria which is called Field) .

In your code, you are trying to do it to two fields at the same time. It can't handle that so it will disregard the Field:=7. To fix the problem you have to call the method multiple times: once for each field.

Sub Filter_Stuff()
 
    Sheets("Sheet1").AutoFilter.Range.AutoFilter Field:=2, Criteria1:=Sheets("Sheet1").Range("A1").Value
    Sheets("Sheet1").AutoFilter.Range.AutoFilter Field:=7, Criteria1:=Sheets("Sheet1").Range("B1").Value
 
    Sheets("Sheet2").AutoFilter.Range.AutoFilter Field:=2, Criteria1:=Sheets("Sheet1").Range("A1").Value
    Sheets("Sheet2").AutoFilter.Range.AutoFilter Field:=7, Criteria1:=Sheets("Sheet1").Range("B1").Value
 
    Sheets("Sheet3").AutoFilter.Range.AutoFilter Field:=2, Criteria1:=Sheets("Sheet1").Range("A1").Value
    Sheets("Sheet3").AutoFilter.Range.AutoFilter Field:=7, Criteria1:=Sheets("Sheet1").Range("B1").Value
 
End Sub

 

[jim may]

Colin,

This is the BEST explanation I've seen contrasting the WS AF property versus the AF Range Method; Thank you.. I've documented it so as to read it Over, and Over...
Much appreciated..

Jim

 

[Colin Legg]

You're welcome, Jim.

The code will have problems if (1) An autofilter isn't on each sheet or (2) Filtering is already being applied to an autofilter (I assume you would want to clear the filtering first) or (3) Worksheet protection prevents filtering so, if you're planning to deploy this to a client, you'll need to add some defensive coding to cater for these scenarios as necessary.