Adapt code to search partial text, case find put result column AT

marreco

Well-known Member
Joined
Jan 1, 2011
Messages
607
Hi.

Code:
Sub myFilter2()'Coded by RickXL
'https://www.mrexcel.com/forum/excel-questions/917998-wild-card-unable-use-autofilter-array.html
    Dim dic     As Object
    Dim eleData As Variant
    Dim eleCrit As Variant
    Dim arrData As Variant
    Dim vTst    As Variant


    Set dic = CreateObject("Scripting.Dictionary")
    vTst = Array("*Cancel*", "*cancel*")
    
    With ActiveSheet
        .AutoFilterMode = False
        arrData = .Range("E2:E" & .Cells(.Rows.Count, "E").End(xlUp).Row)
        For Each eleCrit In vTst
            For Each eleData In arrData
                If eleData Like eleCrit Then dic(eleData) = vbNullString
            Next
        Next
        'If value each cell in coluna E has a text like this("*Cancel*", "*cancel*") then
        'Insert letter w into each cell in column AT


        'I try this
        .Offset(, 41).Value = dic(eleData.Value)
    End With
    
End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,132
Office Version
365
Platform
Windows
If you just want to put a "w" into another cell, try
Code:
Sub marreco()
   Dim Cl As Range
   For Each Cl In Range("E2", Range("E" & Rows.Count).End(xlUp))
      If InStr(1, Cl.Value, "cancel", 1) Then Cl.Offset(, 41) = "w"
   Next Cl
End Sub
 

marreco

Well-known Member
Joined
Jan 1, 2011
Messages
607
Hi Fluff

Work greate!!

Thank you!!
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,132
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

Forum statistics

Threads
1,077,674
Messages
5,335,603
Members
399,028
Latest member
greyland

Some videos you may like

This Week's Hot Topics

Top