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]

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.

 

[Fluff]

The code from post#20 does not change any cells other than those in the intBCOCol.

 

[MAM8433]

I reran Message 20 code and got the same results; intBCOCol numbers now match intBCOExcCol in

 

[MAM8433]

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]

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

 

[MAM8433]

It's not, I stepped through the #20 code with F8 and watched the .value = evaluate line change the values in intBCOCol column.

 

[Fluff]

But that's what you wanted.

 

[MAM8433]

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]

What if you try

   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]

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.