Text filter that contains a text

[LeanneBG]

Hi Experts! I know it sounds so simple.. but i cannot seem to find the answers.. I have below code that used to filter the exact "SELECTED_PRCS_PC_FLN", however, i found out that sometimes the data shows not only "SELECTED_PRCS_PC_FLN" but also this :SELECTED_PC_FLN". Is there a way to just change it to filter something that contains "PC_FLN" instead of the specific "SELECTED_PRCS_PC_FLN"? So that it can capture even those that are tagged as "SELECTED_PC_FLN" and others that have "PC_FLN" in its name.. Thanks in advance!

ActiveSheet.Range("$A$1:$L$1000").AutoFilter Field:=6, Criteria1:= _
        "SELECTED_PRCS_PC_FLN"

 

[LeanneBG]

To add... i have a code where it adds below formula, not sure how to adjust the code to put either SELECTED_PRCS_SCN_TGT or SELECTED_SCN_TGT

  "=IF(AND(INDEX(SRPT!C[3],MATCH(1,(INDEX(SRPT!C[-3],MATCH(RC[-2],SRPT!C[3],0))=SRPT!C[-3])*(""SELECTED_PRCS_SCN_TGT""=SRPT!C[2]),0))=MENU!R8C3),""TRUE"",""FALSE"")"
    Range("E2").FormulaArray = _

 

[Michael M]

Try using the wildcard

ActiveSheet.Range("$A$1:$L$1000").AutoFilter Field:=6, Criteria1:="*PC_FLN"

 

[LeanneBG]

Try using the wildcard

ActiveSheet.Range("$A$1:$L$1000").AutoFilter Field:=6, Criteria1:="*PC_FLN"

Thank you @Michael M, above code works, however, i think i still need to change those that are only tagged as "selected_prcs_tgt" into "Selected_prcs_scn_tgt" since i have formulas in my workbook that looks up the word "selected_prcs_scn_tgt". It is better if i filter all that has pc fln, and then replace all with this word "selected_prcs_scn_tgt" so i wont need to change all formulas..

 

[LeanneBG]

Sorry i was asking a question here.. is it a better approach to find/filter all that has pc fln and then replace with "selected_prcs_scn_tgt"? I tried recording the macro to do this however i am encountering an error whenever embedded in the code

 

[Michael M]

If you post the resat of the code, we may be able to do a REPLACE for the 2 items.
Are you then going to delete the rows with that entry ???

 

[LeanneBG]

If you post the resat of the code, we may be able to do a REPLACE for the 2 items.
Are you then going to delete the rows with that entry ???

Hi @Michael M, See code below. Apologies for the code.. i only tried recording it.. I have on error resume next per step cause sometimes there is nothing to replace since it is already correct.. and sometimes there's totally no data in the 2nd table.

Dim Last_Row As Long
ActiveSheet.AutoFilterMode = False


Sheets("SRPT").Select


Last_Row = Range("A" & Rows.Count).End(xlUp).Row


Range("A1:K1").Select
Selection.AutoFilter


''Loop thru First Table


ActiveSheet.Range("A2:K" & Last_Row).AutoFilter Field:=6, Criteria1:= _
        "SELECTED_PC_FLN"
        
    Columns("F:F").Select
 
    Selection.Replace What:="SELECTED_PC_FLN", Replacement:= _
        "SELECTED_PRCS_PC_FLN", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase _
        :=False, SearchFormat:=False, ReplaceFormat:=False
  
    ActiveSheet.ShowAllData
  
    ActiveSheet.Range("A2:K" & Last_Row).AutoFilter Field:=6, Criteria1:= _
        "SELECTED_MONTH_START"
        
    Columns("F:F").Select
    Selection.Replace What:="SELECTED_MONTH_START", Replacement:= _
        "SELECTED_PRCS_MONTH_START", LookAt:=xlPart, SearchOrder:=xlByRows, _
        MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
        
    ActiveSheet.ShowAllData
    
    ActiveSheet.Range("A2:K" & Last_Row).AutoFilter Field:=6, Criteria1:= _
        "SELECTED_SCN_TGT"
        
    Columns("F:F").Select
    Selection.Replace What:="SELECTED_SCN_TGT", Replacement:= _
        "SELECTED_PRCS_SCN_TGT", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase _
        :=False, SearchFormat:=False, ReplaceFormat:=False
    
    ActiveSheet.AutoFilterMode = False
    
    Range("A1").Select
    
  '''Loop thru 2nd Table


  
ActiveSheet.AutoFilterMode = False


Sheets("SRPT").Select


Last_Row = Range("M" & Rows.Count).End(xlUp).Row


Range("M1:S1").Select
Selection.AutoFilter


On Error Resume Next


ActiveSheet.Range("M2:S" & Last_Row).AutoFilter Field:=17, Criteria1:= _
        "SELECTED_PC_FLN"
 
On Error GoTo 0
    Columns("Q:Q").Select
 
    Selection.Replace What:="SELECTED_PC_FLN", Replacement:= _
        "SELECTED_PRCS_PC_FLN", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase _
        :=False, SearchFormat:=False, ReplaceFormat:=False
  On Error Resume Next
    ActiveSheet.ShowAllData
  On Error GoTo 0
  On Error Resume Next
    ActiveSheet.Range("M2:S" & Last_Row).AutoFilter Field:=17, Criteria1:= _
        "SELECTED_MONTH_START"
  On Error GoTo 0
    Columns("Q:Q").Select
  On Error Resume Next
    Selection.Replace What:="SELECTED_MONTH_START", Replacement:= _
        "SELECTED_PRCS_MONTH_START", LookAt:=xlPart, SearchOrder:=xlByRows, _
        MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
  On Error GoTo 0
    On Error Resume Next
    ActiveSheet.ShowAllData
    On Error GoTo 0
    On Error Resume Next
    ActiveSheet.Range("M2:S" & Last_Row).AutoFilter Field:=17, Criteria1:= _
        "SELECTED_SCN_TGT"
     On Error GoTo 0
    On Error Resume Next
    Columns("Q:Q").Select
    On Error GoTo 0
        On Error Resume Next
    Selection.Replace What:="SELECTED_SCN_TGT", Replacement:= _
        "SELECTED_PRCS_SCN_TGT", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase _
        :=False, SearchFormat:=False, ReplaceFormat:=False
     On Error GoTo 0
    ActiveSheet.AutoFilterMode = False


 
    Range("A1").Select
   


    
End Sub

 

[Michael M]

Maybe like this

Sub MM1()
Dim lr As Long
Sheets("SRPT").Select
lr = Range("A" & Rows.Count).End(xlUp).Row
    With Columns("F1:F" & lr)
    .Replace "SELECTED_PC_FLN", "SELECTED_PRCS_PC_FLN"
        .Replace "SELECTED_MONTH_START", "SELECTED_PRCS_MONTH_START"
        .Replace "SELECTED_SCN_TGT", "SELECTED_PRCS_SCN_TGT"
    End With
lr = Range("M" & Rows.Count).End(xlUp).Row
    With Columns("Q:Q" & lr)
        .Replace "SELECTED_PC_FLN", "SELECTED_PRCS_PC_FLN"
        .Replace "SELECTED_MONTH_START", "SELECTED_PRCS_MONTH_START"
        .Replace "SELECTED_SCN_TGT", "SELECTED_PRCS_SCN_TGT"
    End With
End Sub

 

[LeanneBG]

Maybe like this

Sub MM1()
Dim lr As Long
Sheets("SRPT").Select
lr = Range("A" & Rows.Count).End(xlUp).Row
    With Columns("F1:F" & lr)
    .Replace "SELECTED_PC_FLN", "SELECTED_PRCS_PC_FLN"
        .Replace "SELECTED_MONTH_START", "SELECTED_PRCS_MONTH_START"
        .Replace "SELECTED_SCN_TGT", "SELECTED_PRCS_SCN_TGT"
    End With
lr = Range("M" & Rows.Count).End(xlUp).Row
    With Columns("Q:Q" & lr)
        .Replace "SELECTED_PC_FLN", "SELECTED_PRCS_PC_FLN"
        .Replace "SELECTED_MONTH_START", "SELECTED_PRCS_MONTH_START"
        .Replace "SELECTED_SCN_TGT", "SELECTED_PRCS_SCN_TGT"
    End With
End Sub

Hi @Michael M, thanks for the code, however, it is not working nothing is happening everytime i run the macro, it is not changing/replacing the words..

 

[Michael M]

Ok, the REPLACE is acting on columns "F" & "Q", is that correct ?
You are working on the correct sheet ?
Are all the texts in UPPER case ?