CODEWITHSUNDEEP
excelvbafilterexact-match
alexey
alexey

Reputation: 1

VBA Macro for Exact Match Filtering

I have a VBA macro that filters on 6 differents criteria, but I'm encountering an issue where the filter includes partial matches. For example, when I filter for the element 1 "G56", it also includes entries like "G56736". I need the filter to match exactly on the specified characters.

Here is my relevant code:

     For Each c In Range("ListeCodes")
    If c.Value = "" Then
        Exit For
    End If
    el1 = c.Offset(0, -6).Value
    el2 = c.Offset(0, -5).Value
    el3 = c.Offset(0, -4).Value
    el4 = c.Offset(0, -3).Value
    el5 = c.Offset(0, -2).Value
    el6 = c.Offset(0, -1).Value
    Range("CritEl1").Value = el1
    Range("CritEl2").Value = el2
    Range("CritEl3").Value = el3
    Range("CritEl4").Value = el4
    Range("CritEl5").Value = el5
    Range("CritEl6").Value = el6
    Range("CritEl1Verif").Value = el1
    Range("CritEl2Verif").Value = el2
    Range("CritEl3Verif").Value = el3
    Range("CritEl4Verif").Value = el4
    Range("CritEl5Verif").Value = el5
    Range("CritEl6Verif").Value = el6
    Sheets("Base").Select
 Range("A1").Select
 Range("BaseCoda").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
     Range("J1:O2"), Unique:=False
 Application.Goto Reference:="BaseCoda"
 Selection.Font.ColorIndex = 50
 Range("A1").Select
 ActiveSheet.ShowAllData

 Next c

 (Range("BaseCoda").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
    Range("J1:O2"), Unique:=False
 Application.Goto Reference:="BaseCoda")

I've tried changing it to ensure an exact match, but it doesn't seem to work:

    Range("CritEl1").Value = "=" & el1
 Range("CritEl2").Value = "=" & el2
 Range("CritEl3").Value = "=" & el3
....

How can I modify my VBA code to filter based strictly on the exact match of the 6 elements?

as explained: the combination of the 6 elements give us a code (that's not that relevant for the VBA code) that's the combination that will filter the data ( see second picture)

Combination:
enter image description here

Data:
enter image description here

Picture 3 :
enter image description here Thanks in advance for your help!

Upvotes: 0

Views: 133

Answers (1)

Tim Williams
Tim Williams

Reputation: 166126

Small example:

enter image description here

This code hides rows 6 and 7:

    Range("A5:E8").AdvancedFilter Action:=xlFilterInPlace, _
                                  CriteriaRange:=Range("A1:E2")

Upvotes: 0

Related Questions