VBA Autofilter Based on text in a Cell From Another Sheet

  • Hi guys, I Really need your help......


    So basically I want to do autofilter at MUFG Client sheet in "Keyword" field (Field 29) from another cell (I18) in Company Information sheet. And the content of the cell is the result from vlookup formula so it will change and not always be the same. Here goes my vba code:


    Code
    1. sub filter_by_cell_value ()
    2. Sheets ("MUFG Client").Range("A2").Autofilter Field:=29, _
    3. Criteria1:="=*" & Sheets("Company Information").Cells(18,6).Value & "*", xlOperator:= xlOr
    4. End Sub


    My objective is I want the autofilter can read the text in cell I18 without specific text/criteria.

    For example, if the cell I18 contains Cosmetics, Chemical --> I want the autofilter in Keyword Field can show the word Cosmetics or Chemical, then

    If I change the content of company information sheet into different company (The result of vlookup), the cell I18 in Company Information will change into Food & Beverage, Business Expansion, FMCG--> And I also want the autofilter in the keyword Field (MUFG Client sheet) shows Food & Beverage or Business Expansion or FMCG (Autofiltering that contains those words by ignoring order)


    And from my vba code above, Cells(18,6) is cell I18 in Company Information Sheet.


    Is it possible to do so? I think I have to discuss this directly to make you guys understand. Sorry if this makes misunderstanding.


    Thank you so much...

  • Welcome to Ozgrid.


    Please take a few minutes to read the Forum Rules to learn how to use the Forum properly. I have added Code Tags to your post the rules explain why and how. They also, explain how to add an example workbook which will help us understand your question.