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