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

code	assets	location	filter(this is a button)
		AMK
Code	date	cost	assets	owner	condition	location
12345	12/02/2016	2000	sealing machine	abc	good	Amk
22456	10/05/2017	1000	sugar machine	abc	good	Amk Mrt
55624	11/04/2018	1800	induction cooker	abc	good	AMK

<tbody>
</tbody>

 

[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