VBA Autofilter partial match in two column

[alantse2010]

Hi all, I want to use the autofilter for partial match in two columns, it not works for two columns but works for one column.

May I know how to revise the code?

Sub Search()

Dim Mws As Worksheet
Dim PR As Worksheet
Dim Rng As Range
Set Mws = ThisWorkbook.Sheets("Data input v2")
Set PR = ThisWorkbook.Sheets("Project Record")
Mws.Range("C19:AW9999").ClearContents
Set Rng = PR.Range("D2:AX" & PR.Cells(PR.Rows.Count, "AX").End(xlUp).Row)
With Rng
'Rng.AutoFilter Field:=4, Criteria1:=Mws.Range("E5").Value
'Rng.AutoFilter Field:=5, Criteria1:=Mws.Range("G5").Value
Rng.AutoFilter Field:=4, Criteria1:="*" & Mws.Range("E5").Value & "*"
Rng.AutoFilter Field:=5, Criteria1:="*" & Mws.Range("G5").Value & "*"
Rng.Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count - 1).Copy Mws.Range("C19")
Rng.Parent.AutoFilterMode = False
End With

End Sub

If I use below formula, it works, but i don't know how to convert to VBA code that make it editable as filter formula

=IFS(I9="Partial Match",FILTER('Project Record'!D3:AX99999,ISNUMBER(SEARCH(E5,'Project Record'!G3:G99999))*ISNUMBER(SEARCH(G5,'Project Record'!H3:H99999)),"No Match Found"))

Would anyone help?

THank you very much.

 

[RoryA]

Your code should work as long as you aren't trying to filter numbers - wildcards will not work for that.

 

[alantse2010]

Your code should work as long as you aren't trying to filter numbers - wildcards will not work for that.

Thank you for your reply.
may i know if how to filter number with character?
As Mws.Range("E5").Value contain number and character or character only , such as YT-154895, WS-248350 and buffer
Also, Mws.Range("G5").Value is number only, such as 4501236852.

Thank you very much

 

[RoryA]

E5 is fine, but you cannot do a text type filter using G5 unless column G has numbers stored as text. If they are stored as numbers, you can only do exact matches or other numeric filters like >, <, <= etc. So to do something like a 'starts with 45' you'd need to filter for >=4500000000 and <4600000000

 

[alantse2010]

E5 is fine, but you cannot do a text type filter using G5 unless column G has numbers stored as text. If they are stored as numbers, you can only do exact matches or other numeric filters like >, <, <= etc. So to do something like a 'starts with 45' you'd need to filter for >=4500000000 and <4600000000

Thank you for your reply.

I try to amend the type of column G and Test many times, it still not works, I don't know why.
So, i use the filter formula and record the Marco to do this.
Anyway, thank you very much for your help.
It is my formula:

=IFS(
I9="Partial Match",FILTER('Project Record'!A2:AT99983,ISNUMBER(SEARCH(E5,'Project Record'!D2:D99983))*ISNUMBER(SEARCH(G5,'Project Record'!E2:E99983))*ISNUMBER(SEARCH(I5,'Project Record'!K2:K99983))*ISNUMBER(SEARCH(E7,'Project Record'!F2:F99983))*ISNUMBER(SEARCH(E9,'Project Record'!G2:G99983)),"No Match Found"))

Below is my code:

Sub Search()
Range("B19:AV9999").ClearContents
Range("C19").Select
ActiveCell.Formula2R1C1 = _
        "=IFS(" & Chr(10) & "R[-10]C[6]=""Partial Match"",FILTER('Project Record'!R[-17]C[-2]:R[99964]C[43],ISNUMBER(SEARCH(R[-14]C[2],'Project Record'!R[-17]C[1]:R[99964]C[1]))*ISNUMBER(SEARCH(R[-14]C[4],'Project Record'!R[-17]C[2]:R[99964]C[2]))*ISNUMBER(SEARCH(R[-14]C[6],'Project Record'!R[-17]C[8]:R[99964]C[8]))*ISNUMBER(SEARCH(R[-12]C[2],'Project Record'!R[-17]C[3]:R[99964]C[3]))*ISN" & _
        "UMBER(SEARCH(R[-10]C[2],'Project Record'!R[-17]C[4]:R[99964]C[4])),""No Match Found""))" & _
        ""
    'ActiveCell.Formula2R1C1 = _
        "=IFS(" & Chr(10) & "R[-10]C[6]=""Partial Match"",FILTER('Project Record'!R[-16]C[1]:R[99980]C[47],ISNUMBER(SEARCH(R[-14]C[2],'Project Record'!R[-16]C[4]:R[99980]C[4]))*ISNUMBER(SEARCH(R[-14]C[4],'Project Record'!R[-16]C[5]:R[99980]C[5])),""No Match Found""))"
    'Range("E5").Select
    'ActiveCell.FormulaR1C1 = "YT-150022"
    Range("C19:AV9999").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("C20").Select
End Sub