Autofilter with Variables

MAM8433

New Member
Joined
May 18, 2011
Messages
35
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

New Member
Joined
May 18, 2011
Messages
35
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).
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,128
Office Version
  1. 365
Platform
  1. Windows
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
1605111781623.png
 

MAM8433

New Member
Joined
May 18, 2011
Messages
35
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,128
Office Version
  1. 365
Platform
  1. Windows
Glad you sorted it & thanks for the feedback.
 

MAM8433

New Member
Joined
May 18, 2011
Messages
35

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,128
Office Version
  1. 365
Platform
  1. Windows
Can you answer my question from post#12?
Also when it fails, what is the value of these variables
intLastRowN
intLastCol
intBCOCol
intBCOExcCol
 

MAM8433

New Member
Joined
May 18, 2011
Messages
35

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,128
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
May 18, 2011
Messages
35
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,128
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
   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
 

Watch MrExcel Video

Forum statistics

Threads
1,119,255
Messages
5,576,994
Members
412,759
Latest member
Jackuk127
Top