Filter on cell reference

[rjplante]

I am trying to set up a filter that will use the text from a cell to filter on. I have a user form that the user will select the product to filter for. This is recorded in cell EG1. I have a vlookup formula in cell EG2 that displays the info in the second column of a table with the filter criteria. If the user selects product A, the cell will display, "Tool 1", "Tool 2", "Tool 3" (with quotes and commas). This is the format of the criteria section of the filter in some of my previous filters macros. I have those hard coded, but this is using a vlookup and a cell reference to execute the filter. When I run it, it does not apply the filter to the column and I get no rows returned. What am I missing?

Dim IFilterCell, AFilterCell As Range


IFilterCell = Sheets("Investigation Tasks").Range("EG2").Value


    'filter for Status - Investigation is New  (Column E = 5)
    ActiveSheet.Range("$A$2:$AL2" & lastrow).AutoFilter Field:=5, Criteria1:="New"
    
    '   Filter for Selected product (Column Q = 17)
    ActiveSheet.Range("$A$2:$AL2" & lastrow).AutoFilter Field:=17, Criteria1:=IFilterCell

Thanks for any help to get this working.

Robert

 

[Fluff]

Try

ActiveSheet.Range("$A$2:$AL2" & lastrow).AutoFilter Field:=17, Criteria1:=IFilterCell, Operator:=xlFilterValues

 

[rjplante]

When I tried this solution, it did not fix my problem. Still will not apply filter parameters to the designated column. I did check the columns and it is correct.

 

[Jonmo1]

Your original code works fine for me.

When I run it, it does not apply the filter to the column and I get no rows returned.

This means the filter WAS in fact applied. But no rows met the criteria of that filter.

I would say there are no cells in the 17th column (Column Q) that match the value in Sheets("Investigation Tasks").Range("EG2")
At least not in the same rows where column E = New.

 

[rjplante]

When I go to column 17, the filter is not applied to the list. I can check the box next to the desired option and get 3064 records that match my defined parameters.

 

[Jonmo1]

I'm not clear on what exactly 'not working' is....

Take it one step at a time.
Step through the code by pressing F8 and observe the results as it goes.

It first filters column 5 for "New"
Does that work ? Do you see only rows with New in the 5th column (Column E) ?


Then proceed to filter column 17 on IFilterCell
Do the rows previously shown with New in column E STILL show, or do they disappear ?

 

[rjplante]

When I step through the code the first filter for "New" items is applied correctly. When I get to the next line where it should filter for "Tool 1" (the value in cell EG2) it fails and returns 0 rows instead of 3064 rows as expected.

 

[Jonmo1]

When I get to the next line where it should filter for "Tool 1" (the value in cell EG2) it fails and returns 0 rows instead of 3064 rows as expected.

Does this mean the rows shown in the filter for New are no longer visible ?
If yes, that means the 2nd filter WAS applied, but none of those rows matched the your variable IFilterCell in column Q

Are you sure the variable IFilterCell actually contains the correct value ?
F8 it again, and after the line that assigns a value to IFilterCell, hover your mouse over that variable to see what value it is given.

 

[rjplante]

Thanks for the hover trick, it was applying the filter term ""Tool 1"" into the array. When I changed my cell value to just Tool 1 without quotes, it worked. The I ran into problem two. In some of the cells in my lookup table, I have multiple values I would like filtered on (Tool 3, Tool 4. Tool 5) When I enter it without quotes around each item, the filter gets applied as "Tool 3, Tool 4, Tool 5". The I tried to change the text in my table to read Tool 3", "Tool 4", "Tool 5 in which case VBA would add quotes around the outside and complete the array format. This did not work. The filter looks correct when I hover over the IFilterCell block. It reads "Tool 3", "Tool 4", "Tool 5" which is what the array should look like in the filter. I should have 612 rows and I get 0 after this is applied. Getting even more confused.

 

[rjplante]

opps dup post