Option Compare Text
Sub essaiFiltre()
Set f = Sheets("feuil1")
clé = "Mar"
'que les valeurs entieres qui sont EGALES !!! a "mar"
Tbl = Application.Transpose(f.Range("A1:A13" & f.[A65000].End(xlUp).Row).Value)
Tbl = Filtre(Tbl, clé)
[C1] = "XlWhole"
If Not IsEmpty(Tbl) Then f.[C2].Resize(UBound(Tbl)) = Application.Transpose(Tbl)
'toutes les valeurs qui CONTIENNENT !! "mar"
Tbl = Application.Transpose(f.Range("A1:A13" & f.[A65000].End(xlUp).Row).Value)
Tbl = Filtre(Tbl, clé, MacXlpart:=True)
[D1] = "Xlpart ""mar"""
If Not IsEmpty(Tbl) Then f.[D2].Resize(UBound(Tbl)) = Application.Transpose(Tbl)
'toute les valeurs qui COMMENCE !! par "mar"
Tbl = Application.Transpose(f.Range("A1:A13" & f.[A65000].End(xlUp).Row).Value)
Tbl = Filtre(Tbl, clé, , commence_par:=True)
[E1] = "Commence par ""mar"""
If Not IsEmpty(Tbl) Then f.[E2].Resize(UBound(Tbl)) = Application.Transpose(Tbl)
'EXCLURE toute les valeurs qui COMMENCENT!!! par "mar"
Tbl = Application.Transpose(f.Range("A1:A13" & f.[A65000].End(xlUp).Row).Value)
Tbl = Filtre(Tbl, clé, commence_par:=True, Include:=False)
[F1] = "commence par ""mar"" exclusion "
If Not IsEmpty(Tbl) Then f.[F2].Resize(UBound(Tbl)) = Application.Transpose(Tbl)
'EXCLURE toute les valeurs qui CONTIENNENT !!!!!! le mot "mar"
Tbl = Application.Transpose(f.Range("A1:A13" & f.[A65000].End(xlUp).Row).Value)
Tbl = Filtre(Tbl, clé, MacXlpart:=True, Include:=False)
[G1] = "XLPART ""mar"" exclusion"
If Not IsEmpty(Tbl) Then f.[G2].Resize(UBound(Tbl)) = Application.Transpose(Tbl)
End Sub
Function Filtre(ByRef Tbl, ByVal clé As String, Optional ByRef MacXlpart As Boolean = False, Optional ByRef commence_par As Boolean = False, Optional ByRef Include As Boolean = True)
n = 0: Dim b()
If MacXlpart = True Then clé = "*" & clé & "*" Else clé = clé
If commence_par = True Then clé = clé & "*"
For i = LBound(Tbl) To UBound(Tbl)
If Include Then
If Tbl(i) Like clé Then n = n + 1: ReDim Preserve b(1 To n): b(n) = Tbl(i)
Else
If Not Tbl(i) Like clé Then n = n + 1: ReDim Preserve b(1 To n): b(n) = Tbl(i)
End If
Next i
If n > 0 Then Filtre = b
End Function