[SOLVED] VBA Autofilter partial match in two column

VBA Autofilter partial match in two column

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 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.

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.

Solution

This works in my sample sheet if i correctly understand what you want to do.

Sub two_column_partial_match()

Dim rng As Range 
Dim startData As Variant
Dim resultData As Variant
Dim i As Long
Dim lastrow As Long
Dim k As Long
Dim j As Long

Range("C19:K1048576").Clear

lastrow = Worksheets("Project Record").Range("G" & 
Rows.Count).End(xlUp).Row
Set rng = Worksheets("Project Record").Range("G3:O" & lastrow)

startData = rng.Value
ReDim resultData(1 To rng.Rows.Count, 1 To 9)

k = 1

For i = 1 To UBound(startData, 1)
  If InStr(1, startData(i, 1), Cells(5, 5), 1) > 0 And InStr(1, 
startData(i, 2), Cells(5, 7), 1) > 0 Then

    For j = 1 To 9
       resultData(k, j) = startData(i, j)
   
    Next j
    k = k + 1

  Else
  End If


Next i

Range("C19").Resize(k, 9) = resultData

End Sub