Procedure trop longue

**Hanada** · 17/07/2017, 13h58

Bonjour La famille
J'ai besoin de votre aide, je suis dans l'impasse
j'ai conçu une macro qui traite 6 feuilles sur le mm classeur ( chaque feuille à un code particulier ) mais dans l’exécution de la macro on m'affiche le message suivant " Procédure trop longue"
Je voudrai séparer mon code en sous-parties et puis utiliser la fonction Call dans le Sub Général.
Voici le début de mon code ; c'est à partir du Bloc 1 que le code se répète pour chaque feuille

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
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
Sub path_Region()
If Sheets("1").ComboBox1.Value = "NA" Then
If Sheets("1").ComboBox4.Value = "Retail" Then
Worksheets("P_NA_Retail").Visible = True
Worksheets("P_NA_Retail").Select
Else
Worksheets("P_NA_HOD").Visible = True
Worksheets("P_NA_HOD").Select
End If
Else:
If Sheets("1").ComboBox1.Value = "IT" Then
Worksheets("P_IT").Visible = True
Worksheets("P_IT").Select
Else
If Sheets("1").ComboBox1.Value = "FRBE" Then
Worksheets("P_FRBE").Visible = True
Worksheets("P_FRBE").Select
Else
If Sheets("1").ComboBox1.Value = "EUROP" Then
Worksheets("P_EUROPE").Visible = True
Worksheets("P_EUROPE").Select
Else:
If Sheets("1").ComboBox1.Value = "AOA" Then
If Sheets("1").ComboBox4.Value = "Retail" Then
Worksheets("P_AOA_Retail").Visible = True
Worksheets("P_AOA_Retail").Select
Else
Worksheets("P_AOA_HOD").Visible = True
Worksheets("P_AOA_HOD").Select
End If
Else
If Sheets("1").ComboBox1.Value = "Latam" Then
Worksheets("P_Latam_Retail").Select
End If
End If
End If
End If
End If
End If
Dim Brand As String, Country As String
Country = Sheets("1").ComboBox2.Value
Brand = Sheets("1").ComboBox3.Value
If ActiveSheet.Name = "P_NA_Retail" Or ActiveSheet.Name = "P_NA_HOD" Or ActiveSheet.Name = "P_IT" Then
GoTo No_country
End If
Set Cellule_Country = ActiveSheet.Range("A1:BK1").Find(Country, lookat:=xlWhole)
Ligne_Country = Cellule_Country.Row
col_Country = Cellule_Country.Column
Set Cellule_Brand = ActiveSheet.Range(Cells(1, col_Country), Cells(620, col_Country)).Find(Brand, lookat:=xlWhole)
Ligne_Brand = Cellule_Brand.Row
Col_Brand = Cellule_Brand.Column
No_country:
Set Cellule_Brand = ActiveSheet.Range("A1:BK620").Find(Brand, lookat:=xlWhole)
Ligne_Brand = Cellule_Brand.Row
Col_Brand = Cellule_Brand.Column

' l le numéro de ligne ou commence Direct'
l = Ligne_Brand + 3
Do While ActiveSheet.Cells(l, Col_Brand - 4).Value <> 2 And l <= Ligne_Brand + 68
l = l + 1
Loop

' p le numéro de ligne ou commence Indirect'
P = l
Do While ActiveSheet.Cells(P, Col_Brand - 4).Value <> 3 And P <= Ligne_Brand + 68
P = P + 1
Loop

' q le numéro de ligne ou finit indirect +1'
q = P
Do While ActiveSheet.Cells(q, Col_Brand - 4).Value <> 4 And q <= Ligne_Brand + 68
q = q + 1
Loop

'Supply"""""""""""""""
' DL_S et l'indice de la dernière non vide de la sheet"supply"'
DL_S = 0
Do While Not IsEmpty(Sheets("Supply").Cells(DL_S + 1, 1))
DL_S = DL_S + 1
Loop
'Bloc1
If ActiveSheet.Name = "P_EUROPE" Then
Sheets("Supply").Activate
For j = Ligne_Brand + 3 To l - 1
'Somme de volume
Sheets("P_EUROPE").Cells(j, Col_Brand - 1).Value = Application.WorksheetFunction.SumIfs(Sheets("Supply").Range(Cells(2, "E"), Cells(DL_S, "E")), Sheets("Supply").Range(Cells(2, "B"), Cells(DL_S, "B")), Sheets("P_EUROPE").Cells(j, Col_Brand - 3), Sheets("Supply").Range(Cells(2, "F"), Cells(DL_S, "F")), Sheets("P_EUROPE").Cells(j, Col_Brand - 2), Sheets("Supply").Range(Cells(2, "C"), Cells(DL_S, "C")), Sheets("P_EUROPE").Cells(j, Col_Brand + 4))
'Somme de total liters
Sheets("P_EUROPE").Cells(j, Col_Brand).Value = Application.WorksheetFunction.SumIfs(Sheets("Supply").Range(Cells(2, "M"), Cells(DL_S, "M")), Sheets("Supply").Range(Cells(2, "B"), Cells(DL_S, "B")), Sheets("P_EUROPE").Cells(j, Col_Brand - 3), Sheets("Supply").Range(Cells(2, "F"), Cells(DL_S, "F")), Sheets("P_EUROPE").Cells(j, Col_Brand - 2), Sheets("Supply").Range(Cells(2, "C"), Cells(DL_S, "C")), Sheets("P_EUROPE").Cells(j, Col_Brand + 4))
'somme de nombre de pallets
Sheets("P_EUROPE").Cells(j, Col_Brand + 1).Value = Application.WorksheetFunction.SumIfs(Sheets("Supply").Range(Cells(2, "Q"), Cells(DL_S, "Q")), Sheets("Supply").Range(Cells(2, "B"), Cells(DL_S, "B")), Sheets("P_EUROPE").Cells(j, Col_Brand - 3), Sheets("Supply").Range(Cells(2, "F"), Cells(DL_S, "F")), Sheets("P_EUROPE").Cells(j, Col_Brand - 2))
If Sheets("P_EUROPE").Cells(j, Col_Brand - 2).Value = "Truck" Then
'Somme de TKM truck
Sheets("P_EUROPE").Cells(j, Col_Brand + 2).Value = Application.WorksheetFunction.SumIfs(Sheets("Supply").Range(Cells(2, "N"), Cells(DL_S, "N")), Sheets("Supply").Range(Cells(2, "B"), Cells(DL_S, "B")), Sheets("P_EUROPE").Cells(j, Col_Brand - 3))
Else
If Sheets("P_EUROPE").Cells(j, Col_Brand - 2) = "Intermodal Train" Then
'Somme de TKM train
Sheets("P_EUROPE").Cells(j, Col_Brand + 2).Value = Application.WorksheetFunction.SumIfs(Sheets("Supply").Range(Cells(2, "O"), Cells(DL_S, "O")), Sheets("Supply").Range(Cells(2, "B"), Cells(DL_S, "B")), Sheets("P_EUROPE").Cells(j, Col_Brand - 3))
Else
If Sheets("P_EUROPE").Cells(j, Col_Brand - 2) = "Intermodal Boat" Then
'Somme de TKM boat
Sheets("P_EUROPE").Cells(j, Col_Brand + 2).Value = Application.WorksheetFunction.SumIfs(Sheets("Supply").Range(Cells(2, "P"), Cells(DL_S, "P")), Sheets("Supply").Range(Cells(2, "B"), Cells(DL_S, "B")), Sheets("P_EUROPE").Cells(j, Col_Brand - 3))
End If
End If
End If
'Somme de Gross weight
Sheets("P_EUROPE").Cells(j, Col_Brand + 3).Value = Application.WorksheetFunction.SumIfs(Sheets("Supply").Range(Cells(2, "R"), Cells(DL_S, "R")), Sheets("Supply").Range(Cells(2, "B"), Cells(DL_S, "B")), Sheets("P_EUROPE").Cells(j, Col_Brand - 3), Sheets("Supply").Range(Cells(2, "F"), Cells(DL_S, "F")), Sheets("P_EUROPE").Cells(j, Col_Brand - 2), Sheets("Supply").Range(Cells(2, "C"), Cells(DL_S, "C")), Sheets("P_EUROPE").Cells(j, Col_Brand + 4))
If Sheets("P_EUROPE").Cells(j, Col_Brand + 3).Value = 0 Then
Sheets("P_EUROPE").Cells(j, Col_Brand + 3).Value = 1
End If
Next j

'________________________________________________________________________________________________
'Direct"""""""""""""""
' DL_D et l'indice du la dernière non vide de la sheet"Direct"'
DL_D = 0
Do While Not IsEmpty(Sheets("Direct").Cells(DL_D + 1, 1))
DL_D = DL_D + 1
Loop
Sheets("Direct").Activate
For jj = l To P - 1
'Somme de volume
Sheets("P_EUROPE").Cells(jj, Col_Brand - 1).Value = Application.WorksheetFunction.SumIfs(Sheets("Direct").Range(Cells(2, "G"), Cells(DL_D, "G")), Sheets("Direct").Range(Cells(2, "B"), Cells(DL_D, "B")), Sheets("P_EUROPE").Cells(jj, Col_Brand - 3), Sheets("Direct").Range(Cells(2, "H"), Cells(DL_D, "H")), Sheets("P_EUROPE").Cells(jj, Col_Brand - 2))
'Somme de total liters
Sheets("P_EUROPE").Cells(jj, Col_Brand).Value = Application.WorksheetFunction.SumIfs(Sheets("Direct").Range(Cells(2, "O"), Cells(DL_D, "O")), Sheets("Direct").Range(Cells(2, "B"), Cells(DL_D, "B")), Sheets("P_EUROPE").Cells(jj, Col_Brand - 3), Sheets("Direct").Range(Cells(2, "H"), Cells(DL_D, "H")), Sheets("P_EUROPE").Cells(jj, Col_Brand - 2))
'somme de nombre de pallets
Sheets("P_EUROPE").Cells(jj, Col_Brand + 1).Value = Application.WorksheetFunction.SumIfs(Sheets("Direct").Range(Cells(2, "S"), Cells(DL_D, "S")), Sheets("Direct").Range(Cells(2, "B"), Cells(DL_D, "B")), Sheets("P_EUROPE").Cells(jj, Col_Brand - 3), Sheets("Direct").Range(Cells(2, "H"), Cells(DL_D, "H")), Sheets("P_EUROPE").Cells(jj, Col_Brand - 2))
If Sheets("P_EUROPE").Cells(jj, Col_Brand - 2) = "Truck" Then
'Somme de TKM truck
Sheets("P_EUROPE").Cells(jj, Col_Brand + 2).Value = Application.WorksheetFunction.SumIfs(Sheets("Direct").Range(Cells(2, "P"), Cells(DL_D, "P")), Sheets("Direct").Range(Cells(2, "B"), Cells(DL_D, "B")), Sheets("P_EUROPE").Cells(jj, Col_Brand - 3))
Else
If Sheets("P_EUROPE").Cells(jj, Col_Brand - 2) = "Intermodal Train" Then
'Somme de TKM train
Sheets("P_EUROPE").Cells(jj, Col_Brand + 2).Value = Application.WorksheetFunction.SumIfs(Sheets("Direct").Range(Cells(2, "Q"), Cells(DL_D, "Q")), Sheets("Direct").Range(Cells(2, "B"), Cells(DL_D, "B")), Sheets("P_EUROPE").Cells(jj, Col_Brand - 3))
Else
If Sheets("P_EUROPE").Cells(jj, Col_Brand - 2) = "Intermodal Boat" Then
'Somme de TKM boat
Sheets("P_EUROPE").Cells(jj, Col_Brand + 2).Value = Application.WorksheetFunction.SumIfs(Sheets("Direct").Range(Cells(2, "R"), Cells(DL_D, "R")), Sheets("Direct").Range(Cells(2, "B"), Cells(DL_D, "B")), Sheets("P_EUROPE").Cells(jj, Col_Brand - 3))
End If
End If
End If
'Somme de Gross weight
Sheets("P_EUROPE").Cells(jj, Col_Brand + 3).Value = Application.WorksheetFunction.SumIfs(Sheets("Direct").Range(Cells(2, "T"), Cells(DL_D, "T")), Sheets("Direct").Range(Cells(2, "B"), Cells(DL_D, "B")), Sheets("P_EUROPE").Cells(jj, Col_Brand - 3), Sheets("Direct").Range(Cells(2, "H"), Cells(DL_D, "H")), Sheets("P_EUROPE").Cells(jj, Col_Brand - 2))
If Sheets("P_EUROPE").Cells(jj, Col_Brand + 3).Value = 0 Then
Sheets("P_EUROPE").Cells(jj, Col_Brand + 3).Value = 1
End If
Next jj
'_________________________________________________________________________________________________________
'Indirect"""""""""""""""
' DL_I et l'indice du la dernière non vide de la sheet"InDirect"'
DL_I = 0
Do While Not IsEmpty(Sheets("Indirect").Cells(DL_I + 1, 1))
DL_I = DL_I + 1
Loop
Sheets("Indirect").Activate
For jjj = P To q - 1
'Somme de volume
Sheets("P_EUROPE").Cells(jjj, Col_Brand - 1).Value = Application.WorksheetFunction.SumIfs(Sheets("Indirect").Range(Cells(2, "G"), Cells(DL_I, "G")), Sheets("Indirect").Range(Cells(2, "B"), Cells(DL_I, "B")), Sheets("P_EUROPE").Cells(jjj, Col_Brand - 3), Sheets("Indirect").Range(Cells(2, "H"), Cells(DL_I, "H")), Sheets("P_EUROPE").Cells(jjj, Col_Brand - 2))
'Somme de total liters
Sheets("P_EUROPE").Cells(jjj, Col_Brand).Value = Application.WorksheetFunction.SumIfs(Sheets("Indirect").Range(Cells(2, "O"), Cells(DL_I, "O")), Sheets("Indirect").Range(Cells(2, "B"), Cells(DL_I, "B")), Sheets("P_EUROPE").Cells(jjj, Col_Brand - 3), Sheets("Indirect").Range(Cells(2, "H"), Cells(DL_I, "H")), Sheets("P_EUROPE").Cells(jjj, Col_Brand - 2))
'somme de nombre de pallets
Sheets("P_EUROPE").Cells(jjj, Col_Brand + 1).Value = Application.WorksheetFunction.SumIfs(Sheets("Indirect").Range(Cells(2, "S"), Cells(DL_I, "S")), Sheets("Indirect").Range(Cells(2, "B"), Cells(DL_I, "B")), Sheets("P_EUROPE").Cells(jjj, Col_Brand - 3), Sheets("Indirect").Range(Cells(2, "H"), Cells(DL_I, "H")), Sheets("P_EUROPE").Cells(jjj, Col_Brand - 2))
If Sheets("P_EUROPE").Cells(jjj, Col_Brand - 2) = "Truck" Then
'Somme de TKM truck
Sheets("P_EUROPE").Cells(jjj, Col_Brand + 2).Value = Application.WorksheetFunction.SumIfs(Sheets("Indirect").Range(Cells(2, "P"), Cells(DL_I, "P")), Sheets("Indirect").Range(Cells(2, "B"), Cells(DL_I, "B")), Sheets("P_EUROPE").Cells(jjj, Col_Brand - 3))
Else
If Sheets("P_EUROPE").Cells(jjj, Col_Brand - 2) = "Intermodal Train" Then
'Somme de TKM train
Sheets("P_EUROPE").Cells(jjj, Col_Brand + 2).Value = Application.WorksheetFunction.SumIfs(Sheets("Indirect").Range(Cells(2, "Q"), Cells(DL_I, "Q")), Sheets("Indirect").Range(Cells(2, "B"), Cells(DL_I, "B")), Sheets("P_EUROPE").Cells(jjj, Col_Brand - 3))
Else
If Sheets("P_EUROPE").Cells(jjj, Col_Brand - 2) = "Intermodal Boat" Then
'Somme de TKM boat
Sheets("P_EUROPE").Cells(jjj, Col_Brand + 2).Value = Application.WorksheetFunction.SumIfs(Sheets("Indirect").Range(Cells(2, "R"), Cells(DL_I, "R")), Sheets("Indirect").Range(Cells(2, "B"), Cells(DL_I, "B")), Sheets("P_EUROPE").Cells(jjj, Col_Brand - 3))
End If
End If
End If
'Somme de Gross weight
Sheets("P_EUROPE").Cells(jjj, Col_Brand + 3).Value = Application.WorksheetFunction.SumIfs(Sheets("Indirect").Range(Cells(2, "T"), Cells(DL_I, "T")), Sheets("Indirect").Range(Cells(2, "B"), Cells(DL_I, "B")), Sheets("P_EUROPE").Cells(jjj, Col_Brand - 3), Sheets("Indirect").Range(Cells(2, "H"), Cells(DL_I, "H")), Sheets("P_EUROPE").Cells(jjj, Col_Brand - 2))
If Sheets("P_EUROPE").Cells(jjj, Col_Brand + 3).Value = 0 Then
Sheets("P_EUROPE").Cells(jjj, Col_Brand + 3).Value = 1
End If
Next jjj
End If

**Menhir** · 17/07/2017, 15h49

Envoyé par Hanada

dans l’exécution de la macro on m'affiche le message suivant " Procédure trop longue"
Je voudrai séparer mon code en sous-parties et puis utiliser la fonction Call dans le Sub Général.

Je ne pense pas que ce soit le nombre de ligne dans le module qui pose problème (perso, j'ai déjà fait des modules bien plus gros).
A mon avis, ça doit plutôt être une boucle qui tourne à l'infini à cause d'une erreur d'algorithme.

Mets des balises ou des affichages Debug.Print dans ton code pour localiser la partie qui pose problème et comprendre ce qui est à modifier.

**Hanada** · 17/07/2017, 15h55

Merci Menhir pour ton retour
le code que j'ai publié c'est pas en totalité il manque encors des blocs ( mais ce sont les mm que le bloc 1 sauf qu'ils sont adapté aux feuilles cibles ) donc je sais si le probléme vient du fiat que j'ai mis beaucoup de lignes ou c'est autre chose

**Menhir** · 17/07/2017, 16h05

Même si le code que tu as mis est reproduit une centaine de fois, ce n'est pas ça qui posera problème.
Je pense que persister à investiguer dans cette voie sera une perte de temps inutile.

**Hanada** · 17/07/2017, 16h07

D'accord je vais voir d'ou vient le problème
Merci beaucoup

**Jean-Philippe André** · 17/07/2017, 16h39

Salut,

l'erreur est-elle vraiement "procedure trop longue" ? Car l'exacte erreur serait "procedure trop grande", lie a la taille du fichier que prendrait le code...

**Hanada** · 17/07/2017, 17h01

Oui j'ai exactement cette erreur qui s'affiche 'Procedure trop grande" et j'arrive pas à résoudre le probléme

**Jean-Philippe André** · 17/07/2017, 17h06

OK, donc ce cas, il te faut envisager les pistes suivantes :
- optimiser ton code en enlevant les etapes inutiles
- decouper ton code en plusieurs modules

**Hanada** · 17/07/2017, 17h10

oui c'est le découpage qui m'a posé probleme, j'arrive pas distinguer entre les variables globales et locales

**Jean-Philippe André** · 17/07/2017, 17h16

Alors sans aller jusqu'a ces aspects la, tu peux deja tres simplement faire un decoupage comme cet exemple

Code :

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

1
2
3
4
5
6
7
8
Sub GrosseSub()
A
B
C
D
E
F
End Sub

en la transformant en

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
Sub GrosseSub()
PetiteSub1
PetiteSub2
End Sub
 
Sub PetiteSub1()
A
B
C
End Sub
 
Sub PetiteSub2()
D
E
F
End Sub

**eriiic** · 17/07/2017, 17h41

Bonjour,

A regarder ton code on voit énormément de SumIfs
Tes données de départ ne se prêtent pas à des TCD ?
Ca allégerait considérablement ta macro si 90% de tes calculs sont déjà fait (en longueur mais pas mal en temps aussi).
eric

Procedure trop longue

Macros et VBA Excel

Discussions similaires

Partager

Partager