Problème tableau croisé dynamique avec variable

**liop49** · 16/05/2012, 18h37

Bonjour,

J'ai un tableau croisé dynamique sur une feuille "TCD" qui doit donner des résultats en fonction d'une variable (UserForm1.TextBox3.Value) obtenue dans un formulaire

Voici le code :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
    Sheets("TCD").Select
    Range("B6").Select
    ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("Code produit" _
        ).CurrentPage = UserForm1.TextBox3.Value
 
    ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotCache.Refresh
 
 
    Sheets("donnees").Select

Il fonctionne bien si la variable existe par contre si la variable du formulaire n'existe pas dans le

PivotFields("Code produit

, il ne me dit

Erreur d'exécution 1004 Impossible de définir la propriété default de la classe PivotItem

ce qui est normal car la valeur demandée n'existe pas.
Ma question est la suivante :
Est ce que l'on peut éviter ce message qui impose un débogage ou une fin ?
Il faudrait juste un message disant que la valeur n'existe pas.

Est ce que quelqu'un peut m'aider ?

Merci

P.S. : je vous mets le fichier en pièce jointe
L'exemple à faire est Rocou comme plante dans un premier temps puis Argile verte ensuite.

**78chris** · 16/05/2012, 19h15

Bonjour

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
Private Sub ComboBox4_Change()
Dim vrech As Range
 
Sheets("TCD").Select
Range("B6").Select
 
For Each cas In ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("Code produit").PivotItems
        If cas = "PRE12" Then
            ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("Code produit" _
                ).CurrentPage = UserForm1.TextBox3.Value
            ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotCache.Refresh
            Exit For
        End If
    Next cas

**liop49** · 17/05/2012, 00h24

Bonsoir,

Je viens d'essayer la solution de 78Chris mais cela change rien et en plus je ne comprends pas pourquoi tu imposes "PRE12"

Dans mon exemple, j'ai mis 2 valeurs possibles mais en réalité j'ai plus de 700 codes produits et seuls 400 sont présents dans les données du tableau croisés dynamique.

D'ailleurs, je viens de me rendre compte qu'avec toute base de données (6500 lignes) le tableau croisé dynamique délire !!!!
Je m'explique si je rentre une nouvelle donnée sur un produit alors le croisé dynamique ne la prend pas en considération (j'ai biens ur mis une plage de donnée suffisante pour que les nouvelle svaleurs soit dans la plage des valeurs prises en compte).
Le truc encore plus bizare, c'est que si je demande via le formulaire un code produit non présent dans la base alors au lieu de m'afficher

Erreur d'exécution 1004 Impossible de définir la propriété default de la classe PivotItem

il me laisse l'ancien filtre et dans le povotItem il a remplacé le code produit inexistant par l'ancien

Est ce que quelqu'un a déjà rencontré ce problème ?
Merci d'avance pour votre aide car je ne voit pas comment faire.

Nini

P.S. : l'objectif du croisé dynamique est de m'afficher une statistique sur les 4 dernières années glissantes sur deux paramètres des données (moyenne des pertes et moyenne des rendement par le produit sélectionné dans le formulaire)

Voici le code complet utilisé dans Module 1:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
Sub enregistre()
 
    Dim EntreePlus As Worksheet, ZZ As Range, Erg, i As Integer, AA As Range, BB As Range, EE As Range, XX As Range, YY As Range, FF As Range, GG As Range, HH As Range, II As Range, JJ As Range, KK As Range, LL As Range, MM As Range, NN As Range, OO As Range
    Dim valeur As String
 
    Set EntreePlus = ThisWorkbook.Worksheets("donnees")
 
    Application.ScreenUpdating = True
 
 
 
    If UserForm1.TextBox1.Value <> Format(UserForm1.TextBox1, "dd/MM/yyyy") Then
        MsgBox "Veuillez renseigner la date sous le format jj/mm/aaaa"
        Exit Sub
    End If
 
    If UserForm1.TextBox1.Value = "" Then
        MsgBox "Veuillez renseigner la date de fabrication"
        Exit Sub
    End If
 
    If UserForm1.ComboBox1.Value = "" Then
        MsgBox "Veuillez renseigner le nom de l'opérateur!"
        Exit Sub
    End If
 
    If UserForm1.TextBox5.Value = "" Then
        MsgBox "Veuillez entrer une quantité entrée"
        Exit Sub
    End If
 
    If UserForm1.TextBox6.Value = "" Then
        MsgBox "Veuillez entrer une quantité sortie"
        Exit Sub
    End If
 
    If UserForm1.TextBox11.Value = "0" And UserForm1.TextBox12.Value = "0" Then
        MsgBox "Veuillez entrer le temps de la fabrication"
        Exit Sub
    End If
 
    If UserForm1.TextBox2.Value = "" Then
        UserForm1.TextBox2.Value = "-"
    End If
 
    If UserForm1.ComboBox3.Value = "" Then
        UserForm1.ComboBox3.Value = "-"
    End If
 
    If UserForm1.ComboBox4.Value = "" Then
        UserForm1.ComboBox4.Value = "-"
    End If
 
    If UserForm1.TextBox13.Value = "" Then
        UserForm1.TextBox13.Value = "-"
    End If
 
 
    Ligne = EntreePlus.Cells(16384, 1).End(xlUp).Offset(1, 0).Row
    Set XX = EntreePlus.Cells(Ligne, 1) 'Date
    Set ZZ = EntreePlus.Cells(Ligne, 2) 'Opérateur
    Set YY = EntreePlus.Cells(Ligne, 3) 'Plante utilisée
    Set AA = EntreePlus.Cells(Ligne, 4) 'Code produit de la plante utilisée
    Set BB = EntreePlus.Cells(Ligne, 5) 'N° de lot
    Set EE = EntreePlus.Cells(Ligne, 6) 'Quantité entrée
    Set GG = EntreePlus.Cells(Ligne, 7) 'Quantité sortie
    Set LL = EntreePlus.Cells(Ligne, 8) 'Temps passé
    Set MM = EntreePlus.Cells(Ligne, 9) 'Matériel utilisé
    Set NN = EntreePlus.Cells(Ligne, 10) 'Opération
    Set OO = EntreePlus.Cells(Ligne, 11) 'Perte
    Set PP = EntreePlus.Cells(Ligne, 12) 'Rendement
    Set QQ = EntreePlus.Cells(Ligne, 13) 'Commentaire
    Set RR = EntreePlus.Cells(Ligne, 14) 'Pertes autres
 
    XX.Offset(0, i).Value = UserForm1.TextBox1.Text 'Format(UserForm1.TextBox1, "dd/MM/yyyy")
    ZZ.Offset(0, i).Value = UserForm1.ComboBox1.Value
    YY.Offset(0, i).Value = UserForm1.ComboBox2.Value
    AA.Offset(0, i).Value = UserForm1.TextBox3.Text
    BB.Offset(0, i).Value = UserForm1.TextBox4.Text
    EE.Offset(0, i).Value = UserForm1.TextBox5.Text
    GG.Offset(0, i).Value = UserForm1.TextBox6.Text
    'HH.Offset(0, i).Value = UserForm1.TextBox7.Text
    'II.Offset(0, i).Value = UserForm1.TextBox8.Text
    'JJ.Offset(0, i).Value = UserForm1.TextBox9.Text
    'KK.Offset(0, i).Value = UserForm1.TextBox10.Text
    LL.Offset(0, i).Value = (UserForm1.TextBox11.Text) + ((UserForm1.TextBox12.Text) / 60)
    MM.Offset(0, i).Value = UserForm1.ComboBox3.Value
    NN.Offset(0, i).Value = UserForm1.ComboBox4.Value
    OO.Offset(0, i).Value = ((UserForm1.TextBox5.Text) - (UserForm1.TextBox6.Text)) / (UserForm1.TextBox5.Text)
    PP.Offset(0, i).Value = (UserForm1.TextBox6.Text) / ((UserForm1.TextBox11.Text) + ((UserForm1.TextBox12.Text) / 60))
    QQ.Offset(0, i).Value = UserForm1.TextBox2.Value
    RR.Offset(0, i).Value = UserForm1.TextBox13.Value
 
 
 
Unload UserForm1
 
Application.ScreenUpdating = True
 
    MsgBox "Les données ont été enregistrées avec succés"
 
End Sub

Voic le code dans UserForm1 :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
 
Private Sub CommandButton1_Click()
Module1.enregistre
End Sub
 
Private Sub CommandButton2_Click()
Unload UserForm1
End Sub
 
Private Sub ComboBox2_Change()
Dim vrech As Range
'je recherche dans la colonne C la valeur de la combo
Set vrech = Sheets("Liste").Columns("C:C").Find(Me.ComboBox2.Value)
 
'si je trouve une valeur alors j'affiche la valeur correspondante de la
'colonne D dans le textbox
If Not vrech Is Nothing Then
  Me.TextBox3.Value = vrech.Offset(0, 1).Value
 
'sinon j'affiche un message
Else
MsgBox "Aucune valeur trouvée!"
End If
 
 
End Sub
 
Private Sub ComboBox4_Change()
Dim vrech As Range
 
    Sheets("TCD").Select
    Range("B6").Select
    ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("Code produit" _
        ).CurrentPage = UserForm1.TextBox3.Text
 
    ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotCache.Refresh
 
 
    Sheets("donnees").Select
 
 
'je recherche dans la colonne B la valeur de la combo
Set vrech = Sheets("TCD").Columns("A:A").Find(Me.ComboBox4.Value)
 
'si je trouve une valeur alors j'affiche la valeur correspondante de la
'colonne D dans le textbox
If Not vrech Is Nothing Then
  Me.TextBox7.Value = vrech.Offset(0, 1).Value
  Me.TextBox7.Value = Format(Me.TextBox7, "0%")
 
  Me.TextBox8.Value = vrech.Offset(0, 2).Value
  Me.TextBox8.Value = Format(Me.TextBox8, "#")
 
'sinon j'affiche un message
Else
MsgBox "Aucune valeur trouvée pour les statistiques moyennes !"
End If
 
 
 
 
UserForm1.TextBox9.Value = ((UserForm1.TextBox5.Text) - (UserForm1.TextBox6.Text)) / (UserForm1.TextBox5.Text)
UserForm1.TextBox9.Value = Format(UserForm1.TextBox9.Value, "0%")
 
UserForm1.TextBox10.Value = (UserForm1.TextBox6.Text) / ((UserForm1.TextBox11.Text) + ((UserForm1.TextBox12.Text) / 60))
UserForm1.TextBox10.Value = Format(UserForm1.TextBox10.Value, "#")
 
End Sub

**78chris** · 17/05/2012, 09h44

Bonjour

Effectivement j'avais fais des tests et oublié de remettre la référence au userform

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
For Each cas In ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("Code produit").PivotItems
        If cas = UserForm1.TextBox3.Value Then
            ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("Code produit" _
                ).CurrentPage = UserForm1.TextBox3.Value
            ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotCache.Refresh
            Exit For
        End If
Next cas

Ceci pour tester que la valeur existe avant de modifier le filtre.

Concernant d'anciennes valeurs :
Il faut savoir que lorsqu'Excel crée un TCD, il crée un cache contenant le cube de données.
Ce cube n'est jamais complétement apuré lorsque des données source disparaissent. A partir de la version 2007 on peut demander à Excel d'"oublier" les données et reconstituer le cube.
Pour les versions précédentes, il faut utiliser VBA pour purger.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
Sub deleteOldItemsWB()
'gets rid of unused items in pivotTable
'Debra Dalgleish - based on MSKB (202232)
Dim ws As Worksheet
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim i As Integer
On Error Resume Next
For Each ws In ActiveWorkbook.Worksheets
    For Each pt In ws.PivotTables
        pt.RefreshTable
        For Each pf In pt.PivotFields
            For Each pi In pf.PivotItems
                If pi.RecordCount = 0 And _
                Not pi.IsCalculated Then
                pi.Delete
                End If
            Next
        Next
    Next
Next
End Sub

Concernant les nouvelles données : je te conseille de déclarer ta liste source en liste, puis de redéfinir la source de ton TCD afin qu'il prenne en compte cette liste. Ainsi l'ajout de données sera pris en compte automatiquement.

**liop49** · 21/05/2012, 09h59

Bonjour

Merci Chris pour ta réponse que je viens de travailler !

Ton code fonctionne mais ca rame sérieusement donc je ne peux pas continuer avec

Je pense partir sur ta proposition

je te conseille de déclarer ta liste source en liste, puis de redéfinir la source de ton TCD

Ma question est la suivante comment mettre en variable du TCD une liste de données ne se situant pas dans la plage de données du tableau crosiée dynamique.

J'ai les onglets suivants : "Liste", "TCD" et "donnees". Le tableau croisé dynamique de TCD est lié aux données de l'onglet "donnees".
Je voudrai mettre dans mon code VBA, une variable dans le PivotFields tapant sur une liste de l'onglet "liste" :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
    ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("Matériel utilisé" _
        ).CurrentPage = Liste_operation

Sachant que Liste_opération est égale à

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=Liste!$F$1:$F$51

Suis je clair ?

En l'espérant et en espérant avoir un retour de votre part pour me sortir de ce bourbier

Nini

**78chris** · 21/05/2012, 11h34

Bonjour

Non je n'ai pas tout compris concernant la dernière question !

Reprécise et joins le fichier (éventuellement sur ci-joint)

Pour le code qui rame :
Le test de la valeur s'arrête dès que la valeur est trouvée grâce à la ligne

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Exit For

donc cela ne devrait pas tant ramer sauf si tu as des milliers de cas et que la valeur est à la fin...

As-tu purgé ton cache avant ?

Tu peux aussi prévoir une gestion d'erreur pour reprendre quand ça plante mais c'est, à mon avis, moins propre.

**liop49** · 21/05/2012, 12h10

Bonjour Chris et merci pour votre patience,

Pour le fait que ca rame, je ne peux pas vous dire ce qui se passe. Du coup, j'ai changé mon fusil d'épaule et j'ai essayé de modifié mon tableau croisé dynamique pour qu'il y ait moins de contrainte mais je suis encore bloqué.
L'objectif sera d'avoir toutes les entrées possibles dans mes PivotFields et pour se faire je voudrais qu'ils soient fonction d'une liste définie et non sur les données

Pour la purge, le code ne fonctionne pas ?

Ci-joint le fichier

Merci

nini

**Daniel.C** · 21/05/2012, 12h26

Bonjour,

Tu pourrais ajouter ce test avant de changer la valeur du champ de page :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
    If Not IsNumeric(Application.Match(UserForm1.TextBox3.Value, [donnees!D:D], 0)) Then
        MsgBox "Saisie incorrecte"
        Exit Sub
    End If

en adaptant le code à ton besoin.

**liop49** · 21/05/2012, 13h33

@Daniel.C

Merci pour ta réponse

: j'ai réussi à la travailler pour obtenir ce que je voulais.

Reste mon problème sur mon tableau croisé dynamique. Les données dans les PivotField ne sont pas représentatives des valeurs de l'onglet données !!!!
Par ailleurs, si je fais des ajouts de données, il ne les prends pas en compte malgré un refresh des données ????

Le problème de Chris n'y change rien :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
Sub deleteOldItemsWB()
'gets rid of unused items in pivotTable
'Debra Dalgleish - based on MSKB (202232)
Dim ws As Worksheet
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim i As Integer
On Error Resume Next
For Each ws In ActiveWorkbook.Worksheets
    For Each pt In ws.PivotTables
        pt.RefreshTable
        For Each pf In pt.PivotFields
            For Each pi In pf.PivotItems
                If pi.RecordCount = 0 And _
                Not pi.IsCalculated Then
                pi.Delete
                End If
            Next
        Next
    Next
Next
End Sub

Par avance merci pour votre aide

Nini

**Daniel.C** · 21/05/2012, 15h27

J'ai créé une liste avec les données source (elle s'appelle "Liste1" par défaut).
Une fois pour toutes, tu codes :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Sheets("TCD").PivotTables(1).SourceData = "donnees!" & Sheets("donnees").ListObjects("Liste1").Range.Address(, , xlR1C1)

Les données que ajouteras seront automatiquement prises en compte après actualisation :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Sheets("TCD").PivotTables("Tableau croisé dynamique1").PivotCache.Refresh

**liop49** · 22/05/2012, 12h12

Bonjour,

Désolé pour le temps de ma réponse.

Lorsque tu dis :
1.

J'ai créé une liste avec les données source (elle s'appelle "Liste1" par défaut).

Quelle est la zone définie par cette liste ?

2.

Une fois pour toutes, tu codes :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Sélectionner tout - Visualiser dans une fenêtre à partSheets("TCD").PivotTables(1).SourceData = "donnees!" & Sheets("donnees").ListObjects("Liste1").Range.Address(, , xlR1C1)

Je mets ce code en remplacement d'un existant ou je l'ajoute dans le code existant ?

Merci

Nini

**Daniel.C** · 22/05/2012, 15h43

Pour la définition de la liste, j'ai sélectionné la plage A2:O7034 (Regarde le classeur joint. Une fois cela fait, il faut exécuter la macro :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
Sub test2()
Sheets("TCD").PivotTables(1).SourceData = "donnees!" & _
Sheets("donnees").ListObjects("Liste1").Range.Address(, , xlR1C1)
End Sub

qui relie la liste au TCD, une fois pour toute.

**liop49** · 22/05/2012, 15h52

Merci Daniel

Je ne trouve pas la macro test2 ?

**Daniel.C** · 22/05/2012, 15h55

Non, effectivement, puisque je l'ai déjà exécutée. Tu peux l'ajouter dans l'un des modules standards si tu le souhaites.

**liop49** · 22/05/2012, 16h04

Oulala

Je suis largué

J'ai remis le Test2 dans un module et j'ai défini liste1 dans l'onglet donnée
Lorsque je lance Test2, j'ai une erreur d'exécution '9' - L'indice n'appartient pas à la sélection

Aie aie, je crois que je suis trop nul pour y arriver

Nini la loose