Erreur d'exécution '91' : Variable objet ou variable de bloc With non définie.

Version imprimable

Bonjour,

Je me tourne vers vous car j'ai cette fameuse erreur d'exécution '91' qui m'embette. J'ai beau chercher, je ne trouve pas la solution à mon problème.

Je vous ai mis la ligne à problème en rouge dans le code ci-dessous.

Petite explication sur la macro : Je vous ai fait parvenir que la partie qui était nécessaire (les autres sont sensiblement identiques : juste les critères et les variables qui changent).

Son but :
Choisir dans un menu déroulant les critères qui nous intéresse, puis lorsque l'on clique sur le bouton associé, sur une première page prédéfinie cela me filtre ma base de données, puis sur une autre feuille me le retranscrit en TCD.

J'ai essayé de rafistoler une première erreur (Lignes en gras), où j'avais l'erreur d'exécution '1004' : Cette commande requiert au moins deux lignes de données sources. Vous ne pouvez pas l'utiliser sur une seule ligne de données. Essayer la méthode suivante : si vous utilisez un filtre élaboré, sélectionnez la plage de cellules qui contient au moins deux lignes de données. Puis cliquez à nouveau sur la commande Filtre élaboré.

D'où le fait qu'il y est "On Error Resume Next" et "On Error GoTo 0".
Code:

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 103 104 105 106 'Définition des critères pour SES Sheets("Génération").Activate Else: [B1] = 4 And ([C1] = 1 Or [C1] = 2) And ([D1] = 1 Or [D1] = 2) And ([E1] = 1 Or [E1] = 2) And ([K1] = 1 Or [K1] = 2) And ([L1] = 1 Or [L1] = 2) 'Suppression TCD Sheets("Tableau").Activate Range("A1:Q300").Select Range("Q300").Activate Selection.Delete 'Suppresion Liste Sheets("Liste").Activate Range("A1:L400").Select Range("L400").Activate Selection.Delete 'Filtre élaboré pour trier la BD élève Sheets("BD Eleves").Range("A1:L400").AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=Sheets("Génération").Range("T17:AE18"), CopyToRange:=Sheets("Liste").Range( _ "A1:L1"), Unique:=False Sheets("Liste").Select 'Tri de la feuille Liste, par Option 4 puis 5 Cells.Select Selection.Sort Key1:=Range("H2"), Order1:=xlAscending, Key2:=Range("I2") _ , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _ False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _ :=xlSortNormal 'Déclaration des variables Range("A1").Select Dim wb2 As Workbook Dim ws5 As Worksheet, ws6 As Worksheet Dim PTCache2 As PivotCache Dim pt2 As PivotTable Dim rngPT2 As Range 'Optimisation (Gel Affichage) Application.ScreenUpdating = False 'Initialisation des variables Set wb2 = ActiveWorkbook Set ws5 = wb2.Worksheets("Liste") Set rngPT2 = ws5.Cells(1).CurrentRegion 'Données sources du TCD Set ws6 = wb2.Worksheets("Tableau") 'Suppression TCD On Error Resume Next ws6.PivotTables(1).TableRange2.Clear On Error GoTo 0 'Création du cache de TCD (à partir de rngPT) Set PTCache2 = wb2.PivotCaches.Add _ (SourceType:=xlDatabase, _ SourceData:=rngPT2) 'Création du TCD en feuille 'Tableau' nommé TCD_1 On Error Resume Next Set pt2 = PTCache2.CreatePivotTable _ (tabledestination:=ws6.Cells(6, 2), _ TableName:="TCD_1", _ defaultversion:=xlPivotTableVersion10) On Error GoTo 0 With pt2 'Calcul TCD manuel (Optimisation) .ManualUpdate = True 'Ajout des étiquettes de lignes et colonnes .AddFields RowFields:="OPTION ECO", _ ColumnFields:=Array("OPTION 4", "SEXE") 'Ajout champ valeurs With .PivotFields("NOM") .Orientation = xlDataField .Function = xlCount .NumberFormat = "#,##0" .Caption = "NB NOMS" End With 'Calcul automatique (affiche le TCD) .ManualUpdate = False End With wb2.ShowPivotTableFieldList = False With ws6 .Activate .[A1].Select End With Set rngPT2 = Nothing Set pt2 = Nothing Set PTCache2 = Nothing Set ws6 = Nothing: Set ws5 = Nothing Set wb2 = Nothing Sheets("Tableau").Activate End If End Sub
Merci à ceux qui prendront le temps de lire et de répondre à mon post.

18/05/2016, 09h26
antonysansh
Bonjour noemiesamira,

Lors du debug, pt2 est bien diffèrent de Nothing ?
Car avec
Code:

1 2 On Error Resume Next Set pt2 = ...
Il ce peut que pt2 ne soit référencé.

Sinon tu peux essayer avec

Code:

With wb2.pt2 ...
18/05/2016, 09h54
Menhir

Commence par virer de ton code tous les On Error Resume Next.
Faire du débugage avec cette instruction dans le code, c'est comme jouer à colin-maillard, ça empêche de voir les erreurs.
18/05/2016, 14h51
noemiesamira

1 pièce(s) jointe(s)

Les On Error Resume Next sont là justement parce que si je le enlève il y a des erreurs, et elles s'enchainent les unes sur les autres !

Je suis débutante en VBA, je vous mets le fichier en pièce jointe, car j'ai un peu de mal à comprendre comment je peux faire pour savoir que pt2 est bien différent de Nothing :/

Merci pour vos réponses ! Pièce jointe 210065
18/05/2016, 14h58
Menhir

Citation:

Envoyé par noemiesamira

Les On Error Resume Next sont là justement parce que si je le enlève il y a des erreurs, et elles s'enchainent les unes sur les autres !

Les On Error ne corrigent pas les erreurs, ils ne font que les masquer. Mais de toute façon, ça finit toujours par ressortir plus tard.
Il vaudrait mieux essayer de corriger l'erreur que de la masquer.

Citation:

Je suis débutante en VBA, je vous mets le fichier en pièce jointe, car j'ai un peu de mal à comprendre comment je peux faire pour savoir que pt2 est bien différent de Nothing :/

Tu mets un arrêt sur ton code juste avant la ligne qui plante et tu regarde le contenu de la variable dans la fenêtre Variable Locales.
18/05/2016, 15h12
noemiesamira

Ah d'accord ! Merci à toi, je n'en comprenais pas bien l'utilisation, je pensais que ça refaisait la manip' jusqu'à ce que ça marche.

J'ai donc mis le point d'arrêt et dans la fenêtre ça me dit "Nothing" pour tout, qu'est ce que cela veut dire ?
18/05/2016, 17h05
Menhir

Citation:

Envoyé par noemiesamira

Ah d'accord ! Merci à toi, je n'en comprenais pas bien l'utilisation, je pensais que ça refaisait la manip' jusqu'à ce que ça marche.

En informatique, théoriquement, quand tu fais deux fois la même action avec les mêmes paramètres, tu obtiens le même résultat (même si Windows est venu quelque peu ébranler cette règle, mais c'est une autre histoire).

Le On Error Resume Next, permet, lorsqu'on rencontre une erreur, de passer à la ligne suivante sans générer d'erreur.
Mais il n'empêche que si la commande n'a pas marché, ça aura forcément des conséquences sur la suite, que l'erreur apparaisse tout de suite ou non.

Donc, il vaut mieux chercher pourquoi l'erreur se produit et modifier le code en conséquence plutôt que de la shunter.

Citation:

J'ai donc mis le point d'arrêt et dans la fenêtre ça me dit "Nothing" pour tout, qu'est ce que cela veut dire ?

Ca veut dire que les variables en question ne contiennent rien.
Il va falloir que tu remontes le courant pour trouver la source du problème.

Peut-être que, justement, si tu n'avais pas mis de On Error, l'exécution se serait arrêtée plus tôt dans le code, ce qui aurait facilité la recherche du problème.
Comme je le disais, avec des On Error, rechercher un bug revient à jouer à clin-maillard : on doit y aller à tâtons.
19/05/2016, 08h59
noemiesamira

D'accord, les derniers 'On Error Resume Next' que j'ai inséré, sont ceux juste au dessus de la la ligne en rouge (cf: premier message); tout le bloc 'création du TCD en feuille 'tableau' nommé TCD_1.

J'avais le message : "erreur 1004 Cette commande requiert au moins deux lignes de données sources. Vous ne pouvez pas l’utiliser sur une seule ligne de données.
Essayez la méthode suivante :
- Si vous utilisez un filtre élaboré, sélectionnez une plage de cellules qui contient au moins deux lignes de donnée. Puis cliquez à nouveau sur la commande filtre élaboré. "

Ce qui est étrange, c'est que elle beug mais seulement pour ces critères là ! (cf:piece jointes messages précédents)
19/05/2016, 09h09
Menhir

Si pt2 est Nothing, ça veut dire que tu es en train d'essayer de faire un Update sur du vide.
Il est normal que VBA couine.

Je te l'ai dit, ton erreur est en amont.

Je pense que ton code a du généré une erreur bien au dessus. Tu as pensé t'en sortir en mettant un On Error qui masquait l'erreur.
Sauf que, comme rien n'était résolut, ça t'a forcément généré une autre erreur plus loin que tu a tenté de masquer de la même façon et ainsi de suite.

Je te le répète pour la dernière fois (parce que ça commence à être lassant) : vire tous tes On Error, remonte à la source du problème et corrige-le proprement.

Excuses-moi, j'aime bien comprendre pour faire les choses, et mieux, pour bien les refaire !

J'ai donc enlevé tout les On Error Resume Next.

Premier message d'erreur :
Erreur d'exécution '1004' : La méthode 'PivotTables' de l'objet '_Worksheet' a échoué.

Je te met la partie qui coince en couleur (vert) :

Code:

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
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
Option Explicit
Sub TEST_TRI()
'
' TEST_TRI Macro
' Macro enregistrée le 10/05/2016 par lycée


'Suppression TCD
  Sheets("Tableau").Activate
  Range("A1:Q300").Select
  Range("Q300").Activate
  Selection.Delete
  
  'Suppresion Liste
  Sheets("Liste").Activate
  Range("A1:L400").Select
  Range("L400").Activate
  Selection.Delete


    'Définition des critères pour CITEC
Sheets("Génération").Activate
If ([B1] = 1 Or [B1] = 2) And [C1] = 3 And ([D1] = 1 Or [D1] = 2) And ([E1] = 1 Or [E1] = 2) And ([K1] = 1 Or [K1] = 2) And ([L1] = 1 Or [L1] = 2) Then
    
    'Filtre élaboré pour trier la BD élève
    Sheets("BD Eleves").Range("A1:L400").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Sheets("Génération").Range("T4:AE5"), CopyToRange:=Sheets("Liste").Range( _
        "A1:L1"), Unique:=False
        
    Sheets("Liste").Select
    'Tri de la feuille Liste, par Option 4 puis 5
    Cells.Select
    Selection.Sort Key1:=Range("I2"), Order1:=xlAscending, Key2:=Range("H2") _
        , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
        False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
        :=xlSortNormal
       
    'Déclaration des variables
    Range("A1").Select
  
 Dim wb As Workbook
Dim ws As Worksheet, ws2 As Worksheet
Dim PTCache As PivotCache
Dim pt As PivotTable
Dim rngPT As Range

    'Optimisation (Gel Affichage)
    Application.ScreenUpdating = False

    'Initialisation des variables
    Set wb = ActiveWorkbook
    Set ws = wb.Worksheets("Liste")
    Set rngPT = ws.Cells(1).CurrentRegion     'Données sources du TCD
    Set ws2 = wb.Worksheets("Tableau")
    
    'Suppression TCD
    ws2.PivotTables(1).TableRange2.Clear
    
   
   'Création du cache de TCD (à partir de rngPT)
    Set PTCache = wb.PivotCaches.Add _
                  (SourceType:=xlDatabase, _
                   SourceData:=rngPT)
                   
    'Création du TCD en feuille 'Tableau' nommé TCD_1
    Set pt = PTCache.CreatePivotTable _
             (tabledestination:=ws2.Cells(6, 2), _
              TableName:="TCD_1", _
              defaultversion:=xlPivotTableVersion10)
    With pt
    
    'Calcul TCD manuel (Optimisation)
        .ManualUpdate = True
        
    'Ajout des étiquettes de lignes et colonnes
        .AddFields RowFields:=Array("OPTION 4", "OPTION ECO"), _
                   ColumnFields:="SEXE"
                   
     'Ajout champ valeurs
        With .PivotFields("NOM")
            .Orientation = xlDataField
            .Function = xlCount
            .NumberFormat = "#,##0"
            .Caption = "NB NOMS"
        End With
        
      'Calcul automatique (affiche le TCD)
        .ManualUpdate = False
    End With

    wb.ShowPivotTableFieldList = False

    With ws2
        .Activate
        .[A1].Select
    End With

    Set rngPT = Nothing
    Set pt = Nothing
    Set PTCache = Nothing
    Set ws2 = Nothing: Set ws = Nothing
    Set wb = Nothing
    
Sheets("Tableau").Activate

    'Définition des critères pour SC-IG
   
Sheets("Génération").Activate
ElseIf ([B1] = 1 Or [B1] = 2) And [C1] = 9 And ([D1] = 1 Or [D1] = 2) And ([E1] = 1 Or [E1] = 2) And ([K1] = 1 Or [K1] = 2) And ([L1] = 1 Or [L1] = 2) Then

'Suppression TCD
  Sheets("Tableau").Activate
  Range("A1:Q300").Select
  Range("Q300").Activate
  Selection.Delete
  
  'Suppresion Liste
  Sheets("Liste").Activate
  Range("A1:L400").Select
  Range("L400").Activate
  Selection.Delete


    'Filtre élaboré pour trier la BD élève
    Sheets("BD Eleves").Range("A1:L400").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Sheets("Génération").Range("T7:AE8"), CopyToRange:=Sheets("Liste").Range( _
        "A1:L1"), Unique:=False
        
    Sheets("Liste").Select
    'Tri de la feuille Liste, par Option 4 puis 5
    Cells.Select
    Selection.Sort Key1:=Range("I2"), Order1:=xlAscending, Key2:=Range("H2") _
        , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
        False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
        :=xlSortNormal
       
    'Déclaration des variables
    Range("A1").Select
    
 Dim wb1 As Workbook
Dim ws3 As Worksheet, ws4 As Worksheet
Dim PTCache1 As PivotCache
Dim pt1 As PivotTable
Dim rngPT1 As Range

    'Optimisation (Gel Affichage)
    Application.ScreenUpdating = False

    'Initialisation des variables
    Set wb1 = ActiveWorkbook
    Set ws3 = wb1.Worksheets("Liste")
    Set rngPT1 = ws3.Cells(1).CurrentRegion     'Données sources du TCD
    Set ws4 = wb1.Worksheets("Tableau")
    
    'Suppression TCD
    ws4.PivotTables(1).TableRange2.Clear
    
   
   'Création du cache de TCD (à partir de rngPT)
    Set PTCache1 = wb1.PivotCaches.Add _
                  (SourceType:=xlDatabase, _
                   SourceData:=rngPT1)
                   
    'Création du TCD en feuille 'Tableau' nommé TCD_1
    Set pt1 = PTCache1.CreatePivotTable _
             (tabledestination:=ws4.Cells(6, 2), _
              TableName:="TCD_1", _
              defaultversion:=xlPivotTableVersion10)
    With pt1
    
    'Calcul TCD manuel (Optimisation)
        .ManualUpdate = True
        
    'Ajout des étiquettes de lignes et colonnes
        .AddFields RowFields:=Array("OPTION 4", "OPTION ECO"), _
                   ColumnFields:="SEXE"
                   
     'Ajout champ valeurs
        With .PivotFields("NOM")
            .Orientation = xlDataField
            .Function = xlCount
            .NumberFormat = "#,##0"
            .Caption = "NB NOMS"
        End With
        
      'Calcul automatique (affiche le TCD)
        .ManualUpdate = False
    End With

    wb1.ShowPivotTableFieldList = False

    With ws4
        .Activate
        .[A1].Select
    End With

    Set rngPT1 = Nothing
    Set pt1 = Nothing
    Set PTCache1 = Nothing
    Set ws4 = Nothing: Set ws3 = Nothing
    Set wb1 = Nothing
    
    Sheets("Tableau").Activate
    
    'Définition des critères pour SES
Sheets("Génération").Activate
Else: [B1] = 4 And ([C1] = 1 Or [C1] = 2) And ([D1] = 1 Or [D1] = 2) And ([E1] = 1 Or [E1] = 2) And ([K1] = 1 Or [K1] = 2) And ([L1] = 1 Or [L1] = 2)

'Suppression TCD
  Sheets("Tableau").Activate
  Range("A1:Q300").Select
  Range("Q300").Activate
  Selection.Delete
  
  'Suppresion Liste
  Sheets("Liste").Activate
  Range("A1:L400").Select
  Range("L400").Activate
  Selection.Delete


    'Filtre élaboré pour trier la BD élève
    Sheets("BD Eleves").Range("A1:L400").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Sheets("Génération").Range("T17:AE18"), CopyToRange:=Sheets("Liste").Range( _
        "A1:L1"), Unique:=False
        
    Sheets("Liste").Select
    'Tri de la feuille Liste, par Option 4 puis 5
    Cells.Select
    Selection.Sort Key1:=Range("H2"), Order1:=xlAscending, Key2:=Range("I2") _
        , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
        False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
        :=xlSortNormal
       
    'Déclaration des variables
    Range("A1").Select
    
 Dim wb2 As Workbook
Dim ws5 As Worksheet, ws6 As Worksheet
Dim PTCache2 As PivotCache
Dim pt2 As PivotTable
Dim rngPT2 As Range

    'Optimisation (Gel Affichage)
    Application.ScreenUpdating = False

    'Initialisation des variables
    Set wb2 = ActiveWorkbook
    Set ws5 = wb2.Worksheets("Liste")
    Set rngPT2 = ws5.Cells(1).CurrentRegion     'Données sources du TCD
    Set ws6 = wb2.Worksheets("Tableau")
    
    
    'Suppression TCD
    ws6.PivotTables(1).TableRange2.Clear
    
   
   'Création du cache de TCD (à partir de rngPT)
    Set PTCache2 = wb2.PivotCaches.Add _
                  (SourceType:=xlDatabase, _
                   SourceData:=rngPT2)
                   
    
    'Création du TCD en feuille 'Tableau' nommé TCD_1
    Set pt2 = PTCache2.CreatePivotTable _
             (tabledestination:=ws6.Cells(6, 2), _
              TableName:="TCD_1", _
              defaultversion:=xlPivotTableVersion10)
  
    
    With pt2
    
    'Calcul TCD manuel (Optimisation)
          .ManualUpdate = True
        
    'Ajout des étiquettes de lignes et colonnes
        .AddFields RowFields:="OPTION ECO", _
                   ColumnFields:=Array("OPTION 4", "SEXE")
                   
     'Ajout champ valeurs
        With .PivotFields("NOM")
            .Orientation = xlDataField
            .Function = xlCount
            .NumberFormat = "#,##0"
            .Caption = "NB NOMS"
        End With
       
      'Calcul automatique (affiche le TCD)
        .ManualUpdate = False
    End With

    wb2.ShowPivotTableFieldList = False

    With ws6
        .Activate
        .[A1].Select
    End With

    Set rngPT2 = Nothing
    Set pt2 = Nothing
    Set PTCache2 = Nothing
    Set ws6 = Nothing: Set ws5 = Nothing
    Set wb2 = Nothing
    
    Sheets("Tableau").Activate
    
End If

  
 End Sub

19/05/2016, 13h56
Menhir

Mets un arrêt ou un Debug.Print juste avant et regarde ce qu'il y a dans ws2.

S'il n'y a pas ce qu'il faut dans cette variable, remonte au Set qui a servi à la renseigner et demande-toi pourquoi il n'a pas renseigné correctement la variable.
Et ainsi de suite, remonte jusqu'à la source de ton problème.
21/05/2016, 21h01
mjpmjp

TableRange2 est en lecture seule ?

bonjour,

Code:

ws2.PivotTables(1).TableRange2.Clear

peut être que TableRange2 est en lecture seule et que TableRange1 fonctionne

@+JP
23/05/2016, 10h30
noemiesamira

Bonjour,

Tout d'abord, excusez moi pour cette réponse tardive.

J'ai essayé de faire ce que vous m'avez dit tout les deux, sans aucuns succès.

Il me dit que ws2 est "Nothing" mais il correspond bien à ma feuille pourtant.

Je m'excuse de vous donner autant de mal (peut être qu'un jour j'arriverai à comprendre tout ça !)
23/05/2016, 11h34
Menhir

Citation:

Envoyé par noemiesamira

Il me dit que ws2 est "Nothing" mais il correspond bien à ma feuille pourtant.

Là, je ne comprends absolument pas ce que tu veux dire : comment une variable contenant Nothing pourrait correspondre à ta feuille ?

Citation:

J'ai essayé de faire ce que vous m'avez dit tout les deux, sans aucuns succès.

Visiblement, tu t'es contenté de la première ligne de mon message.
Fais ma suite.

Citation:

Envoyé par Menhir

S'il n'y a pas ce qu'il faut dans cette variable, remonte au Set qui a servi à la renseigner et demande-toi pourquoi il n'a pas renseigné correctement la variable.
Et ainsi de suite, remonte jusqu'à la source de ton problème.
23/05/2016, 14h05
noemiesamira

Dans le set, la variable a l'air bien définie.

Il me dit que ws2 correspond bien à ma 5ème feuille (Tableau) mais il n'y a aucune variable.
Dans cette feuille, les informations demandées doivent apparaitre sous forme de TCD, donc je pense que c'est pour ça qu'il n'y a pas de variable.

Je me trompe surement (c'est même presque certain)