vba filter exact match

[amelia123456]

I have a few locations, eg. AMK, AMK MRT.

When i type AMK, both amk and amk mrt will appear, how do i filter to exact when i type amk, only amk will appear?



this is my code

Sub Advanced_Filtering()


Range("A8:H1611").AdvancedFilter _
Action:=xlFilterInPlace, _
CriteriaRange:=Range("A1:C2")


End Sub





[TABLE="width: 500"]
<tbody>[TR]
[TD]code[/TD]
[TD]assets[/TD]
[TD]location[/TD]
[TD] filter(this is a button)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]AMK[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Code[/TD]
[TD]date[/TD]
[TD]cost[/TD]
[TD]assets[/TD]
[TD]owner[/TD]
[TD]condition[/TD]
[TD]location[/TD]
[/TR]
[TR]
[TD]12345[/TD]
[TD]12/02/2016[/TD]
[TD]2000[/TD]
[TD]sealing machine[/TD]
[TD]abc[/TD]
[TD]good[/TD]
[TD]Amk[/TD]
[/TR]
[TR]
[TD]22456[/TD]
[TD]10/05/2017[/TD]
[TD]1000[/TD]
[TD]sugar machine[/TD]
[TD]abc[/TD]
[TD]good[/TD]
[TD]Amk Mrt[/TD]
[/TR]
[TR]
[TD]55624[/TD]
[TD]11/04/2018[/TD]
[TD]1800[/TD]
[TD]induction cooker[/TD]
[TD]abc[/TD]
[TD]good[/TD]
[TD]AMK[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[dmt32]

Hi Dave,

I have tried but it doesnt work. There's error

what's the error?

 

[amelia123456]

Hi Dave,

https://drive.google.com/file/d/1VbwQaE-UrRMdoPv5LCV5-ab5S1xSCG6x/view?usp=sharing

https://drive.google.com/file/d/1BZjlQIBqac1TdsdliKqPlrEbG7cGigtb/view?usp=sharing

it does not work, whenever i click on the filter button, "" will appear continuously and results will not appear.

 

[amelia123456]

what's the error?

https://drive.google.com/file/d/1VbwQaE-UrRMdoPv5LCV5-ab5S1xSCG6x/view?usp=sharing

When i did not type anything on to the criteria boxes and click fliter, = and "" will appear. results will not be shown too

https://drive.google.com/file/d/1BZjlQIBqac1TdsdliKqPlrEbG7cGigtb/view?usp=sharing


Even when i key in the the amk in the criteria box it does not show too. if i click filter alot of time the "" will keep appearing each time i key in

 

[Marcelo Branco]

Try

Sub Advanced_Filtering()
    Dim strCriteria As String
      
    strCriteria = "AMK"
    Range("C2") = "=""=" & strCriteria & """"
        
    If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData
    
    Range("A8").CurrentRegion.AdvancedFilter _
        Action:=xlFilterInPlace, CriteriaRange:=Range("A1:C2")
End Sub

M.

 

[DanteAmor]

Please do the following:
- Change the format of cell C2 to general format. (This is very important)
- Write in the cell C2 the Amk data without quotes or spaces, only the Amk data


That should look like this:

- Execute the following code

Sub Advanced_Filtering()
    If InStr(1, Range("C2").Value, "=") = 0 Then
        Range("C2").Formula = "=""=" & Range("C2").Value & """"
    End If
    Range("A8:H1611").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("A1:C2")
End Sub

 

[dmt32]

Hi Dave,

it does not work, whenever i click on the filter button, "" will appear continuously and results will not appear.

Hi,
sorry about that, my bad should have thought about blank entry

try this update which includes the other filter criteria headings you have in the range

Sub Advanced_Filtering()
    Dim strCriteria As String
    Dim cell As Range
    
    For Each cell In Range("A2:C2")
        If Len(cell.Text) > 0 Then
        strCriteria = cell.Text
'ensure = sign not already included
        strCriteria = Replace(strCriteria, "=", "")
'add = sign to formula
        cell.Formula = "=""=" & strCriteria & """"
        End If
    Next cell
'reset filter
    If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData
'apply filter
    Range("A8").CurrentRegion.AdvancedFilter _
    Action:=xlFilterInPlace, _
    CriteriaRange:=Range("A1:C2")
End Sub

Hope helpful

Dave