simplification d'une boucle

**Daniel117** · 28/07/2015, 12h02

Bonjour,

je me suis debrouillé pour faire une macro qui dois s'executer sur une vingtaine de feuilles d'un classeur.
la structure conditionnelle du code l'emmene à faire des boucle et les executions sont les mêmes sur certaines feuilles .
seulement j'ai du mal à simplifier l'ecriture du code qui m'a pris un temps fou et en plus je ne peux pas masquer l'execution du code . Du coup l'ecran est mise à jour à chaque fois.
j'ai essaiyé d'inserer

Code :

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

Applicationscreen.updating = False

mais ça ne marche pas.
pour l'execution du code:
- elle est identique sur les feuilles BS_y+1 à BS_y+5 (5 feuilles) puis BS_01 à BS_12 soit 12 feuilles, 3 feuilles distinctes.
le but est de simplifier les boucles sur les feuilles òù l'execution est identique.
les traitements se déclenchent à partir des test qui s'effectuent sur un code saisi en inputbox. si ce code fait partie d'une des valeurs d'un tableau sur une autre feuille , sinon un autre traitement se declenche.
le code marche mais est très long je voudrais le simplifier en bouclant sur les feuilles ou`n le traitement est identique et simplifier les 2 grandes parties
je suis débutant j'aimerai ameliorer merci d'avance de votre aide. parce que je dois le dupliquer
voici un extrait du code

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
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
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
Private Sub Workbook_Open()
 
Dim entity As String
Dim i As Integer
Dim x As Variant
nb_codes = Sheets("DATA IS décaissé").Cells(4, 5).End(xlDown).Row 'tu obtiens le nombre de codes que tu as
 
Sheets("BS").Unprotect
Sheets("PARAMETERS").Select
entity = InputBox("Saisissez votre code entité en Majuscule SVP            Please enter your entity code in Capital Letter : ")
Range("D5").Select
Range("D5").Value = entity
MsgBox ("Code entité valide,Entity code is ok")
 
nb_codes = Sheets("DATA").Cells(4, 5).End(xlDown).Row 
 
ReDim x(nb_codes)
 
For i = 1 To nb_codes
11   x(i) = Sheets("DATA").Cells(i, 5)
Next i
 
For i = 1 To UBound(x)
  If Sheets("PARAMETERS").Range("D5") = x(i) Then 
      '1
         Sheets("BS").Select
         Sheets("BS").Unprotect
 
        Sheets("BS").Cells.Locked = False
        'verrouillage du calcul des flux F99, F15 et F50 du compte A44200
         Sheets("BS").Range("H119").Locked = True
         Sheets("BS").Range("M119").Locked = True
         Sheets("BS").Range("Y119").Locked = True
 
         'verrouillage du calcul des flux F99, F15 et F50 du compte A46710
 
         Sheets("BS").Range("H131").Locked = True
         Sheets("BS").Range("M131").Locked = True
         Sheets("BS").Range("Y131").Locked = True
 
        'verrouillage du calcul des flux F99, F15 et F50 du compte P44200
         Sheets("BS").Range("H266").Locked = True
         Sheets("BS").Range("M266").Locked = True
         Sheets("BS").Range("Y266").Locked = True
        'verrouillage du COMPTES R695 sur les 11 premiers mois du budget
 
        ActiveSheet.Protect
        Sheets("P&L_NATURE_BUDGET").Select
        Sheets("P&L_NATURE_BUDGET").Unprotect
 
        Sheets("P&L_NATURE_BUDGET").Cells.Locked = False
        Range("C200:P200").Locked = True
        ActiveSheet.Protect
         '2
         Sheets("BS_Y+1").Select
        'verrouillage du calcul des flux F99, F15 et F50 du compte A44200 sur le BS Y+1
          Sheets("BS_Y+1").Unprotect
         Sheets("BS_Y+1").Cells.Locked = False
         Sheets("BS_Y+1").Range("H115").Locked = True
         Sheets("BS_Y+1").Range("M115").Locked = True
         Sheets("BS_Y+1").Range("Z115").Locked = True
 
         'verrouillage du calcul des flux F99, F15 et F50 du compte A46710
 
         Sheets("BS_Y+1").Range("H127").Locked = True
         Sheets("BS_Y+1").Range("M127").Locked = True
         Sheets("BS_Y+1").Range("Z127").Locked = True
 
         'verrouillage du calcul des flux F99, F15 et F50 du compte P44200 sur le BS Y+1
 
         Sheets("BS_Y+1").Range("H260").Locked = True
         Sheets("BS_Y+1").Range("M260").Locked = True
         Sheets("BS_Y+1").Range("Z260").Locked = True
         ActiveSheet.Protect
         '3
          Sheets("BS_Y+2").Select
          'verrouillage du calcul des flux F99, F15 et F50 du compte A44200 sur le BS Y+1
          Sheets("BS_Y+2").Unprotect
 
          Sheets("BS_Y+2").Cells.Locked = False
         Sheets("BS_Y+2").Range("H115").Locked = True
         Sheets("BS_Y+2").Range("M115").Locked = True
         Sheets("BS_Y+2").Range("Z115").Locked = True
 
         'verrouillage des flux F99, F15 et F50 du compte A46710
 
         Sheets("BS_Y+2").Range("H127").Locked = True
         Sheets("BS_Y+2").Range("M127").Locked = True
         Sheets("BS_Y+2").Range("Z127").Locked = True
 
         'verrouillage du calcul des flux F99, F15 et F50 du compte P44200 sur le BS Y+1
 
         Sheets("BS_Y+2").Range("H260").Locked = True
         Sheets("BS_Y+2").Range("M260").Locked = True
         Sheets("BS_Y+2").Range("Z260").Locked = True
         Sheets("BS_Y+2").Protect
         'même traitement de la feuille Y+1 à Y+5
 
        'Sur le budget
 
        Sheets("BS_01").Select
          'verrouillage du calcul des flux F99, F15 et F50 du compte A44200 sur le BS Y+1
          Sheets("BS_01").Unprotect
          Sheets("BS_01").Cells.Locked = False
         Sheets("BS_01").Range("H115").Locked = True
         Sheets("BS_01").Range("M115").Locked = True
         Sheets("BS_01").Range("Y115").Locked = True
         'verrouillage des flux F99, F15 et F50 du compte A46710
 
         Sheets("BS_01").Range("H126").Locked = True
         Sheets("BS_01").Range("M126").Locked = True
         Sheets("BS_01").Range("Y126").Locked = True
 
         'verrouillage du calcul des flux F99, F15 et F50 du compte P44200 sur le BS Y+1
 
         Sheets("BS_01").Range("H259").Locked = True
         Sheets("BS_01").Range("M259").Locked = True
         Sheets("BS_01").Range("Y259").Locked = True
         ActiveSheet.Protect
 
         Sheets("BS_02").Select
          'verrouillage du calcul des flux F99, F15 et F50 du compte A44200 sur le BS Y+1
          Sheets("BS_02").Unprotect
          Sheets("BS_02").Cells.Locked = False
         Sheets("BS_02").Range("H115").Locked = True
         Sheets("BS_02").Range("M115").Locked = True
         Sheets("BS_02").Range("Y115").Locked = True
         'verrouillage des flux F99, F15 et F50 du compte A46710
 
         Sheets("BS_02").Range("H126").Locked = True
         Sheets("BS_02").Range("M126").Locked = True
         Sheets("BS_02").Range("Y126").Locked = True
 
         'verrouillage du calcul des flux F99, F15 et F50 du compte P44200 sur le BS Y+1
 
         Sheets("BS_02").Range("H259").Locked = True
         Sheets("BS_02").Range("M259").Locked = True
         Sheets("BS_02").Range("Y259").Locked = True
         ActiveSheet.Protect
 
         Sheets("BS_03").Select
          'verrouillage du calcul des flux F99, F15 et F50 du compte A44200 sur le BS Y+1
          Sheets("BS_03").Unprotect
         Sheets("BS_03").Cells.Locked = False
         Sheets("BS_03").Range("H115").Locked = True
         Sheets("BS_03").Range("M115").Locked = True
         Sheets("BS_03").Range("Y115").Locked = True
         'verrouillage des flux F99, F15 et F50 du compte A46710
 
         Sheets("BS_03").Range("H126").Locked = True
         Sheets("BS_03").Range("M126").Locked = True
         Sheets("BS_03").Range("Y126").Locked = True
 
         'verrouillage du calcul des flux F99, F15 et F50 du compte P44200 sur le BS Y+1
 
         Sheets("BS_03").Range("H259").Locked = True
         Sheets("BS_03").Range("M259").Locked = True
         Sheets("BS_03").Range("Y259").Locked = True
         ActiveSheet.Protect
 
   'même traietement sur chaque feuille même cellule jusqu'à la feuille BS_12
 
    Sheets("PARAMETERS").Select
 
    Exit Sub 'pas besoin de continuer à boucler, on sort de la macro
 
  End If
 
Next i
 
'2eme PARTIE
 
For i = 1 To UBound(x)
  If Sheets("PARAMETERS").Range("D5") <> x(i) Then 'si la valeur de la cellule correspond à une du variant
       'copier-coller de votre code précédent
      '1
        Sheets("BS").Select
         Sheets("BS").Unprotect
        'verrouillage du calcul des flux F99, F15 et F50 du compte A44200
         Range("H119").ClearContents
         Range("Y119").ClearContents
         Sheets("BS").Range("M118").Select
         Selection.AutoFill Destination:=Range("M118:M119"), Type:=xlFillDefault
 
         'verrouillage du calcul des flux F99, F15 et F50 du compte A46710
 
         Sheets("BS").Range("H131").ClearContents
         Sheets("BS").Range("Y131").ClearContents
         Sheets("BS").Range("M130").Select
         Selection.AutoFill Destination:=Range("M130:M131"), Type:=xlFillDefault
        'verrouillage du calcul des flux F99, F15 et F50 du compte P44200
         Sheets("BS").Range("H266").ClearContents
         Sheets("BS").Range("Y266").ClearContents
         Sheets("BS").Range("M265").Select
         Selection.AutoFill Destination:=Range("M265:M266"), Type:=xlFillDefault
        '2
        Sheets("P&L_NATURE_BUDGET").Select
        Sheets("P&L_NATURE_BUDGET").Unprotect
        Range("F200:P200").ClearContents
        ActiveSheet.Protect
 
         Sheets("BS_Y+1").Select
        'verrouillage du calcul des flux F99, F15 et F50 du compte A44200 sur le BS Y+1
        Sheets("BS_Y+1").Unprotect
        Sheets("BS_Y+1").Range("H115").ClearContents
        Sheets("BS_Y+1").Range("Z115").ClearContents
        Sheets("BS_Y+1").Range("M114").Select
        Selection.AutoFill Destination:=Range("M114:M115"), Type:=xlFillDefault
        Sheets("BS_Y+1").Range("H127").ClearContents
        Sheets("BS_Y+1").Range("Z127").ClearContents
        Sheets("BS_Y+1").Range("M126").Select
        Selection.AutoFill Destination:=Range("M126:M127"), Type:=xlFillDefault
        Sheets("BS_Y+1").Range("H260").ClearContents
         Sheets("BS_Y+1").Range("Z260").ClearContents
        Sheets("BS_Y+1").Range("M259").Select
         Selection.AutoFill Destination:=Range("M259:M260"), Type:=xlFillDefault
 
         'verrouillage du calcul des flux F99, F15 et F50 du compte A46710
 
         Sheets("BS_Y+1").Select
 
         Sheets("BS_Y+1").Unprotect
         Sheets("BS_Y+1").Range("H126").ClearContents
         Sheets("BS_Y+1").Range("Z126").ClearContents
         Range("M126").Select
         Selection.AutoFill Destination:=Range("M125:M126"), Type:=xlFillDefault
         'verrouillage du calcul des flux F99, F15 et F50 du compte P44200 sur le BS Y+1
         Range("H260").ClearContents
         Range("Z260").ClearContents
         Sheets("BS_Y+1").Range("M259").Select
         Selection.AutoFill Destination:=Range("M259:M260"), Type:=xlFillDefault
         '3
          Sheets("BS_Y+2").Select
          'verrouillage du calcul des flux F99, F15 et F50 du compte A44200 sur le BS Y+1
          Sheets("BS_Y+2").Unprotect
          Sheets("BS_Y+2").Range("H115").ClearContents
          Sheets("BS_Y+2").Range("Z115").ClearContents
          Sheets("BS_Y+2").Range("M115").Select
          Selection.AutoFill Destination:=Range("M114:M115"), Type:=xlFillDefault
 
         'verrouillage des flux F99, F15 et F50 du compte A46710
 
         Sheets("BS_Y+2").Range("H127").ClearContents
         Sheets("BS_Y+2").Range("Z127").ClearContents
         Sheets("BS_Y+2").Range("M126").Select
         Selection.AutoFill Destination:=Range("M126:M127"), Type:=xlFillDefault
 
         'verrouillage du calcul des flux F99, F15 et F50 du compte P44200 sur le BS Y+1      
         Sheets("BS_Y+2").Range("H260").ClearContents
         Sheets("BS_Y+2").Range("Z260").ClearContents
         Sheets("BS_Y+2").Range("M259").Select
         Selection.AutoFill Destination:=Range("M259:M260"), Type:=xlFillDefault
         Sheets("BS_Y+2").Protect    
        'même traitement de la feuille y+1 à y+5
 
        Sheets("BS_01").Select
          'verrouillage du calcul des flux F99, F15 et F50 du compte A44200 sur le BS Y+1
          Sheets("BS_01").Unprotect
         Sheets("BS_01").Range("H115").ClearContents
          Sheets("BS_01").Range("Y115").ClearContents
         Range("M114").Select
         Selection.AutoFill Destination:=Range("M114:M115"), Type:=xlFillDefault
 
         'verrouillage des flux F99, F15 et F50 du compte A46710
 
         Sheets("BS_01").Range("H126").ClearContents
        Sheets("BS_01").Range("Y126").ClearContents
        Range("M125").Select
       Selection.AutoFill Destination:=Range("M125:M126"), Type:=xlFillDefault
 
         'verrouillage du calcul des flux F99, F15 et F50 du compte P44200 sur le BS Y+1
 
        Sheets("BS_01").Range("H259").ClearContents
        Sheets("BS_01").Range("Y259").ClearContents
         Range("M258").Select
         Selection.AutoFill Destination:=Range("M258:M259"), Type:=xlFillDefault
         Sheets("BS_02").Select
          'verrouillage du calcul des flux F99, F15 et F50 du compte A44200 sur le BS Y+1
          Sheets("BS_02").Unprotect
 
         Sheets("BS_02").Range("H115").ClearContents
         Sheets("BS_02").Range("Y115").ClearContents
         Range("M114").Select
         Selection.AutoFill Destination:=Range("M114:M115"), Type:=xlFillDefault
 
         'verrouillage des flux F99, F15 et F50 du compte A46710
 
         Sheets("BS_02").Range("H126").ClearContents
         Sheets("BS_02").Range("Y126").ClearContents
         Range("M125").Select
          Selection.AutoFill Destination:=Range("M125:M126"), Type:=xlFillDefault
 
         'verrouillage du calcul des flux F99, F15 et F50 du compte P44200 sur le BS Y+1
 
         Sheets("BS_02").Range("H259").ClearContents
         Sheets("BS_02").Range("Y259").ClearContents
         Range("M258").Select
         Selection.AutoFill Destination:=Range("M258:M259"), Type:=xlFillDefault
 
          Sheets("BS_03").Select
          'verrouillage du calcul des flux F99, F15 et F50 du compte A44200 sur le BS Y+1
          Sheets("BS_03").Unprotect
 
         Sheets("BS_03").Range("H115").ClearContents
          Sheets("BS_03").Range("Y115").ClearContents
         Range("M114").Select
         Selection.AutoFill Destination:=Range("M114:M115"), Type:=xlFillDefault
 
         'verrouillage des flux F99, F15 et F50 du compte A46710
 
         Sheets("BS_03").Range("H126").ClearContents
         Sheets("BS_03").Range("Y126").ClearContents
         Range("M125").Select
        Selection.AutoFill Destination:=Range("M125:M126"), Type:=xlFillDefault
 
         'verrouillage du calcul des flux F99, F15 et F50 du compte P44200 sur le BS Y+1
 
         Sheets("BS_03").Range("H259").ClearContents
         Sheets("BS_03").Range("Y259").ClearContents
         Range("M258").Select
         Selection.AutoFill Destination:=Range("M258:M259"), Type:=xlFillDefault
 
         'meme traitement de de la feuille BS_01 a la feuille BS_12
        Exit Sub 'pas besoin de continuer à boucler, on sort de la macro
  End If
Next i 
End Sub

**Ombak** · 28/07/2015, 13h18

Hey,

si tu actualises énormément tes feuilles, la solution de screen updating est bonne en effet, pourquoi ne marche t'elle pas ?

Code :

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

Application.ScreenUpdating = False

au début

et

Code :

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

Application.ScreenUpdating = True

à la fin (même s'il est facultatif je crois

Sinon quand tu utiles souvent tes feuilles, tu peux raccourcir ton code de la façon suivante:

Code :

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

1
2
3
With sheets("BS")
 .range(..)....
 .range(..)....

mais ça ne raccourcira sûrement pas ton temps d'exécution

Dernière chose, si tes feuilles sont toutes à la suite. Tu peux utiliser une boucle de la forme suivante:

Code :

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

1
2
3
4
5
6
7
8
 
dim sht as worksheet
for j = 1 to sheets.count 'tu peux remplacer par des vrais chiffres, ici cela suppose que ton classeur ne contienne aucune autre feuille
 
'puis poser quelque chose comme
set sht = sheets(j)
 
'et ensuite effectuer ton code avec cette feuille-ci sans avoir besoin de le ré-écrire

**Daniel117** · 03/08/2015, 18h20

ok c'est bon.

simplification d'une boucle [XL-2010]

Macros et VBA Excel

Vue hybride

Discussions similaires

Partager

Partager