XL 2013 VBA : filtre multicritères d'un TCD selon une liste définie dans un array

[erictran]

Bonjour la communauté!

Je travaille sur un TCD créé en VBA : je voudrais que les champs du pivotFields "Utilisateur creat. cde" soient filtrés par rapport à une liste de valeurs "string" contenues dans un array nommé "tableau_SECU"

J'ai essayé 2 manières :

Avec une boucle For Each PivotItems et une boucle IF imbriquée , mais le champ correspondant du TCD est filtré pour toutes les valeurs sauf sur le dernier élément de la liste du "tableau_SECU" :

With .PivotFields("Utilisateur creat. cde")
        For Each valeur In .PivotItems
             For i=1 to Nbre_agents
             if valeur.name<>tableau_SECU(i,1) then
                    valeur.visible=false
            Next
        Next
      
 End With

et celle ci car cela me semblait plus propre avec une boucle For Each PivotItems et une boucle WHILE qui permet de passer au PivotItems suivant dès que l'on trouve une valeur de PivotItems égale à une valeur de l'array "tableau_SECU" mais ca bug en syntaxe sur le "End while" je ne comprends pas pourquoi

With .PivotFields("Utilisateur creat. cde")
          For Each valeur In .PivotItems
                                i = 1
                     While valeur.Name <> tableau_SECU(i, 1) And i < Nbre_agents
                     i = i + 1
                     valeur.Name.Visible = False
                    End while
        Next
      
    End With

Voilà, il y aurait peut être une solution en mettant les valeurs de pivotItems et du tableau dans deux "scripting dictionnary" puis comparer les éléments des 2 comme condition pour passer la valeur du filtre à visible ?

Edit : j'ai essayé les segments également mais comment le paramétrer automatiquement avec les éléments du tableau (qui en comporte une quarantaine)?
Edit2 : je suis sur excel 2013


Merci infiniment pour votre aide!

 

[xUpsilon]

Bonjour,

Si le problème est juste que ça ne fonctionne pas sur la dernière valeur de tableau_SECU, c'est que c'est ton i qui ne varie pas correctement, mais comme on ne sait pas comment tableau_SECU est construit, ce n'est pas évident.
Quoi qu'il en soit, j'utiliserais plutôt ceci :

for i=LBound(tableau_SECU) to UBound(tableau_SECU)

LBound est l'index minimum de ton tableau (sa toute première ligne, que son premier élément commence à 0 comme à 18000), et UBound est l'index maximum de ton tableau (la fameuse dernière valeur sur laquelle il ne boucle pas pour le moment).

Essaye avec ça et reviens nous voir.

Bonne soirée,

 

[erictran]

Merci pour ta réponse.

J'ai bien vérifié avec un debug.print la liste des éléments de tableau_SECU et meme avec un debug.print valeur.name ; tableau_SECU(i,1) pour vérifier qu'il existe bien des valeurs de pivotitems identiques à des valeurs de tableau_SECU
le debug.print sous condition if valeur.name=tableau(i,1) renvoie bien OK

mais rien n'y fait : c'est comme si il ne trouvait pas la correspondance valeur.name et tableau_SECU(i,1) alors que le debug print me les affiche bien.

Je mets tout le code, peut être que l'erreur vient d'ailleurs?

Sub CreerTCD()

Application.ScreenUpdating = False

Dim plage As Range
Dim derlig As Integer
Dim PTcache As PivotCache
Dim PTtable As PivotTable
Dim tableau_SECU() As String
Dim Nbre_agents As Integer

Erase tableau_SECU

'création du tableau membres sécu avec la liste des noms de la feuille "filtres"
derlig1 = Worksheets("Filtres").Cells(Rows.Count, 5).End(xlUp).Row
ReDim tableau_SECU(derlig1 - 1, 1)
For i = 1 To derlig1 - 1
        tableau_SECU(i, 1) = CStr(Worksheets("Filtres").Range("E" & i + 1))
Next
 
'cherche la dernière ligne de la base de données pour création du TCD
With Worksheets("Comparaison")
                If .FilterMode Then
                .ShowAllData
                End If
                derlig = .Range("B" & Rows.Count).End(xlUp).Row
End With


'création du TCD
Set PTcache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=Worksheets("Comparaison").Range("B2:AI" & derlig))
Set PTtable = Worksheets("Par_processus").PivotTables.Add(PivotCache:=PTcache, TableDestination:=Worksheets("Par_processus").Range("H2"), TableName:="TCD_sécu")

With Worksheets("Par_processus").PivotTables("TCD_sécu")
.PivotFields("Utilisateur creat. cde").Orientation = xlRowField
.AddDataField ActiveSheet.PivotTables("TCD_sécu").PivotFields("Mt engagé"), "Somme de Mt engagé", xlSum
.AddDataField ActiveSheet.PivotTables("TCD_sécu").PivotFields("Mt eng. non reçu"), "Somme de Mt eng. non reçu", xlSum
.PivotFields("Utilisateur creat. cde").ClearAllFilters
.PivotFields("Somme de Mt eng. non reçu").NumberFormat = "#,##0"
.PivotFields("Somme de Mt engagé").NumberFormat = "#,##0"
.PivotFields("Utilisateur creat. cde").EnableMultiplePageItems = True

'Filtrage des pivotfield avec les noms issus de tableau_SECU
  
    With .PivotFields("Utilisateur creat. cde")
          For Each valeur In .PivotItems
                For i = LBound(tableau_SECU) To UBound(tableau_SECU)
                If valeur.Name <> tableau_SECU(i, 1) Then
                valeur.Visible = False
                Else
             If valeur.Name = tableau_SECU(i, 1) Then
               Debug.Print "OK"
               Else: Debug.Print "NOK"
                End If
                End If
                Next
        Next
       
    End With

Merci!

Eric

 

[xUpsilon]

Bonjour,

Une remarque, le ReDim, s'il est construit avec ReDim(x,y) part de l'index 0, sauf que tu remplis de 1 à derlig1 - 1, donc tableau_SECU(0,1) est vide :

ReDim tableau_SECU(derlig1 - 1, 1)
For i = 1 To derlig1 - 1

Il y a autre chose qui me choque. Dans ta boucle tu attribues le .Visible = False dès que tu trouves un élément de ton tableau qui est différent de ton Item dans ton TCD. Mais tu ne le remets jamais en visible, donc concrètement ça veut dire que si l'élément de ton TCD ne correspond pas à tous les éléments de tableau_SECU à la fois, il sera forcément retiré du filtre.
Sauf que les filtres VBA pour TCD ne permettent que de filtrer de manière à ce qu'il reste un élément minimum dans le TCD, on ne peut pas descendre à aucune valeur affichée.
Donc mon idée, c'est que ta macro retire toutes les lignes de ton TCD, puis en arrivant à la dernière elle ne peut pas le faire car on ne peut pas filtrer un TCD qui n'affiche rien.

Encore une fois, sans fichier, difficile à dire.

Bonne journée,

 

[erictran]

Bonjour,

Merci pour ton aide,

J'ai mis les éléments à exporter vers tableau_SECU dans une colonne E , avec un entête de colonne "SECU" :
le "-1" est pour enlever l'en-tête de colonne dans le comptage des lignes. . oui j'aurai préférablement pu intégrer le -1 dans derlig cela auraît été plus propre

Je comprends bien ta deuxième remarque , et c'est bien ce qui se passe car seul le filtre à partir du dernier élément de tableau_SECU est appliqué
pourtant j'ai bien plusieurs éléments du filtre dans le TCD , j'ai vraisemblablement un problème de compréhension de ce que ma boucle "for Each valeur in .PivotItems" fait :

ma compréhension :

Par défaut , tous les pivoitems sont visibles à la création du TCD donc ont la propriété visible=True

Boucle For Each : Pour chacune des valeurs de PivoItems
=> - Boucle For i : pour chacun des éléments de tableau_SECU
- teste si il y a une correspondance entre valeur PivotItems et élément de tableau_SECU
- sans correspondance : le pivoitems correspondant est filtré : application de la valeur false à la propriété Visible
- passe à la valeur suivante de PivotItems

Mais pourtant , le code se deroule comme si la boucle "for i " était avant la boucle "for each et écrase les valeurs précédentes du filtre pour les remettre à false , le debug.print montre bien qu'il ya des correspondances .. bref je ne comprend pas j'ai besoin de vos lumières

J'ai mis le fichier anonymisé en PJ,

merci d'avance!

Eric

 

[xUpsilon]

Eric,

En effet, le -1 est compréhensible même si pas propre, mais le 1 n'est pas bon.
Le problème est le suivant, tu as un tableau qui fait ça :
1
2
3
4

derlig1 = Worksheets("Filtres").Cells(Rows.Count, 5).End(xlUp).Row 'Ligne du 4
ReDim tableau_SECU(derlig1 - 1, 1) 'Donc tu redim de tableau_SECU(3,1), sauf que 3 correspond à 0 To 3
For i = 1 To derlig1 - 1 'Ici i commence à 1, donc tu boucles de 1 à 3, et non de 0 à 3

Ta boucle ici teste :
- pour chaque élément de tableau_SECU :
- est-ce que la valeur observée correspond à l'élément de tableau_SECU
- si ce n'est pas la même on masque
- si c'est la même, on ne fait rien

Sauf que du coup :
A moins que tous les éléments de tableau_SECU soient égaux à la valeur observée, sinon tu trouveras toujours au moins un élément de tableau_SECU qui renverra "valeur observée <> élément de tableau_SECU", et qui donc masquera la ligne.
Donc avec cette boucle, quoi qu'il arrive tu vas masquer.

Pour masquer si la valeur observée n'existe pas une seule fois dans la boucle, il te faut un témoin :

With .PivotFields("Utilisateur creat. cde")
    For Each valeur In .PivotItems
        Temoin=0
        For i = LBound(tableau_SECU) To UBound(tableau_SECU)
             If valeur.Name = tableau_SECU(i, 1) Then
                Temoin=1
                Exit For
             End If
        Next
        If Temoin=0 Then :
            valeur.Visible = False
        Temoin = 0
    Next
 End With

Ton témoin prend la valeur 1 si on trouve la valeur au moins une fois dans tableau_SECU et évite ainsi de masquer. Si on ne trouve jamais d'égalité, le Temoin reste à 0, et une fois qu'on a bouclé sur tous les éléments de tableau_SECU, on peut donc dire que la valeur n'est pas dans le tableau de recherche, et on peut donc masquer la ligne.

J'espère avoir été clair et avoir correctement compris ton problème. Je n'ai pas forcément le temps de m'attarder sur le fichier, aussi fais le test et dis moi si je n'ai pas bien compris ce que tu souhaitais

Bonne journée,

PS : Fais attention à l'indentation dans ton code aussi, c'est important pour la lisibilité.

 

[erictran]

D'accord ! j'ai compris , il manquait la sortie de la boucle après être passé "visible" !

Super je teste mais c'est sûr que c'est ca

edit : c'est bien ca , code corrigé et qui tourne !

Mille mercis!