Macro pour cacher des items dans un TCD

[Sydney]

Bonjour,

Voici mon probleme.
J'ai un tableau croise dynamique dont je souhaiterais cacher, par macro, certaines lignes en fonction d'une condition posee sur la valeur d'une des donnees du TCD.
Je sais que ce n'est pas clair. Pour plus de clarte, voir le fichier joint.

Dans l'exemple de ce fichier, la macro (qui n'est pas de moi) repond plus ou moins bien a ce que je veux faire. Je voudrais par exemple afficher uniquement les lignes pour lesquelles la valeur du champs "Share" est inferieure ou egale a 5. En cliquant sur le bouton "Hide shares by criteria" et en entrant la condition ">1 <=5", on obtient le bon resultat.

Le premier probleme, c'est que la macro renvoit une erreur quand on utilise uniquement le critere "<", alors qu'elle fonctionne bien avec le critere ">" seul. Elle ne fonctionne pas non plus quand on entre en premier critere ">0". Quelqu'un saurait-il ajuster ca?

Mais mon vrai probleme est le suivant :
Le TCD sur lequel je veux utiliser cette macro est legerement different du fichier joint, en ce que le champs que je veux evaluer (en l'occurrence le champs "Share") ne peut pas etre mis dans la zone ligne, mais seulement dans la zone donnee (cf. colonne "Average of Share" dans le fichier test). Or la macro que j'ai ici ne fonctionne pas sur les champs de donnees. De plus, les valeurs a evaluer ne sont pas des nombres entiers, mais des pourcentages a une decimale (et je souhaite conserver les lignes dont la valeur "Share" est inferieure a 5%).

Voila, j'espere avoir ete assez comprehensible.

Quelqu'un saurait-il comment adapter cette macro?

 

[Sydney]

Re : Macro pour cacher des items dans un TCD

Bon, je crois que ma premiere explication n'etait pas claire.
Apres de longues heures de recherches infructueuses, je reviens exposer mon probleme avec plus de clarte.

J'ai un TCD du type de celui en fichier joint ("test3.xls") dans lequel je veux cacher toutes les lignes dont la valeur du chamsp "Share" est inferieur a 5%. Le probleme est que ce champs "Share" est un champs calcule (de formule ='My Wgt' /'Mkt Wgt', donc le rapport entre "My Weight" et "Mkt Weight"). Or je n'ai pas trouve le moyen d'utiliser les valeurs calculees pour realiser mon tri. J'ai lu quelque part qu'il suffisait de remplacer le terme "PivotFields" par "CalculatedFields" dans la macro. J'ai donc essaye ca :

Sub Macro_1()
'essai en prenant la variable dans le champs calcule

Dim pt As PivotTable
Set pt = Worksheets("Sheet4").PivotTables("PivotTable1")

'verifie chaque item du champs "Share"
For Each ci In pt.CalculatedFields("Shr").CalculatedItems
For Each pi In pt.PivotFields("Client").PivotItems
'repere les items dont la valeur est inferieure a 5%
If ci.Value > 0.05 Then
'cache la ligne correspondante
'(par l'intermediaire du champs "Client" car on ne peut pas cacher a partir du champs calcule "Share")
pi.Visible = False
End If
Next pi
Next ci

End Sub​

Malheureusement, il ne semble pas apprecier le "CalculatedFields"...
La macro fonctionne cependant bien dans le cas d'un champs pivot (non calcule) comme "My Wgt" (cf. "macro_1_bis" dans le fichier).

J'ai donc essaye en definissant plusieurs variables qui me permettraient de retrouver la valeur du champs "Share" sans passer par le CalculatedItem. Ca donne :

Sub Macro_2()

Dim pt As PivotTable
Dim pi As PivotItem
Dim pj As PivotItem
Dim pk As PivotItem
Set pt = Worksheets("Sheet4").PivotTables("PivotTable1")

For Each pi In pt.PivotFields("My Wgt").PivotItems
For Each pj In pt.PivotFields("Mkt Wgt").PivotItems
For Each pk In pt.PivotFields("Client").PivotItems

If pi.Value / pj.Value > 0.05 Then
pk.Visible = False
End If

Next pk
Next pj
Next pi

End Sub​

Mais je crois que je definis mal les variables car le resultat est etonnant. Je pense que ce que je n'arrive pas a faire, c'est faire en sorte que la division ne se fasse qu'entre les valeurs d'une meme ligne a chaque fois.

J'ai enfin tente avec la fonction "Offset" :

Sub Macro_3()

Dim pt As PivotTable
Dim pi As PivotItem
Set pt = Worksheets("Sheet4").PivotTables("PivotTable1")

For Each pi In pt.PivotFields("My Wgt").PivotItems
For Each pj In pt.PivotFields("Mkt Wgt").PivotItems
If pi.Value / pj.Value > 0.05 Then
pi.DataRange.Activate
ActiveCell.Offset(rowoffset:=0, columnoffset:=-3).Select
ActiveCell.Delete
End If
Next pj
Next pi

End Sub

Encore une fois le resultat n'est pas le bon, mais je ne comprends pas car si je remplace la ligne
If pi.Value / pj.Value > 0.05 Then
par la ligne
If pi.Value / pj.Value = 0.86 Then
alors elle me supprime bien la ligne avec la "Share" = 86%​

J'avais aussi pense a trier le tableau puis a utiliser la fonction Do Until ou Do While, mais je ne sais pas comment elles fonctionnent et je ne sais pas si elles peuvent servir ici.

Pour resumer, quelqu'un connaitrait-il un moyen direct ou contourne de supprimer les lignes d'un TCD en fonction de valeurs calculees?

Merci d'avance.

 

[GIBI]

Re : Macro pour cacher des items dans un TCD

Bonjour,

la manipulation des TCD en VBA n'est pas très simple à comprendre. Voici une solution qui répond à ta demande (si j'ai compris...).

Remarques :

une première boucle permet d'enlever tous les filtres.
Pour cacher les lignes j'ai fait une double boucle pour tenir compte du décalage de ligne lors d'une "suppression" de ligne (en fait elle n'est pas affichée).


Sub Macro_1()
'essai en prenant la variable dans le champs calcule


'enlever tous les filtres
On Error Resume Next
For Each pvtfield In Worksheets("Sheet4").PivotTables("PivotTable1").PivotFields
For Each pvtitem In pvtfield.PivotItems
pvtitem.Visible = True
Next
Next
On Error GoTo 0
' masquer les lignes en fonction d'une valeur

Set pt = Worksheets("Sheet4").PivotTables("PivotTable1") ' .PivotFields("Data").PivotItems("Share")
' NbLigne = pt.ColumnFields("DATA").PivotItems("Share").DataRange.Count

modif = True
While modif
modif = False
For Each CI In pt.ColumnFields("DATA").PivotItems("Share").DataRange
'repere les items dont la valeur est inferieure a 5%
If CI.Value > 0.05 Then
'cache la ligne correspondante
'(par l'intermediaire du champs "Client" car on ne peut pas cacher a partir du champs calcule "Share")
ligne = CI.Row
Cells(ligne, 2).Delete ' pas de panique = la ligne est filtrée
modif = True
Exit For
End If
Next
Wend

End Sub

Ce n'est qu'une solution, mais qui a le mérite de fonctionner

GIBI

 

[Sydney]

Re : Macro pour cacher des items dans un TCD

Bonjour Gibi,

Merci pour ta macro. Elle fonctionne tres bien !
Par contre, j'ai du suspendre le passage "enlever tous les filtres", car le tableau sur lequel je travaille contiens plus de 70 champs et cette verification alourdissait tellement la macro qu'apres plusieurs minutes je n'en voyais toujours pas le bout. D'autant que cette macro est destinee a etre inseree dans une autre, qui definit deja la configuration des champs affiches, du style :
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Client")
.Orientation = xlRowField
.Position = 1
etc.
puis:
ActiveSheet.PivotTables("PivotTable1").PivotFields("Client"). _
CurrentPage = "(All)"

J'ai aussi adapte le "Cells(ligne, 2).Delete" en "Cells(ligne, 1).Delete", car la colonne que je veux trier dans mon tableau est uniquement la colonne A (il n'y a en fait pas d'autres champs en colonne comme dans mon fichier test, mais j'ai propose cette mise en page au cas ou je me retrouverai dans cette configuration... pourquoi faire simple quand on peut faire complique? ).
D'ailleurs, je n'ai pas compris comment ta macro trie a la fois les colonnes A et B, alors que la colonne A n'est pas designee dans la macro.

Je n'ai pas non plus compris l'histoire des "modif" et je n'ai pas trouve d'explication dans VBA Help. Enfin, je ne suis qu'un debutant en VBA.

Merci encore !

Sydney

 

[Sydney]

Re : Macro pour cacher des items dans un TCD

Rebonjour,

Je vais paraitre perfectionniste, mais existe-t-il un moyen de trier le tableau (par ordre croissant sur le champs "Share" par exemple... ca je sais faire) pour ensuite supprimer toutes les lignes jusqu'a ce que la macro rencontre une valeur inferieure a 5% dans le champs "Share" (une formule du type Until peut-etre?). A ce moment-la la macro s'arreterait.

Je me demande en fait si une telle macro ne permettrait pas d'alleger le calcul (plutot que de verifier toutes les colonnes une a une). Mais je me trompe peut-etre...

Sydney

 

[GIBI]

Re : Macro pour cacher des items dans un TCD

Bonjour,

Tu te lance dans de la programmation sans en connaitre le b à Ba, j'admets donc tes incompréhension

Modif est une variable qui me permet de relancer le traitement tant qu'il me restent des lignes à cacher d'où le tant que (While modif). dés que je trouve une modif je lui affecte la valeur TRUE. j'aurais pu écrire While Modif = TRUE en VBA ce n'est nécessaire la Variable Modif étant assimilée par défaut à un BOOLEEN


La macro ne trie pas, mais elle cache ou plutôt elle filtre UNE ligne via le .DELETE, Une ligne est identifiée par l'ensemble des colonnes Client+Mkt Wgt+My Wgt ==> le DELETE va donc tenir compte de l'identification complète de la ligne. dans l'exemple on peut prendre commme référence les colonne 1 2 ou 3 c'est la ligne qui compte.

GIBI