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

 

[Jonmo1]

That's a new issue, and probably deserves a new thread.
Maybe mention filtering by an array in it's title.

But just a quick look using the macro recorder, the criteria to filter that way needs to look like

Criteria1:=Array("Tool 1", " Tool 2", " Tool 3")

 

[rjplante]

I have edited my field within my look up table and when run the macro in step mode and stop after the filter is applied, I hover over the IFilterCell it looks like IFilterCell = "Tool 3", " Tool 4", " Tool 5" just like it should for an array set up, yet is doesn't filter using those parameters. When I manually apply the filters to the table, I should get 854 records. I don't know what is going wrong.

 

[Fluff]

Try

Dim IFilterCell, AFilterCell As Range


IFilterCell = Split(Sheets("List").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, Operator:=xlFilterValues

 

[rjplante]

Thanks Fluff! It is now working as intended. I will do some more rigorous testing and come back for more guidance if needed.

 

[Fluff]

Glad it's sorted & thanks for the feedback.