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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,128
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
         .Value = .Parent.Evaluate(Replace(Replace("if(iserror(#),@,if((@=""Y"")*(#=""OUT""),""N"",@))", "@", .Address), "#", .Offset(, intBCOExcCol - .Column).Address))
 
Solution

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

MAM8433

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

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

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

VBA Code:
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

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

ADVERTISEMENT

You need to change intBCOExcCol to your new variable.
 

MAM8433

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

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

Watch MrExcel Video

Forum statistics

Threads
1,119,256
Messages
5,577,001
Members
412,760
Latest member
PetterL
Top