Filtre automatique avec VBA

Version imprimable

Bonjour,

Je dois faire des filtres sur une feuille de mon classeur, le problème c'est que les colonnes peuvent changer d'un moment à l'autre, je voudrais faire en sorte que le programme recherche la bonne colonne avant de faire le filtre selon le nom de la colonne qui se trouve toujours en première ligne, par exemple :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 Nbc = Range("A1").CurrentRegion.Columns.Count Dim i As Integer For i = 1 To Nbc If Cells(1, i).Value = "Das" Then ActiveSheet.Columns(i).AutoFilter _ Field:=7, Criteria1:="SANTE" If Cells(1, i).Value = "Date_1ereTarification" Then ActiveSheet.Columns(i).AutoFilter Field:=8, Operator:= _ xlFilterValues, Criteria2:=Array(0, "7/16/2015") Next i
Je pense que le

Code:

Activesheet.columns()

pose problème car je vois que le filtre automatique est généralement utilisé avec des

Code:

Range

.

Merci d'avance pour votre aide

Bonjour,

un exemple pour comprendre
je pars du principe que ta première colonne remplie est en A

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
Sub Test()
Dim Recherche As String
 
' En-tête de colonne recherché
Recherche = "TOTO"
 
' on cherche l'en-tête dans la ligne 1
Set c = Rows(1).Find(Recherche, , xlValues, xlWhole)
 
' si on l'a trouvé  :    filtre automatique sur cette colonne avec "SANTE" en contenu
If Not c Is Nothing Then Range("A1").AutoFilter c.Column, "SANTE"
 
End Sub

Merci pour votre réponse, cela fonctionne parfaitement. En revanche, j'ai un problème lorsque le critère est de type date :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 For i = 1 To Nbc Set d = Rows(i).Find("Date_1ereTarification", , xlValues, xlWhole) If Not d Is Nothing Then Range("A1").AutoFilter d.Column, Operator:= _ xlFilterValues, Criteria2:=Array(0, "7/16/2015") Set e = Rows(i).Find("Date_DemandeSouscription", , xlValues, xlWhole) If Not d Is Nothing Then Range("A1").AutoFilter e.Column, Operator:= _ xlFilterValues, Criteria2:=Array(0, "7/16/2015") Next i
J'ai essayé de faire avec l'enregistreur de macros, et il prend comme critère la dernière date en cours, je voudrais avoir comme critère la dernière date en automatique, cela est il possible ?

Je n'arrive pas à utiliser la fonction Date, quand je fais ça par exemple :
Code:

1 2 3 Set e = Rows(i).Find("Date_DemandeSouscription", , xlValues, xlWhole) If Not d Is Nothing Then Range("A1").AutoFilter e.Column, Operator:= _ xlFilterValues, Criteria2:=Array(0, "Date")
cela ne fonctionne pas.

Cordialement

20/07/2015, 15h04
joe.levrai
Sans me pencher plus en avant sur ton problème, je note que tu find avec une variable e, et qu'ensuite tu testes l'existence de l'objet d, c'est normal ?
Code:

1 2 3 Set e = Rows(i).Find("Date_DemandeSouscription", , xlValues, xlWhole) If Not d Is Nothing Then Range("A1").AutoFilter e.Column, Operator:= _ xlFilterValues, Criteria2:=Array(0, "Date")
ensuite, tu veux filtrer quoi exactement en terme de date ? Uniquement la date du jour ?

Merci pour votre réponse, je l'avais corrigé dans mon programme, j'avais oublié de faire la correction ici :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
Nbl = Range("A1").CurrentRegion.Rows.Count
Nbc = Range("A1").CurrentRegion.Columns.Count
 
    For i = 1 To Nbc
 
       Set c = Rows(i).Find("Das", , xlValues, xlWhole)
        ' si on l'a trouvé  :    filtre automatique sur cette colonne avec "SANTE" en contenu
'
         If Not c Is Nothing Then Range("A1").AutoFilter c.Column, "SANTE"
 
        Set d = Rows(i).Find("Date_1ereTarification", , xlValues, xlWhole)
         If Not d Is Nothing Then Range("A1").AutoFilter d.Column, Operator:= _
        xlFilterValues, Criteria2:=Array(0, "7/16/2015")
 
        Set e = Rows(i).Find("Date_DemandeSouscription", , xlValues, xlWhole)
         If Not e Is Nothing Then Range("A1").AutoFilter e.Column, Operator:= _
        xlFilterValues, Criteria2:=Array(0, "7/16/2015")
 
Next i

Oui je désire filtrer la date du jour, mon but étant de permettre à la macro de filtrer la colonne correspondante sur l'année 2016 si l'année en cours est 2016.

Voyez-vous ?

20/07/2015, 15h27
joe.levrai

non je ne vois pas :hum:

tu veux la date du jour ou pas ?
là tu parles de 2016 si on est en 2016 ... et on fait quoi tant qu'on est en 2015 ? et quand on sera en 2017 ?
20/07/2015, 15h33
aziz1015

Merci pour votre réponse

J'essaye d'expliquer que je veux la date du jour comme critère peut être en m'exprimant mal. C'est exactement la date du jour que je veux comme critère.

Cordialement

Tu n'as pas besoin d'utiliser une variable pour chaque filtrage
tu peux tout regrouper dans des tableaux de correspondance, et faire une boucle sur chaque cas

comme ceci :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
Sub ioi()
Dim ListeTitre()
Dim ListeParam()
 
ListeTitre = Array("Das", "Date_1ereTarification", "Date_DemandeSouscription")
ListeParam = Array("SANTE", Format(Date, "dd/mm/yyyy"), Format(Date, "dd/mm/yyyy"))
 
' on boucle sur chaque colonne cherchée
' et on filtre sur son paramètre dédié
For i = LBound(ListeTitre) To UBound(ListeTitre)
    Set C = Nothing
    Set C = Rows(1).Find(ListeTitre(i), , xlValues, xlWhole)
    If Not C Is Nothing Then Range("A1").AutoFilter C.Column, ListeParam(i)
Next i
 
End Sub

Merci pour votre réponse, malheureusement le filtre se fait encore mal sur les colonnes contenant des dates lorsque j'utilise votre code, par contre tout fonctionne quand je procède comme suit :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
 
Nbc = ActiveSheet.Range("A1").CurrentRegion.Columns.Count
   For i = 1 To Nbc
 
      Set c = Rows(i).Find("Das", , xlValues, xlWhole)
      si on l'a trouvé  :    filtre automatique sur cette colonne avec "SANTE" en contenu
 
         If Not c Is Nothing Then Range("A1").AutoFilter c.Column, "SANTE"
 
        Set d = Rows(i).Find("Date_1ereTarification", , xlValues, xlWhole)
         If Not d Is Nothing Then ActiveSheet.Range("A1").AutoFilter d.Column, Operator:= _
        xlFilterValues, Criteria2:=Array(0, "7/16/2015")
 
        Set e = Rows(i).Find("Date_DemandeSouscription", , xlValues, xlWhole)
         If Not d Is Nothing Then ActiveSheet.Range("A1").AutoFilter e.Column, Operator:= _
        xlFilterValues, Criteria2:=Array(0, "7/16/2015")
 
         Set f = Rows(i).Find("Statut_Etude", , xlValues, xlWhole)
           If Not f Is Nothing Then ActiveSheet.Range("A1").AutoFilter Field:=38, Criteria1:=Array( _
        "3", "4", "5", "6"), Operator:=xlFilterValues
 
      Next i

Toutefois, je dois reconnaître que votre code est meilleur, je n'ai aucune erreur mais le filtre ne se fait pas sur le format date, j'ai enlevé tous les autres pour ne garder que les colonnes dates et j'ai bien constaté que le problème venait de là. Aussi, dans la colonne "statut_etude", le filtre est fait uniquement sur le chiffre 6. Sauriez vous pourquoi?

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Dim ListeTitre()
Dim ListeParam()
 
ListeTitre = Array("Das", "Statut_Etude", "Date_1ereTarification", "Date_DemandeSouscription")
ListeParam = Array("SANTE", Array("3", "4", "5", "6"), Format(Date, "dd/mm/yyyy"), Format(Date, "dd/mm/yyyy"))
 
' on boucle sur chaque colonne cherchée
' et on filtre sur son paramètre dédié
For i = LBound(ListeTitre) To UBound(ListeTitre)
    Set c = Rows(1).Find(ListeTitre(i), , xlValues, xlWhole)
    If Not c Is Nothing Then Range("A1").AutoFilter c.Column, ListeParam(i)
Next i
 
MsgBox UBound(ListeTitre)

Cordialement

20/07/2015, 16h40
joe.levrai

De mon côté ça fonctionne bien, mais je suis pas toujours propre quand je propose des filtres liés à des dates

comment sont-elles écrites sur ta feuille excel ?

j'avais saisi des dates du style "20/07/2015" (format dd/mm/yyyy)

il existe une solution universelle pour manipuler les dates entre VBA (format anglais) et Excel (format français chez nous), mais je l'oublie à chaque fois :oops:

peut être

Code:

Format(Date, "d/mm/yyyy"))

il me semble que c'est Daniel.C qui a expliqué ça dans plusieurs de ses messages, je regarde son historique

EDIT : bon je n'ai pas retrouvé ce dont je parlais au dessus
Essaye aussi ce format :

Code:

Format(Date, "mm/dd/yyyy"))

mais vérifie qu'il n'inverse pas le mois et le jour :

- entre le 1er et le 12 du mois
- à partir du 13 du mois

Un autre contributeur viendra certainement me dépanner :ptdr:
20/07/2015, 17h01
aziz1015

1 pièce(s) jointe(s)

Merci pour votre réponse,

je ne vois vraiment pas d'où peut venir le problème, j'ai changé le format de différentes manières possibles en utilisant l'enregistreur de macro pour avoir le bon format. Ma méthode peu orthodoxe fonctionne mais le code que vous m'avez donné n'a aucun bug mais ne fait pas les filtres, je vous envoie un fichier joint mon fichier Excel que vous constatiez par vous-même si vous le voulez bien.

Merci pour votre aide

Pièce jointe 183429
20/07/2015, 17h34
joe.levrai

C'est normal que tu n'ai aucune ligne avec la date du jour dans ton fichier ?

Ca expliquerait le fait qu'après application de tes filtres, tu ne remontes aucun résultat :mrgreen:

j'ai mis la date du jour pour ta ligne 2 dans les deux champs filtrés ==> la ligne remonte après application du filtre
20/07/2015, 17h43
aziz1015

Citation:

Envoyé par joe.levrai

C'est normal que tu n'ai aucune ligne avec la date du jour dans ton fichier ?

Ca expliquerait le fait qu'après application de tes filtres, tu ne remontes aucun résultat :mrgreen:

J'ai oublié de vous le préciser mais même après avoir changé toutes les cellules en la date du jour, cela ne fonctionne pas. Aussi, vous remarquerez dans le code que j'ai obtenu grâce à l'utilisateur de macros que j'ai des dates dans le code

Code:

Criteria2:=Array(0, "7/16/2015")

mais le filtre se fait sur toute l'année 2015.

Du coup, j'ai pensé qu'avoir la date du jour me donnerait toutes les dates en 2015 tout comme dans le code que j'ai obtenu avec l'enregistreur de macros.

Il y a aussi le filtre sur ce critère qui ne se fait pas correctement qui ne fonctionne pas

Code:

Array("3", "4", "5", "6") de la colonne "Statut_Etude", le filtre se fait uniquement sur le chiffre 6

Alors le problème viendrait de ma version d'Excel ? Parce que si ça marche chez vous, ça devrait marchez moi aussi je crois !
Je souhaiterais en fait filtrer uniquement l'année en cours. Cela est-il possible ?

Merci pour votre aide

Pour ton problème de Statut_Etude, étant donné que tu filtres plusieurs valeurs, il ne faut pas oublier de spécifier l'argument XlFilterValues :
Code:

1 2 3 4 5 For i = LBound(ListeTitre) To UBound(ListeTitre) Set c = Nothing Set c = Rows(1).Find(ListeTitre(i), , xlValues, xlWhole) If Not c Is Nothing Then Range("A1").AutoFilter c.Column, ListeParam(i), xlFilterValues Next i
sinon il ne prendra que la dernière valeur de ton array

pour la date du jour, je ne sais que dire, ça fonctionne de mon côté (y compris en utilisant d'autres formats)

reprend le fichier que tu m'as joins, à la ligne deux de ta BDD, met "20/07/2015" dans les deux colonnes qui seront filtrées sur les dates (colonne H et colonne J)
teste cette macro
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 Sub Test1() Dim ListeTitre() Dim ListeParam() ListeTitre = Array("Das", "Statut_Etude", "Date_1ereTarification", "Date_DemandeSouscription") ListeParam = Array("SANTE", Array("3", "4", "5", "6"), Format(Date, "dd/mm/yyyy"), Format(Date, "dd/mm/yyyy")) ' on boucle sur chaque colonne cherchée ' et on filtre sur son paramètre dédié For i = LBound(ListeTitre) To UBound(ListeTitre) Set c = Nothing Set c = Rows(1).Find(ListeTitre(i), , xlValues, xlWhole) If Not c Is Nothing Then Range("A1").AutoFilter c.Column, ListeParam(i), xlFilterValues Next i End Sub
fonctionnel à 100% chez moi, testé sur Excel2013 et Excel2010

20/07/2015, 18h41
aziz1015

Merci beaucoup,

Je suis sur Excel 2007, le filtre se fait bien sur les dates désolé de vous avoir un peu faire perdre votre temps je n'avais pas changé la date dans les deux colonnes :aie:, tout marche bien par contre j'ai une dernière question

Citation:

vu que le format des cellules est de type date, est ce qu'il est possible de filtrer uniquement sur l'année ? Ou bien de filtrer entre le 1er Janvier et le 1ere Décembre de l'année en Cours ?

J'ai trouvé une solution en faisant comme ça :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
 
Sub Test1()
 
 
Dim ListeTitreAnnee()
 
Dim ListeTitre()
Dim ListeParam()
 
ListeTitre = Array("Das", "Statut_Etude", "Date_1ereTarification", "Date_DemandeSouscription")
ListeParam = Array("SANTE", Array("3", "4", "5", "6"), Format(Date, "dd/mm/yyyy"), Format(Date, "dd/mm/yyyy"))
 
' on boucle sur chaque colonne cherchée
' et on filtre sur son paramètre dédié
For i = LBound(ListeTitre) To UBound(ListeTitre)
 
    Set c = Nothing
    Set c = Rows(1).Find(ListeTitre(i), , xlValues, xlWhole)
    If Not c Is Nothing Then Range("A1").AutoFilter c.Column, ListeParam(i), xlFilterValues
 
Next i
 
ListeTitreAnnee = Array("Date_1ereTarification", "Date_DemandeSouscription")
dte = Year(Date)
 
For j = LBound(ListeTitreAnnee) To UBound(ListeTitreAnnee)
 
    Set d = Nothing
    Set d = Rows(1).Find(ListeTitreAnnee(j), , xlValues, xlWhole)
      If Not d Is Nothing Then Range("A1").AutoFilter d.Column, Criteria1:=">=01/01/" & dte, Operator:=xlAnd, Criteria2:="<=12/31/" & dte
 
Next j
 
 
End Sub

Merci infiniment pour votre aide

J'ai le résultat voulu, mais dans la colonne

Code:

"Date_DemandeSouscription", les mois de l'année 2015 ne sont pas cochés

, est ce une erreur ?

Cordialement

Il y a deux solutions possibles :

1) utiliser Criteria1 pour >=01/01/AnneeEnCours // Critéria2 pour <=31/12/AnneeEncours
2) utiliser le filtre chronologique "Cette Annee"

voici l'exemple avec la méthode 2
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 Sub Test1() Dim ListeTitre() Dim ListeParam() ListeTitre = Array("Das", "Statut_Etude", "Date_1ereTarification", "Date_DemandeSouscription") ListeParam = Array("SANTE", Array("3", "4", "5", "6")) ' on boucle sur chaque colonne cherchée ' et on filtre sur son paramètre dédié For i = LBound(ListeTitre) To UBound(ListeTitre) Set c = Nothing Set c = Rows(1).Find(ListeTitre(i), , xlValues, xlWhole) If Not c Is Nothing Then If i <= UBound(ListeParam) Then Range("A1").AutoFilter c.Column, ListeParam(i), xlFilterValues Else Range("A1").AutoFilter c.Column, xlFilterThisYear, xlFilterDynamic End If End If Next i End Sub
Concrètement, j'ai supprimé les Paramètres chronologiques dans le tableau ListeParam
Avant d'appliquer un filtre, je teste si on a dépassé l'indice maximal de ListeParam :

- si on l'a dépassé : on est sur les filtres où tu ne veux que l'année
- sinon : on applique le paramètre de ListeParam pour effectuer le filtre

20/07/2015, 22h17
aziz1015

Merci infiniment pour votre aide qui m'a été très précieuse.