Wild card unable to use in autofilter array

[suresh7860]

Hi Experts

I am getting the results correctly when i use directly without wildcards (*) for contains but when i use wildcard (*) in array its not working, would be helpful if any of you can help me or guide me to correct it.

After this i have code to cut n move the data to another sheet which is working fine need to fix this to complete my work.

Dim vTst As Variant
vTst=Array("ABC", "DEF", "GHI", "JKLM") - Working fine (without contains - exact match -working fine)
vTst = Array("*ABC*", "*DEF*", "*GHI*", "*JKLM*") - (with contains - not working)

With Worksheets("Sheet")
If .AutoFilterMode Then .AutoFilterMode = False
With .Cells(1, 1).CurrentRegion

'filter on all the values in the array

.AutoFilter Field:=2, Criteria1:=vTst, Operator:=xlFilterValues
(working for exact match and not working when wild card (*) used
or
.AutoFilter Field:=2, Criteria1:="=*" & vTst & "*", Operator:=xlFilterValues - wild cards not working

Thanks for your time experts

Regards
Suresh7860

 

[tony92]

Hello Rick,

I am getting elecrit is empty error on my end.

Sub doc_link_extract_using_id()


    '--Segment 1
    'Define sheet that has Doc ID [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=s]#s[/URL]  to get links for "LOB Docs"
    Dim sh_1 As Worksheet
    Set sh_1 = ThisWorkbook.Worksheets("LOB Docs")


    'Declare an array to hold all LOB Doc ID numbers
    Dim Doc_ID_Arr As Variant
    Dim Doc_ID_Value As String
    Dim j As Long
    Dim i As Long
            
    With sh_1
    lastrow_Header_Config = sh_1.Cells(Rows.count, "A").End(xlUp).Row
    ' Read LOB DOC ID's from Column Cell A2 to last value in Column A
    ReDim Doc_ID_Arr(Application.WorksheetFunction.CountA(sh_1.Range("A2:A" & lastrow_Header_Config)) - 1) As Variant
    j = 0
    For i = 2 To lastrow_Header_Config
        Doc_ID_Value = sh_1.Range("A" & i).Value
        If Doc_ID_Value <> "" Then
        Doc_ID_Arr(j) = "*" & Doc_ID_Value & "*"
        j = j + 1
        End If
    Next
   End With
   
    '--Segment 2
   Dim sh_2 As Worksheet 'Data Sheet
   Dim sh_3 As Worksheet 'Output Sheet
   Set sh_2 = ThisWorkbook.Worksheets("GDL db") 'Data Sheet
   Set sh_3 = ThisWorkbook.Worksheets("Seed Template Output")
   
   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")
 
   Dim x As Integer


    For x = LBound(Doc_ID_Arr) To UBound(Doc_ID_Arr)
        vTst = Doc_ID_Arr(x)
    Next x
   
   
   With sh_2
   .AutoFilterMode = False
   ArrData = .Range("A1:A" & .Cells(.Rows.count, "A").End(xlUp).Row)
   For Each eleCrit In vTst
        For Each eleData In ArrData
            If eleData Like eleCrit Then _
            Dic(eleData) = vbNullString
        Next
   Next
    .Columns("A:A").AutoFilter Field:=1, Criteria1:=Dic.Keys, Operator:=xlFilterValues
   sh_2.UsedRange.Copy sh_3.Range("A1")
   End With


End Sub

Can you please help me understand why the eleCrit is showing no values, when vTsts has been assigned the values of array Doc_ID_Arr(x).

 

[Peter_SSs]

Welcome to the MrExcel board!

Hello Rick,

Rick hasn't been on the forum much lately so you might be best to set out fresh what you have, where and just what you are trying to achieve. It might be easier for new helpers to look at your issue alone rather than trying to catch up on the old one then adapt to yours.