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,
welcome to the forum

To extract only the records for AMK, try using the following format in your criteria:

= "=AMK"


Dave

 

[amelia123456]

Hi,

Can I do it without the = sign?

 

[amelia123456]

Hi Dave,

Is there any other ways of coding it instead of putting an "=" sign?

 

[dmt32]

Hi Dave,

Is there any other ways of coding it instead of putting an "=" sign?

Sorry not sure I follow you, you stated wanted an exact match which normally requires the "=" sign to be included in the critieria.

perhaps further explain your reasoning & I or others here may be able to help you further.

Dave

 

[amelia123456]

Hi Dave,

Is it possible to include the = sign into the code instead of typing it whenever i'm looking for that specific criteria.

Thank you.

 

[dmt32]

Hi Dave,

Is it possible to include the = sign into the code instead of typing it whenever i'm looking for that specific criteria.

Thank you.

If you mean you manually enter your filter value in cell C2 & then have code add "=" for you, then try this

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

Dave

 

[amelia123456]

On the picture attached, when i key in AMK in the location box, both amk and amk mrt results will appear.

In the previous reply, you mention to manually include the '=' (equal) sign, it helps.

But i would like to know if there is any other ways to get the result of 'AMK' without manually input the equal sign ?

https://drive.google.com/open?id=1VbwQaE-UrRMdoPv5LCV5-ab5S1xSCG6x

 

[dmt32]

But i would like to know if there is any other ways to get the result of 'AMK' without manually input the equal sign ?

My updated code in #post 7 should do insert the "=" sign for you

Dave

 

[amelia123456]

Hi Dave,

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