Autofilter with Variables

[MAM8433]

I want to filter the column that contains BCO data to “yes” and the BCO_Excl column to “out.” This code was working and now doesn’t, no doubt due to numerous edits.

Because the exact location of the BCO data is not static, variable intBCOCol contains the column number (21 in my case). Likewise, intBCOExcCol contains the BCO_Excl column number (its value is 25).

In my file, the column headers are in row 8. IntLastRowN is the number of the last data row (which is 2477); intLastCol contains the last used column (column 26). My code is:

With Worksheets("Provider").Range(Cells(8, 1), Cells(intLastRowN, intLastCol))

.AutoFilter Field:=intBCOCol, Criteria1:="Y"

.AutoFilter Field:=intBCOExcCol, Criteria1:="OUT"

End With



The code chokes on the second line. The message is: Run-time error ‘1004’: Autofilter method of range class failed.” However when I hover over intBCOCol, it shows that the value is 21, as expected.

Does this mean that the first line of code is incorrect? I read that variables can be used in cell addresses but not in ranges. I hope you can help me as the more I research these issues, the more confused I get.

 

[MAM8433]

Hello. The first line gives me a run-time 9 error. I changed the sheet name to the code name and With Sheets(Sheet1).ListObjects("Claims") generates a run-time 13 error (type mismatch).

 

[Fluff]

In that case it sounds as though the data is not in a structured table.
When in Name manager is the icon against Claims like the 1st or 2nd of these icons

 

[MAM8433]

Fluff: you are a genius. I cannot explain why it wouldn't work before, but this code works when I tried it today:

With Worksheets("Provider")
With .Range(.Cells(8, 1), .Cells(intLastRowN, intLastCol))
.AutoFilter Field:=intBCOCol, Criteria1:="Y"
.AutoFilter Field:=intBCOExcCol, Criteria1:="OUT"
End With
End With

I will try your other recommendation (with the listobject and listcolumns) when I code my second filter activity. Thank you!

 

[Fluff]

Glad you sorted it & thanks for the feedback.

 

[MAM8433]

I'm glad I can still append to this thread! I reported that this code worked a couple of days ago, but it's not running again as of today.

With Worksheets("Provider")
With .Range(.Cells(8, 1), .Cells(intLastRowN, intLastCol))
.AutoFilter Field:=intBCOCol, Criteria1:="Y"
.AutoFilter Field:=intBCOExcCol, Criteria1:="OUT"
End With
End With

It's back to crashing on line: .AutoFilter Field:=intBCOCol, Criteria1:="Y" and messaging "run-time error 1004: AutoFilter method of Range class failed."

At first it didn't run, then it did, and now it doesn't. What am I doing that keeps this code from running consistently? When I hovered over Cells(intLastRowN, intLastCol)), it indicated “Cells(intLastRowN, intLastCol)=Error 2042.” Should I recode the #N/As that are in the intBCOCol column? Or is something else getting interfering?

Also, I tried option 2, and got a run-time error '9': subscript out of range on the first line:

With Sheets("Provider").ListObjects("Claims")
.Range.AutoFilter .ListColumns("BCO Match").Index, "Y"
.Range.AutoFilter .ListColumns("BCO Excl Hosp").Index, "OUT"
End With

I'm guessing that it doesn't recognize "Claims." Any ideas on how to correct my code?

 

[Fluff]

Can you answer my question from post#12?
Also when it fails, what is the value of these variables
intLastRowN
intLastCol
intBCOCol
intBCOExcCol

 

[MAM8433]

The icon next to Claims looks like your first example.; row 2 cells 1 and 2 are blue. When it fails, the values correctly store:
intLastRowN =2477
intLastCol = 26
intBCOCol = 21
intBCOExcCol = 25

 

[Fluff]

The icon next to Claims looks like your first example.; row 2 cells 1 and 2 are blue.

Ok that means it's a named range & not a table.

I can think of no reason why it would work sometimes & not others.
The error 2042 suggests that cell has #N/A in it, but that should not stop the code.

 

[MAM8433]

Right, there are #N/As in BCOExcCol (it contains vlookup values), and they were there when the code ran successfully.

Is there another way to filter? I haven't used advancedfilter.

My goal is to filter, change the visible values in the intBCOCol column to "N," and change the value of the cells in the column 22, one column to the right of the filtered column.

In the meantime, I will review my code.

 

[Fluff]

How about

   With Worksheets("Provider")
      With .Range(.Cells(8, intBCOCol), .Cells(intLastRowN, intBCOCol))
         .Value = Evaluate(Replace("if((@=""Y"")*(" & .Offset(, intBCOExcCol - .Column).Address & "=""OUT""),""N"",@)", "@", .Address))
      End With
   End With