Filtre avancé et tableau VBA

Y compris Power BI, Power Query et toute autre question en lien avec Excel
E
Exermind
Nouveau venu
Nouveau venu
Messages : 3
Inscrit le : 23 août 2015
Version d'Excel : 2013 FR

Message par Exermind » 23 août 2015, 15:10

Bonjour,

Je vous remercie tout d'abord de vos cours VBA qui m'on permis de m'y mettre facilement et rapidement !

Je construit petit à petit un calendrier d'occupation de salles qui se met en forme à partir d'une base de donnée. En gros, ma base de donnée comprend chaque "demande" de réunion avec la date et la salle choisie ; dans mon calendrier, les dates à la verticale, les salles à l'horizontale, je cherche à colorier les cellules correspondant à la date et à la salle d'une réunion.

Pour ça, j'ai fait une macro avec un filtre avancé : je filtre selon les critères de date et de salle et je teste les résultats. J'ai d'abord enregistré "manuellement" une macro où je lance le filtre avancé, puis je l'ai intégré dans une plus complète macro. L'enregistrement du filtre m'a donné cela :
Sub MacroFiltre()
'
' MacroFiltre Macro
'

'
    Range("A1:C20").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
        "E12:G13"), CopyToRange:=Range("E1"), Unique:=False
End Sub
Pas de soucis pour faire fonctionner ma macro complète tant que les zones de filtre (la base de donnée, la zone de critère et la zone de résultat) renvoient vers des plages de cellules du classeur. Cependant, d'après les cours VBA du filtre, il semble plus intéressant d'utiliser des tableaux de donnée dans la macro. Il est vrai que pour l'instant, avec à peine 20 lignes dans ma base de donnée, 31 jours de calendrier et cinq salles, le temps de calcul est déjà appréciable (environ une demi-seconde).

Seulement, je ne sais pas trop comment remplacer les plages de cellule dans la fonction AdvancedFilter par mes tableaux de donnée. déclarés dans ma macro. Voici où j'en suis :
Sub MeF2()

    'Déclaration des variables Ligne et Colonne
    Dim Ligne As Integer, Colonne As Integer
    Ligne = 2
    Colonne = 2
    
    'Déclaration de la variable dernière ligne (Base de donnée)
    Dim Ligne_last As Integer
    Ligne_last = Sheets("BDD").Range("A1").End(xlDown).Row
    
    'Déclaration des tableaux :
    Dim tab_bdd()                       'Tableau de la BDD
    ReDim tab_bdd(Ligne_last - 1, 2)
    Dim tab_critere(1, 2)               'Tableau des critères
    Dim tab_resultat(5, 2)              'Tableau des résultats (6 lignes de résultat au cas où)
    
    'Enregistrement des valeurs dans le tableau de la BDD
    For i = 0 To Ligne_last - 1
        tab_bdd(i, 0) = Sheets("BDD").Range("A" & i + 1)
        tab_bdd(i, 1) = Sheets("BDD").Range("B" & i + 1)
        tab_bdd(i, 2) = Sheets("BDD").Range("C" & i + 1)
    Next
    
    'Enregistrement des valeurs dans le tableau des critères
    tab_critere(0, 0) = Sheets("BDD").Range("A1")
    tab_critere(0, 1) = Sheets("BDD").Range("B1")
    tab_critere(0, 2) = Sheets("BDD").Range("C1")
    
    'Nettoyage du calendrier (risque de rémanence de réunion annulée)
    Sheets("Calendrier").Range("B2:F32").ClearContents
    
    'On va tester colonne par colonne avant de passer à la ligne suivante jusqu'à la dernière date du calendrier
    For Ligne = 2 To 32
    
        For Colonne = 2 To 6
        
            'Nettoyage des cellules résultats du filtre
            For j = 1 To 5
                For k = 0 To 2
                    tab_resultat(j, k) = ""
                Next
            Next
            
            'Nettoyage de la zone de critère
            tab_critere(1, 0) = ""
            tab_critere(1, 1) = ""
            tab_critere(1, 2) = ""
            
            'Déclaration des critères Date puis Salle
            tab_critere(1, 1) = Sheets("Calendrier").Cells(Ligne, 1)
            tab_critere(1, 2) = Sheets("Calendrier").Cells(1, Colonne)
            
            'Filtre avancé (sur le tableau de la BDD, suivant le tableau des critères, les résultats seront écrits dans le tableau des résultats
            tab_bdd().AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=tab_critere(), CopyToRange:=tab_resultat(), Unique:=False
                
            'On test le résultat obtenu
            If tab_resultat(1, 0) <> "" Then                                                    'Si un résultat apparait, son numéro de demande apparaitra en E2
                Sheets("Calendrier").Cells(Ligne, Colonne).Interior.Color = RGB(0, 255, 0)      'On colorie le fond de la cellule en vert
                Sheets("Calendrier").Cells(Ligne, Colonne) = Sheets("BDD").Range("E2")          'On inscrit le numéro de demande dans la cellule
                
            Else                                                                                'Sinon, (pas de numéro de demande)
                Sheets("Calendrier").Cells(Ligne, Colonne).Interior.Color = RGB(255, 255, 255)  'On colorie en blanc la cellule
                Sheets("Calendrier").Cells(Ligne, Colonne) = ""                                 'On vide la cellule de son contenu
                
            End If
            
        Next
        
        'En allant à la ligne suivante, on oublie pas de revenir à la première colonne
        Colonne = 2
        
    Next
    
End Sub
Je ne sais pas si cela est vraiment possible et appréciable pour le temps de calcul. La fonction AdvancedFilter me parait la plus adaptée pourtant, puisqu'à terme, j'aurait d'autres informations dans la base de donnée que je souhaite tester et/ou inscrire en commentaire dans la cellule correspondante du calendrier.

Je joins mon fichier test avec la macro complète MeF() sans les tableaux de donnée (et qui marche) et la macro MeF2() avec les tableaux de donnée.
TestCalVBA.xlsm
(21.88 Kio) Téléchargé 96 fois
Avatar du membre
eriiic
Passionné d'Excel
Passionné d'Excel
Messages : 9'399
Appréciations reçues : 403
Inscrit le : 7 février 2010
Version d'Excel : 2010fr

Message par eriiic » 23 août 2015, 23:15

Bonjour,

Si c'est pour colorer tu peux le faire avec une MFC.
eric

PS : pour un flitre avancé on met plutôt les critères au-dessus de l'extraction. Sinon ils risquent d'être écrasés.
TestCalVBA.xlsm
(53.95 Kio) Téléchargé 94 fois
En essayant continuellement, on finit par réussir.
Donc plus ça rate, plus on a de chances que ça marche.
(les Shadoks)

En plus du merci (si si, ça se fait !!!), penser à mettre en résolu. Merci
E
Exermind
Nouveau venu
Nouveau venu
Messages : 3
Inscrit le : 23 août 2015
Version d'Excel : 2013 FR

Message par Exermind » 24 août 2015, 10:52

Merci bien pour ta formule !

Effectivement, j'étais d'abord parti sur une mise en forme conditionnelle mais j'utilisais RECHERCHEV et je n'arrivais pas à avoir plus d'un critère. Les fonctions ÉQUIV et DÉCALER sont en effet très pratiques.

Je vais peut-être quand même être obligé d'utiliser une macro pour rentrer les commentaires. Pour cela et pour ma culture personnelle, est-il possible d'effectuer un filtre avancé sur un tableau de donnée (dans une macro) ? Serait-ce plus rapide que ma première macro ?

Merci.
Avatar du membre
eriiic
Passionné d'Excel
Passionné d'Excel
Messages : 9'399
Appréciations reçues : 403
Inscrit le : 7 février 2010
Version d'Excel : 2010fr

Message par eriiic » 24 août 2015, 17:00

Bonjour,

oui c'est possible, voir exemple joint. Et c'est extrêmement rapide car très optimisé.
Si c'est pour mettre à jour qq lignes de données ça va.
Par contre si tu as plusieurs dizaines de lignes à mettre à jour sur feuille c'est plus rapide de lire en bloc en une fois les données (datas=[A2:D20])dans une variable tableau et l'inscrire en une fois aussi ([A2:D20]=datas).
eric
TestCalVBA.xlsm
(59.64 Kio) Téléchargé 167 fois
En essayant continuellement, on finit par réussir.
Donc plus ça rate, plus on a de chances que ça marche.
(les Shadoks)

En plus du merci (si si, ça se fait !!!), penser à mettre en résolu. Merci
E
Exermind
Nouveau venu
Nouveau venu
Messages : 3
Inscrit le : 23 août 2015
Version d'Excel : 2013 FR

Message par Exermind » 24 août 2015, 19:09

En fait, j'ai du mal me faire comprendre au départ, je parlais bien des variables tableau. Est-ce équivalent aux Array ?


Sinon, mon gros soucis, c'est la "grammaire" je crois. Comment puis-je affecter l'AdvancedFilter à mon tableau variable ? AdvancedFilter.data ? data.AdvancedFilter ?....

Pareil pour le CriteriaRange et le CopyToRange ; autant faire ça dans une variable tableau.


Sinon, un ami m'a donné une solution pour faire ça directement sur la feuille de calcul avec les tableau matriciels, un truc du genre EQUIV(1;(Date=$A2)*(Salle=B$1);0)


Merci pour tout, j'ai vite avancé sur Excel avec vos conseils.
Avatar du membre
eriiic
Passionné d'Excel
Passionné d'Excel
Messages : 9'399
Appréciations reçues : 403
Inscrit le : 7 février 2010
Version d'Excel : 2010fr

Message par eriiic » 24 août 2015, 19:24

Comment puis-je affecter l'AdvancedFilter à mon tableau variable ?
Il me semble que tu as suffisamment d'éléments dans mon dernier post, je te dis comment lire et écrire une plage.
autant faire ça dans une variable tableau.
Tu peux oui (même réponse qu'au-dessus). Mais il faut tenir compte de ton besoin.
Si tu as des dizaines de lignes à mettre à jour en une fois oui, mais est-ce vraiment le cas ?
Si c'est pour mettre à jour des lignes une par une ce n'est pas la peine, et j'ai bien l'impression que c'est ça.

eric
En essayant continuellement, on finit par réussir.
Donc plus ça rate, plus on a de chances que ça marche.
(les Shadoks)

En plus du merci (si si, ça se fait !!!), penser à mettre en résolu. Merci
S
SouVB
Nouveau venu
Nouveau venu
Messages : 6
Inscrit le : 14 février 2018
Version d'Excel : 2010

Message par SouVB » 14 février 2018, 20:14

Bonsoir Eric,

Le sujet est vieux mais j’espère que vous allez pouvoir m'aider.
J'essaye d'utiliser la fonction filtre avancée via VBA en exploitant uniquement des variables tableaux mais je n'y arrive pas, il semblerait que le filtre avancée fonctionne uniquement lorsqu'il interagit avec des feuilles excel.

J'ai testé le fichier exemple "TestCallVBA" mais la macro plante sur la fonction advanced filter "Erreur de compilation : qualificateur incorrecte" et la variable tableau "Tab_BDD()" se met en rouge.

D'avance merci pour votre aide!
Avatar du membre
eriiic
Passionné d'Excel
Passionné d'Excel
Messages : 9'399
Appréciations reçues : 403
Inscrit le : 7 février 2010
Version d'Excel : 2010fr

Message par eriiic » 14 février 2018, 23:58

Bonjour,

un peu vieux oui.
J'ai testé par curiosité cette antiqué et pas d'erreur.
Tu devrais démarrer ta propre question en mettant ton fichier.
eric
En essayant continuellement, on finit par réussir.
Donc plus ça rate, plus on a de chances que ça marche.
(les Shadoks)

En plus du merci (si si, ça se fait !!!), penser à mettre en résolu. Merci
S
SouVB
Nouveau venu
Nouveau venu
Messages : 6
Inscrit le : 14 février 2018
Version d'Excel : 2010

Message par SouVB » 15 février 2018, 08:38

Bonjour,

Tu as testé avec quelle version d’excel ? Tu as lancé la macro qui utilise le filtre avance via variable tableau ?
Avatar du membre
eriiic
Passionné d'Excel
Passionné d'Excel
Messages : 9'399
Appréciations reçues : 403
Inscrit le : 7 février 2010
Version d'Excel : 2010fr

Message par eriiic » 15 février 2018, 09:27

Bonjour,

le dernier déposé, sur 2010.
Mais démarre ta propre question.
eric
En essayant continuellement, on finit par réussir.
Donc plus ça rate, plus on a de chances que ça marche.
(les Shadoks)

En plus du merci (si si, ça se fait !!!), penser à mettre en résolu. Merci
Répondre Sujet précédentSujet suivant
  • Sujets similaires
    Réponses
    Vues
    Dernier message