VBA - Forcer un filtre TCD à une valeur de critère inexistante

Le 18/03/2019 à 17:44

Bonjour,

Je suis devant un petit problème de Tableau Croisé Dynamique.

J'ai une application Excel qui utilise plusieurs TCD conditionnés par de multiples filtres fixes, c'est à dire qui ne devraient pas changer dans le temps, quitte à produire un tableau vide lorsque le critère de filtre n'est pas respecté.

Le problème est que lorsqu'un critère de filtre n'existe pas à un instant t dans la source de donnée, Excel met dans le filtre une valeur prise (au hasard ?) dans la liste des critères existants. Résultat: le TCD produit un résultat qui n'a rien à voir avec ce que j'attends de lui (basé sur le critère fantaisiste) et, de plus le filtre ne revient plus à la valeur de critère originelle, même lorsque ce critère redevient présent dans la source de donnée.

Je ne sais pas si je suis très clair. Merci de jeter un coup d'oeil sur l'exemple joint.

Dans cet exemple, si je supprime les lignes 6 à 8 (correspondant à l'index ind2) et que je réactualise le TCD, le filtre Index "saute" et prend une des autres valeurs d'index (en l'occurrence ind3).

J'ai donc imaginé une macro VBA pour forcer le filtre à la bonne valeur:

ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("index").ClearAllFilters

ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("index").CurrentPage = "ind2"

A l’exécution, systématiquement, j'obtiens une <Erreur d'execution '5' / Argument ou appel de procédure incorrect.>

En résumé, si quelqu'un pouvait m'aider à trouver un moyen pour forcer un filtre TCD à une valeur de critère inexistant à l'instant donné (mais qui deviendra existant plus tard...), je lui en serait grandement reconnaissant

PS: pour finir et pour rire: il est possible de faire ce que je désire par une saisie manuelle en saisissant ind2 en H1. Pas d'erreur, le tableau se met à jour (il devient vide, logique) mais aucun moyen de le faire en VBA sans provoquer l'erreur... Grrrrrrr...

D'ici vos réponses libératrices, je vous souhaite toutes et tous une bonne soirée !!

89classeur1.xlsx (12.40 Ko)
Le 18/03/2019 à 21:15

Bonjour,

ajoute 1 ligne de plus à la source pour avoir le choix Vide

à tester

Sub test()
With ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("Index")
    .ClearAllFilters

    For Each it In .PivotItems
        If it = "ind2" Then .CurrentPage = "ind2" Else .CurrentPage = "(blank)"
    Next
End With
End Sub
Le 18/03/2019 à 23:52

Y a t il moyen de créer des lignes de références (Ind1 ; Ind2; et Ind3) qui seaient égales à Zero? comme ca, (si pas besoin de prendre en compte le nombre de lignes vides,) sans macro la ligne reste toujours et donc le filtre aussi ?

Le 19/03/2019 à 00:05

re,

je ne comprend pas,

on ne fait qu'une seule fois la définition de la source du tcd,

actuellement la source est Feuil1!$B$3:$E$16

il faut juste la changer pour Feuil1!$B$3:$E$17

sélectionne une cellule du tcd pour faire apparaître au menu du haut "Outils de tableau croisé dynamique"

sur l'onglet création, --> "Données", clic sur "Changer la source"

change le 6 pour 7

Le 19/03/2019 à 00:31

Bonjour à tous,

ta version d'excel dans ton profil est à jour ?

Si tu as >= 2007 tu peux remplacer ton filtre de page par un segment.

eric

87classeur1.xlsx (13.78 Ko)
Le 19/03/2019 à 17:27

Bonjour,

Je vous remercie pour vos contributions.

Effectivement vos réponses pourraient convenir mais l'objectif final n'est pas tout à fait atteint.

Ce que je cherche à faire, ce n'est pas de mettre le filtre à vide, mais plutôt d'y forcer ind2 alors qu'ind2 n'appparait pas dans la liste des critères possibles à l'instant où je le force. Le tableau devrait alors être vide vu que ind2 n'existe pas, mais lorsque ind2 apparaitra, au prochain rafraichissement du TCD, le tableau se remplira...

Je sais, c'est un peu tordu, mais le tableau n'a de sens qu'avec un filtre à ind2...

Merci de vos retours précieux...

Le 19/03/2019 à 17:40

Bonjour,

heuuuu, tu as vraiment testé le fichier que je t'avais mis ?

eric

Le 19/03/2019 à 18:02

Bonjour Eric,

Merci pour ta réponse.

J'ai bien testé ton fichier, mais le fait de passer par un segment affiche la liste de tous les cas possible (ind1 à ind4).

Dans ce cas simplifié, ce n'est pas un problème, mais dans mon cas réel, il y a un liste de cas possibles beaucoup plus long. Résultat : le segment devient énormément long... De plus, par définition le segment permet de modifier facilement le critère de filtre et donc je ne suis pas à l'abri d'une erreur opérateur qui modifierait le filtre par inadvertance...

Donc, je voudrais bien me passer de segment...

Désolé... Mais merci quand même.

Toute autre bonne idée est bien sûr bienvenue...

Pascal

Le 19/03/2019 à 19:35

Bonjour,

l'idée était de trouver un truc pour ne pas perdre le ind2.

Maintenant tu peux copier-coller le segment (supprimer l'original ensuite) sur une autre feuille que tu masques, ça fonctionnera toujours.

Et re-sélectionner l'item par macro :

Sub majTCD()
    Dim slC As SlicerCache, sl As Slicer, sli As SlicerItem
     ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotCache.Refresh
    For Each slC In ActiveWorkbook.SlicerCaches
        For Each sl In slC.Slicers
            If sl.Name = "Index 1" Then
                For Each sli In sl.SlicerCache.SlicerItems
                     sli.Selected = sli.Caption = "ind2" ' activation de l'item voulu, désactivation des autres
                Next sli
            End If
        Next sl
    Next slC
End Sub

Pas d'erreur, que tu aies ou pas des ind2

eric

PS : je n'avais pas regardé de plus près comme tu disais que ça plantait, mais si tu veux garder ton champ page met directement la valeur :

    ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotCache.Refresh
    ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("Index"). _
            ClearAllFilters
    [H1].Value = "ind2"
109classeur1.xlsm (23.68 Ko)
Le 20/03/2019 à 11:43

Merci Eric !

Un peu débordé aujourd'hui, mais je vais essayé dès que possible !

Je te tiens au courant.

Merci encore de ta disponibilité !

Pascal

Le 20/03/2019 à 17:50

Bonjour à tous,

J'ai fait des essais. Super, cela marche très bien !! Avec Slicer et en direct.

Seul dernier problème: Comment faire en direct ([H1]=.Value = "ind2") lorsqu'il y a plusieurs valeurs de critères, genre Ind2 et Ind3 ? Que faut-il mettre dans [H1] ?

Merci de vos retours.

Bonne soirée !

Pascal

Le 20/03/2019 à 18:20

Bonjour,

Seul dernier problème: Comment faire en direct ([H1]=.Value = "ind2") lorsqu'il y a plusieurs valeurs de critères, genre Ind2 et Ind3 ? Que faut-il mettre dans [H1] ?

Là je ne pense pas que ce soit possible.

Il faudrait jouer sur les PivotItems mais comme tu veux en mettre qui n'existent plus...

eric

Le 22/03/2019 à 18:40

Bonjour,

Suite de l'aventure...

Je cherche à récupérer la position (genre [H1]) de la cellule ou se trouve la valeur d'un filtre à partir du nom du TCD et du nom du filtre.

Quelqu'un aurait-il une idée en VBA ?

Mille mercis pour toutes vos contributions.

Pascal

Le 22/03/2019 à 19:00

Bonjour,

si tu es sûr à 120% que personne ne les a renommés :

Set pl = ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("Index").DataRange
Debug.Print pl.Address

Sinon il faudra boucler sur les captions des PivotFields pour le retrouver.

eric

Le 25/03/2019 à 09:23

Super, Eriiic !!

Ca marche super bien !!!

Merci pour tout pour l'instant !

Pascal

Rechercher des sujets similaires à "vba forcer filtre tcd valeur critere inexistante"