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.

 

[Fluff]

How about

         .Value = .Parent.Evaluate(Replace(Replace("if(iserror(#),@,if((@=""Y"")*(#=""OUT""),""N"",@))", "@", .Address), "#", .Offset(, intBCOExcCol - .Column).Address))

 

[MAM8433]

It worked! I can't thank you enough. I checked the values afterwards and got the distributions I expected. I have never heard of Evaluate before, and I know it's not your job to teach me. But would you please describe the role of the @ sign, "-.column," and double quote usage when time permits? It looks as though * is acting as "and."

 

[Fluff]

Both the @ & # signs are place holders & the Replace function replaces them with the relevant address.
You're quite right about the * it's the array form of And.

 

[MAM8433]

I copied your code to filter nearby columns. And the numbers changed by 5 instead of the 20 I was expecting.
This may mean @ and # are still looking at intBCOCol and intBCOExcCol and not at intBCSCol and IntBCSExcCol.
I'm not sure how & when assigned values to the placemarkers. What do I need to do to reset these placemarkers?
Here's the section of code in its entirety:

If BCOwsName <> "" Then
   With Worksheets("Provider")
      With .Range(.Cells(8, intBCOCol), .Cells(intLastRowN, intBCOCol))
          .Value = .Parent.Evaluate(Replace(Replace("if(iserror(#),@,if((@=""Y"")*(#=""OUT""),""N"",@))", "@", .Address), "#", .Offset(, intBCOExcCol - .Column).Address))
      End With
   End With
End If
If BCSwsName <> "" Then
   With Worksheets("Provider")
      With .Range(.Cells(8, intBCSCol), .Cells(intLastRowN, intBCSCol))
          .Value = .Parent.Evaluate(Replace(Replace("if(iserror(#),@,if((@=""Y"")*(#=""OUT""),""N"",@))", "@", .Address), "#", .Offset(, intBCOExcCol - .Column).Address))
      End With
   End With
End If

It works without error; it's just not catching all of instances of intBCSCol = Y and IntBCSExcCol = OUT.

 

[Fluff]

You need to change intBCOExcCol to your new variable.

 

[MAM8433]

LOL. I neglected to scroll on over to the end of the line of code!
Fluff, this IS a thing of beauty! I'm going to research placeholders and Evaluate.

It works!!!!! Thank you! Thank you! Thank you!

 

[Fluff]

Glad to help & thanks for the feedback.