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
Not quite there yet.
Prior to running #20 code, the distribution of values in intBCOcol was:
N 218
Y 2204
#N/A 47

And the distribution of intBCOExcCol was:
Keep for BCO 151
OUT 87
#N/A 2231

I expected 5 records that were intBCOCiol = Y & and intBCOExcCol = OUT to switch to N.

While those 5 did switch to N, other rows flipped as well; intBCOCol now shows:
N 87
Y 151
#N/A 2231
When I look at the data, If intBCOExcCol = Keep for BCO, then intBCOCol= Y; if intBCOExcCol = OUT then intBCOCol= N, etc.
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,128
Office Version
  1. 365
Platform
  1. Windows
The code from post#20 does not change any cells other than those in the intBCOCol.
 

MAM8433

New Member
Joined
May 18, 2011
Messages
35
I reran Message 20 code and got the same results; intBCOCol numbers now match intBCOExcCol in
 

MAM8433

New Member
Joined
May 18, 2011
Messages
35
Fluff, given that some code is not working as expected, could my file be corrupted? I'm wondering if I should paste the code into a new file.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,128
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Are you sure the values aren't changing because of your formulae?
 

MAM8433

New Member
Joined
May 18, 2011
Messages
35
It's not, I stepped through the #20 code with F8 and watched the .value = evaluate line change the values in intBCOCol column.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,128
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

But that's what you wanted. :confused:
 

MAM8433

New Member
Joined
May 18, 2011
Messages
35
Where intBCOCol = Y and intBCOExcCol = OUT, I want to change IntBCOCol to N and change the adjacent cell to the right to "No Match." Currently, the code updates many of the Ys to #N/A because intBCOExcCol happens to equal #N/A.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,128
Office Version
  1. 365
Platform
  1. Windows
What if you try
VBA Code:
   With Worksheets("Provider")
      With .Range(.Cells(8, intBCOCol), .Cells(intLastRowN, intBCOCol))
         .Value = .Parent.Evaluate(Replace("if((@=""Y"")*(" & .Offset(, intBCOExcCol - .Column).Address & "=""OUT""),""N"",@)", "@", .Address))
      End With
   End With
 

MAM8433

New Member
Joined
May 18, 2011
Messages
35
No change. Y's in intBCOCol are overwritten with #NA if intBCOExcCol = #N/A. I notice that N's are also overwritten with #N/A unless the record meets the filter criteria. Then it's correctly changed to N.
 

Watch MrExcel Video

Forum statistics

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