Case is a la place de ElseIf

**Tchapeto** · 08/07/2015, 12h09

Bonjour a tous,

Mon code etant beaucoup trop grand, puisque lorsque je souhaite tester ma macro le message d'erreur me disant que ma procedure est trop grande apparait, j'ai donc decide de mettre des parties de mon code dans d'autres procedures et de les appele dans la procedure principale. Vous vous dites que ca devrait facilement marcher mais le hic c'est qu'une de mes sous-procedure est quand meme trop grande.

Elle est compose de plein de ElseIf et donc je me suis pose la question si utilise Case Is pourrait me raccourcir mon code. Pourriez-vous me dire si telle est le cas comment changer mon code en mettant Case is ?

Je vais vous expliquer que fait cette partie du code le plus clairement possible mais je ne garantie rien et dites moi si je n'ai pas ete assez clair.

On peut voir qu'apres la declarations de mes variables, j'appel ma procedure First_Tab. Cette procedure va tout simplement mettre un 0 ou 1 dans le tableau Month_Copper et un nombre entre 0 et 10 dans le tableau Number_Month_Copper.

Donc dans cette partie si le mois est present (donc un 1 dans mon tableau Month_Copper dans la case correspondant au mois) et si on est au premier tour de ma boucle pour le mois de janvier ou le deuxieme tour pour le mois de fevrier : Instruction.

Cette instruction est decompose en deux parties. Premiere partie, j'ecris le "MOIS" dans la case correspondant a Cel1 et donc si j'ecris dans Cel1, Cel1 doit prendre une nouvelle valeur pour ecrire les prochains mois : la case qui est juste en dessous de celle d'avant. Deuxieme partie : si le nombre de fois ou apparait le mois est 1, si le nombre de fois ou apparait le mois est 2, etc... Jusqu'a 10. Si c'est le cas l'instruction est que je fais la somme (en colonne) en partant de la valeur de Cel4 jusqu'a jusqu'au nombre de fois ou apparait le mois moins 1. Exemple : Si je suis dans le cas ou le mois apparait 3 fois, je fais la somme de (Cel4=K2) (K2: (K2+3-1)) = (K2:K4). Et a la fin du If le nombre de fois ou apparait le mois est fini, j'augmente la valeur de Cel4 du nombre de fois ou apparait le mois. Exemple si le mois apparait 3 fois Cel4 passera de K2 a K5. C'est pareil pour Cel5, Cel6 et Cel7 je fais la somme de cellule sur les colonnes correspondant a Cel5, Cel6, Cel7.

En esperant avoir ete clair.

Merci d'avance pour votre aide.

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
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
Sub Copper_Part()
 
Dim Cel1 As Range
Dim Cel2 As Range
Dim Cel3 As Range
Dim Cel4 As Range
Dim Cel5 As Range
Dim Cel6 As Range
Dim Cel7 As Range
 
Dim Plage As Range
Dim Cel As Range
Dim mydate As Date
Dim Month_Copper(11) As Integer
Dim Month_Brent(11) As Integer
Dim Month_Crude(11) As Integer
Dim Number_Month_Copper(11) As Integer
Dim Number_Month_Brent(11) As Integer
Dim Number_Month_Crude(11) As Integer
 
 
Set Cel1 = Sheet3.Range("J20")
Set Cel2 = Sheet3.Range("J27")
Set Cel3 = Sheet3.Range("J34")
Set Cel4 = Sheet3.Range("K2")
Set Cel4 = Sheet3.Range("L2")
Set Cel4 = Sheet3.Range("M2")
Set Cel4 = Sheet3.Range("N2")
Set Plage = Sheet2.Range("B2:B30")
 
 
Month_Copper(0) = 0
Month_Copper(1) = 0
Month_Copper(2) = 0
Month_Copper(3) = 0
Month_Copper(4) = 0
Month_Copper(5) = 0
Month_Copper(6) = 0
Month_Copper(7) = 0
Month_Copper(8) = 0
Month_Copper(9) = 0
Month_Copper(10) = 0
Month_Copper(11) = 0
 
Month_Brent(0) = 0
Month_Brent(1) = 0
Month_Brent(2) = 0
Month_Brent(3) = 0
Month_Brent(4) = 0
Month_Brent(5) = 0
Month_Brent(6) = 0
Month_Brent(7) = 0
Month_Brent(8) = 0
Month_Brent(9) = 0
Month_Brent(10) = 0
Month_Brent(11) = 0
 
Month_Crude(0) = 0
Month_Crude(1) = 0
Month_Crude(2) = 0
Month_Crude(3) = 0
Month_Crude(4) = 0
Month_Crude(5) = 0
Month_Crude(6) = 0
Month_Crude(7) = 0
Month_Crude(8) = 0
Month_Crude(9) = 0
Month_Crude(10) = 0
Month_Crude(11) = 0
 
Number_Month_Copper(0) = 0
Number_Month_Copper(1) = 0
Number_Month_Copper(2) = 0
Number_Month_Copper(3) = 0
Number_Month_Copper(4) = 0
Number_Month_Copper(5) = 0
Number_Month_Copper(6) = 0
Number_Month_Copper(7) = 0
Number_Month_Copper(8) = 0
Number_Month_Copper(9) = 0
Number_Month_Copper(10) = 0
Number_Month_Copper(11) = 0
 
Number_Month_Brent(0) = 0
Number_Month_Brent(1) = 0
Number_Month_Brent(2) = 0
Number_Month_Brent(3) = 0
Number_Month_Brent(4) = 0
Number_Month_Brent(5) = 0
Number_Month_Brent(6) = 0
Number_Month_Brent(7) = 0
Number_Month_Brent(8) = 0
Number_Month_Brent(9) = 0
Number_Month_Brent(10) = 0
Number_Month_Brent(11) = 0
 
Number_Month_Crude(0) = 0
Number_Month_Crude(1) = 0
Number_Month_Crude(2) = 0
Number_Month_Crude(3) = 0
Number_Month_Crude(4) = 0
Number_Month_Crude(5) = 0
Number_Month_Crude(6) = 0
Number_Month_Crude(7) = 0
Number_Month_Crude(8) = 0
Number_Month_Crude(9) = 0
Number_Month_Crude(10) = 0
Number_Month_Crude(11) = 0
 
Call Module4.First_Tab
 
For n = 0 To 11
 
    If Month_Copper(n) = 1 And n = 0 Then
 
        Cel1.Value = "January"
 
            If Number_Month_Copper(0) = 1 Then
 
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel4, Cel4.Offset(Number_Month_Copper(0) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel5, Cel5.Offset(Number_Month_Copper(0) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel6, Cel6.Offset(Number_Month_Copper(0) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel7, Cel7.Offset(Number_Month_Copper(0) - 1, 0)))
 
            ElseIf Number_Month_Copper(0) = 2 Then
 
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel4, Cel4.Offset(Number_Month_Copper(0) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel5, Cel5.Offset(Number_Month_Copper(0) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel6, Cel6.Offset(Number_Month_Copper(0) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel7, Cel7.Offset(Number_Month_Copper(0) - 1, 0)))
 
            ElseIf Number_Month_Copper(0) = 3 Then
 
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel4, Cel4.Offset(Number_Month_Copper(0) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel5, Cel5.Offset(Number_Month_Copper(0) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel6, Cel6.Offset(Number_Month_Copper(0) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel7, Cel7.Offset(Number_Month_Copper(0) - 1, 0)))
 
            ElseIf Number_Month_Copper(0) = 4 Then
 
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel4, Cel4.Offset(Number_Month_Copper(0) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel5, Cel5.Offset(Number_Month_Copper(0) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel6, Cel6.Offset(Number_Month_Copper(0) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel7, Cel7.Offset(Number_Month_Copper(0) - 1, 0)))
 
            ElseIf Number_Month_Copper(0) = 5 Then
 
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel4, Cel4.Offset(Number_Month_Copper(0) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel5, Cel5.Offset(Number_Month_Copper(0) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel6, Cel6.Offset(Number_Month_Copper(0) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel7, Cel7.Offset(Number_Month_Copper(0) - 1, 0)))
 
            ElseIf Number_Month_Copper(0) = 6 Then
 
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel4, Cel4.Offset(Number_Month_Copper(0) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel5, Cel5.Offset(Number_Month_Copper(0) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel6, Cel6.Offset(Number_Month_Copper(0) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel7, Cel7.Offset(Number_Month_Copper(0) - 1, 0)))
 
            ElseIf Number_Month_Copper(0) = 7 Then
 
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel4, Cel4.Offset(Number_Month_Copper(0) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel5, Cel5.Offset(Number_Month_Copper(0) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel6, Cel6.Offset(Number_Month_Copper(0) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel7, Cel7.Offset(Number_Month_Copper(0) - 1, 0)))
 
            ElseIf Number_Month_Copper(0) = 8 Then
 
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel4, Cel4.Offset(Number_Month_Copper(0) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel5, Cel5.Offset(Number_Month_Copper(0) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel6, Cel6.Offset(Number_Month_Copper(0) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel7, Cel7.Offset(Number_Month_Copper(0) - 1, 0)))
 
            ElseIf Number_Month_Copper(0) = 9 Then
 
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel4, Cel4.Offset(Number_Month_Copper(0) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel5, Cel5.Offset(Number_Month_Copper(0) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel6, Cel6.Offset(Number_Month_Copper(0) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel7, Cel7.Offset(Number_Month_Copper(0) - 1, 0)))
 
            ElseIf Number_Month_Copper(0) = 10 Then
 
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel4, Cel4.Offset(Number_Month_Copper(0) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel5, Cel5.Offset(Number_Month_Copper(0) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel6, Cel6.Offset(Number_Month_Copper(0) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel7, Cel7.Offset(Number_Month_Copper(0) - 1, 0)))
 
            End If
 
            Set Cel1 = Cel1.Offset(1, 0)
 
            Set Cel4 = Cel4.Offset(Number_Month_Copper(0), 0)
            Set Cel5 = Cel5.Offset(Number_Month_Copper(0), 0)
            Set Cel6 = Cel6.Offset(Number_Month_Copper(0), 0)
            Set Cel7 = Cel7.Offset(Number_Month_Copper(0), 0)
 
 
    ElseIf Month_Copper(n) = 1 And n = 1 Then
 
        Cel1.Value = "February"
 
            If Number_Month_Copper(1) = 1 Then
 
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel4, Cel4.Offset(Number_Month_Copper(1) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel5, Cel5.Offset(Number_Month_Copper(1) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel6, Cel6.Offset(Number_Month_Copper(1) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel7, Cel7.Offset(Number_Month_Copper(1) - 1, 0)))
 
            ElseIf Number_Month_Copper(1) = 2 Then
 
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel4, Cel4.Offset(Number_Month_Copper(1) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel5, Cel5.Offset(Number_Month_Copper(1) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel6, Cel6.Offset(Number_Month_Copper(1) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel7, Cel7.Offset(Number_Month_Copper(1) - 1, 0)))
 
            ElseIf Number_Month_Copper(1) = 3 Then
 
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel4, Cel4.Offset(Number_Month_Copper(1) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel5, Cel5.Offset(Number_Month_Copper(1) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel6, Cel6.Offset(Number_Month_Copper(1) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel7, Cel7.Offset(Number_Month_Copper(1) - 1, 0)))
 
            ElseIf Number_Month_Copper(1) = 4 Then
 
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel4, Cel4.Offset(Number_Month_Copper(1) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel5, Cel5.Offset(Number_Month_Copper(1) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel6, Cel6.Offset(Number_Month_Copper(1) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel7, Cel7.Offset(Number_Month_Copper(1) - 1, 0)))
 
            ElseIf Number_Month_Copper(1) = 5 Then
 
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel4, Cel4.Offset(Number_Month_Copper(1) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel5, Cel5.Offset(Number_Month_Copper(1) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel6, Cel6.Offset(Number_Month_Copper(1) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel7, Cel7.Offset(Number_Month_Copper(1) - 1, 0)))
 
            ElseIf Number_Month_Copper(1) = 6 Then
 
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel4, Cel4.Offset(Number_Month_Copper(1) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel5, Cel5.Offset(Number_Month_Copper(1) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel6, Cel6.Offset(Number_Month_Copper(1) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel7, Cel7.Offset(Number_Month_Copper(1) - 1, 0)))
 
            ElseIf Number_Month_Copper(1) = 7 Then
 
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel4, Cel4.Offset(Number_Month_Copper(1) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel5, Cel5.Offset(Number_Month_Copper(1) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel6, Cel6.Offset(Number_Month_Copper(1) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel7, Cel7.Offset(Number_Month_Copper(1) - 1, 0)))
 
            ElseIf Number_Month_Copper(1) = 8 Then
 
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel4, Cel4.Offset(Number_Month_Copper(1) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel5, Cel5.Offset(Number_Month_Copper(1) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel6, Cel6.Offset(Number_Month_Copper(1) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel7, Cel7.Offset(Number_Month_Copper(1) - 1, 0)))
 
            ElseIf Number_Month_Copper(1) = 9 Then
 
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel4, Cel4.Offset(Number_Month_Copper(1) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel5, Cel5.Offset(Number_Month_Copper(1) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel6, Cel6.Offset(Number_Month_Copper(1) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel7, Cel7.Offset(Number_Month_Copper(1) - 1, 0)))
 
            ElseIf Number_Month_Copper(1) = 10 Then
 
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel4, Cel4.Offset(Number_Month_Copper(1) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel5, Cel5.Offset(Number_Month_Copper(1) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel6, Cel6.Offset(Number_Month_Copper(1) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel7, Cel7.Offset(Number_Month_Copper(1) - 1, 0)))
 
            End If
 
            Set Cel1 = Cel1.Offset(1, 0)
 
            Set Cel4 = Cel4.Offset(Number_Month_Copper(1), 0)
            Set Cel5 = Cel5.Offset(Number_Month_Copper(1), 0)
            Set Cel6 = Cel6.Offset(Number_Month_Copper(1), 0)
            Set Cel7 = Cel7.Offset(Number_Month_Copper(1), 0)
 
 
    ElseIf Month_Copper(n) = 1 And n = 2 Then
 
        Cel1.Value = "March"
 
            If Number_Month_Copper(2) = 1 Then
 
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel4, Cel4.Offset(Number_Month_Copper(2) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel5, Cel5.Offset(Number_Month_Copper(2) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel6, Cel6.Offset(Number_Month_Copper(2) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel7, Cel7.Offset(Number_Month_Copper(2) - 1, 0)))
 
            ElseIf Number_Month_Copper(2) = 2 Then
 
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel4, Cel4.Offset(Number_Month_Copper(2) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel5, Cel5.Offset(Number_Month_Copper(2) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel6, Cel6.Offset(Number_Month_Copper(2) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel7, Cel7.Offset(Number_Month_Copper(2) - 1, 0)))
 
            ElseIf Number_Month_Copper(2) = 3 Then
 
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel4, Cel4.Offset(Number_Month_Copper(2) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel5, Cel5.Offset(Number_Month_Copper(2) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel6, Cel6.Offset(Number_Month_Copper(2) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel7, Cel7.Offset(Number_Month_Copper(2) - 1, 0)))
 
            ElseIf Number_Month_Copper(2) = 4 Then
 
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel4, Cel4.Offset(Number_Month_Copper(2) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel5, Cel5.Offset(Number_Month_Copper(2) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel6, Cel6.Offset(Number_Month_Copper(2) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel7, Cel7.Offset(Number_Month_Copper(2) - 1, 0)))
 
            ElseIf Number_Month_Copper(2) = 5 Then
 
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel4, Cel4.Offset(Number_Month_Copper(2) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel5, Cel5.Offset(Number_Month_Copper(2) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel6, Cel6.Offset(Number_Month_Copper(2) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel7, Cel7.Offset(Number_Month_Copper(2) - 1, 0)))
 
            ElseIf Number_Month_Copper(2) = 6 Then
 
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel4, Cel4.Offset(Number_Month_Copper(2) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel5, Cel5.Offset(Number_Month_Copper(2) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel6, Cel6.Offset(Number_Month_Copper(2) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel7, Cel7.Offset(Number_Month_Copper(2) - 1, 0)))
 
            ElseIf Number_Month_Copper(2) = 7 Then
 
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel4, Cel4.Offset(Number_Month_Copper(2) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel5, Cel5.Offset(Number_Month_Copper(2) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel6, Cel6.Offset(Number_Month_Copper(2) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel7, Cel7.Offset(Number_Month_Copper(2) - 1, 0)))
 
            ElseIf Number_Month_Copper(2) = 8 Then
 
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel4, Cel4.Offset(Number_Month_Copper(2) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel5, Cel5.Offset(Number_Month_Copper(2) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel6, Cel6.Offset(Number_Month_Copper(2) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel7, Cel7.Offset(Number_Month_Copper(2) - 1, 0)))
 
            ElseIf Number_Month_Copper(2) = 9 Then
 
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel4, Cel4.Offset(Number_Month_Copper(2) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel5, Cel5.Offset(Number_Month_Copper(2) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel6, Cel6.Offset(Number_Month_Copper(2) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel7, Cel7.Offset(Number_Month_Copper(2) - 1, 0)))
 
            ElseIf Number_Month_Copper(2) = 10 Then
 
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel4, Cel4.Offset(Number_Month_Copper(2) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel5, Cel5.Offset(Number_Month_Copper(2) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel6, Cel6.Offset(Number_Month_Copper(2) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel7, Cel7.Offset(Number_Month_Copper(2) - 1, 0)))
 
            End If
 
            Set Cel1 = Cel1.Offset(1, 0)
 
            Set Cel4 = Cel4.Offset(Number_Month_Copper(2), 0)
            Set Cel5 = Cel5.Offset(Number_Month_Copper(2), 0)
            Set Cel6 = Cel6.Offset(Number_Month_Copper(2), 0)
            Set Cel6 = Cel6.Offset(Number_Month_Copper(2), 0)
 
 
    ElseIf Month_Copper(n) = 1 And n = 3 Then
 
        Cel1.Value = "April"
 
           If Number_Month_Copper(3) = 1 Then
 
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel4, Cel4.Offset(Number_Month_Copper(3) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel5, Cel5.Offset(Number_Month_Copper(3) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel6, Cel6.Offset(Number_Month_Copper(3) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel7, Cel7.Offset(Number_Month_Copper(3) - 1, 0)))
 
            ElseIf Number_Month_Copper(3) = 2 Then
 
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel4, Cel4.Offset(Number_Month_Copper(3) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel5, Cel5.Offset(Number_Month_Copper(3) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel6, Cel6.Offset(Number_Month_Copper(3) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel7, Cel7.Offset(Number_Month_Copper(3) - 1, 0)))
 
            ElseIf Number_Month_Copper(3) = 3 Then
 
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel4, Cel4.Offset(Number_Month_Copper(3) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel5, Cel5.Offset(Number_Month_Copper(3) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel6, Cel6.Offset(Number_Month_Copper(3) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel7, Cel7.Offset(Number_Month_Copper(3) - 1, 0)))
 
            ElseIf Number_Month_Copper(3) = 4 Then
 
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel4, Cel4.Offset(Number_Month_Copper(3) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel5, Cel5.Offset(Number_Month_Copper(3) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel6, Cel6.Offset(Number_Month_Copper(3) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel7, Cel7.Offset(Number_Month_Copper(3) - 1, 0)))
 
            ElseIf Number_Month_Copper(3) = 5 Then
 
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel4, Cel4.Offset(Number_Month_Copper(3) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel5, Cel5.Offset(Number_Month_Copper(3) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel6, Cel6.Offset(Number_Month_Copper(3) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel7, Cel7.Offset(Number_Month_Copper(3) - 1, 0)))
 
            ElseIf Number_Month_Copper(3) = 6 Then
 
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel4, Cel4.Offset(Number_Month_Copper(3) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel5, Cel5.Offset(Number_Month_Copper(3) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel6, Cel6.Offset(Number_Month_Copper(3) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel7, Cel7.Offset(Number_Month_Copper(3) - 1, 0)))
 
            ElseIf Number_Month_Copper(3) = 7 Then
 
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel4, Cel4.Offset(Number_Month_Copper(3) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel5, Cel5.Offset(Number_Month_Copper(3) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel6, Cel6.Offset(Number_Month_Copper(3) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel7, Cel7.Offset(Number_Month_Copper(3) - 1, 0)))
 
            ElseIf Number_Month_Copper(3) = 8 Then
 
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel4, Cel4.Offset(Number_Month_Copper(3) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel5, Cel5.Offset(Number_Month_Copper(3) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel6, Cel6.Offset(Number_Month_Copper(3) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel7, Cel7.Offset(Number_Month_Copper(3) - 1, 0)))
 
            ElseIf Number_Month_Copper(3) = 9 Then
 
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel4, Cel4.Offset(Number_Month_Copper(3) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel5, Cel5.Offset(Number_Month_Copper(3) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel6, Cel6.Offset(Number_Month_Copper(3) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel7, Cel7.Offset(Number_Month_Copper(3) - 1, 0)))
 
            ElseIf Number_Month_Copper(3) = 10 Then
 
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel4, Cel4.Offset(Number_Month_Copper(3) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel5, Cel5.Offset(Number_Month_Copper(3) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel6, Cel6.Offset(Number_Month_Copper(3) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel7, Cel7.Offset(Number_Month_Copper(3) - 1, 0)))
 
            End If
 
            Set Cel1 = Cel1.Offset(1, 0)
 
            Set Cel4 = Cel4.Offset(Number_Month_Copper(3), 0)
            Set Cel5 = Cel5.Offset(Number_Month_Copper(3), 0)
            Set Cel6 = Cel6.Offset(Number_Month_Copper(3), 0)
            Set Cel7 = Cel7.Offset(Number_Month_Copper(3), 0)
 
 
    ElseIf Month_Copper(n) = 1 And n = 4 Then
 
        Cel1.Value = "May"
 
            If Number_Month_Copper(4) = 1 Then
 
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel4, Cel4.Offset(Number_Month_Copper(4) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel5, Cel5.Offset(Number_Month_Copper(4) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel6, Cel6.Offset(Number_Month_Copper(4) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel7, Cel7.Offset(Number_Month_Copper(4) - 1, 0)))
 
            ElseIf Number_Month_Copper(4) = 2 Then
 
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel4, Cel4.Offset(Number_Month_Copper(4) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel5, Cel5.Offset(Number_Month_Copper(4) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel6, Cel6.Offset(Number_Month_Copper(4) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel7, Cel7.Offset(Number_Month_Copper(4) - 1, 0)))
 
            ElseIf Number_Month_Copper(4) = 3 Then
 
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel4, Cel4.Offset(Number_Month_Copper(4) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel5, Cel5.Offset(Number_Month_Copper(4) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel6, Cel6.Offset(Number_Month_Copper(4) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel7, Cel7.Offset(Number_Month_Copper(4) - 1, 0)))
 
            ElseIf Number_Month_Copper(4) = 4 Then
 
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel4, Cel4.Offset(Number_Month_Copper(4) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel5, Cel5.Offset(Number_Month_Copper(4) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel6, Cel6.Offset(Number_Month_Copper(4) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel7, Cel7.Offset(Number_Month_Copper(4) - 1, 0)))
 
            ElseIf Number_Month_Copper(4) = 5 Then
 
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel4, Cel4.Offset(Number_Month_Copper(4) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel5, Cel5.Offset(Number_Month_Copper(4) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel6, Cel6.Offset(Number_Month_Copper(4) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel7, Cel7.Offset(Number_Month_Copper(4) - 1, 0)))
 
            ElseIf Number_Month_Copper(4) = 6 Then
 
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel4, Cel4.Offset(Number_Month_Copper(4) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel5, Cel5.Offset(Number_Month_Copper(4) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel6, Cel6.Offset(Number_Month_Copper(4) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel7, Cel7.Offset(Number_Month_Copper(4) - 1, 0)))
 
            ElseIf Number_Month_Copper(4) = 7 Then
 
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel4, Cel4.Offset(Number_Month_Copper(4) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel5, Cel5.Offset(Number_Month_Copper(4) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel6, Cel6.Offset(Number_Month_Copper(4) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel7, Cel7.Offset(Number_Month_Copper(4) - 1, 0)))
 
            ElseIf Number_Month_Copper(4) = 8 Then
 
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel4, Cel4.Offset(Number_Month_Copper(4) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel5, Cel5.Offset(Number_Month_Copper(4) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel6, Cel6.Offset(Number_Month_Copper(4) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel7, Cel7.Offset(Number_Month_Copper(4) - 1, 0)))
 
            ElseIf Number_Month_Copper(4) = 9 Then
 
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel4, Cel4.Offset(Number_Month_Copper(4) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel5, Cel5.Offset(Number_Month_Copper(4) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel6, Cel6.Offset(Number_Month_Copper(4) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel7, Cel7.Offset(Number_Month_Copper(4) - 1, 0)))
 
            ElseIf Number_Month_Copper(4) = 10 Then
 
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel4, Cel4.Offset(Number_Month_Copper(4) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel5, Cel5.Offset(Number_Month_Copper(4) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel6, Cel6.Offset(Number_Month_Copper(4) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel7, Cel7.Offset(Number_Month_Copper(4) - 1, 0)))
 
            End If
 
            Set Cel1 = Cel1.Offset(1, 0)
 
            Set Cel4 = Cel4.Offset(Number_Month_Copper(4), 0)
            Set Cel5 = Cel5.Offset(Number_Month_Copper(4), 0)
            Set Cel6 = Cel6.Offset(Number_Month_Copper(4), 0)
            Set Cel7 = Cel7.Offset(Number_Month_Copper(4), 0)
 
 
    ElseIf Month_Copper(n) = 1 And n = 5 Then
 
        Cel1.Value = "June"
 
           If Number_Month_Copper(5) = 1 Then
 
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel4, Cel4.Offset(Number_Month_Copper(5) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel5, Cel5.Offset(Number_Month_Copper(5) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel6, Cel6.Offset(Number_Month_Copper(5) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel7, Cel7.Offset(Number_Month_Copper(5) - 1, 0)))
 
            ElseIf Number_Month_Copper(5) = 2 Then
 
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel4, Cel4.Offset(Number_Month_Copper(5) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel5, Cel5.Offset(Number_Month_Copper(5) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel6, Cel6.Offset(Number_Month_Copper(5) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel7, Cel7.Offset(Number_Month_Copper(5) - 1, 0)))
 
            ElseIf Number_Month_Copper(5) = 3 Then
 
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel4, Cel4.Offset(Number_Month_Copper(5) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel5, Cel5.Offset(Number_Month_Copper(5) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel6, Cel6.Offset(Number_Month_Copper(5) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel7, Cel7.Offset(Number_Month_Copper(5) - 1, 0)))
 
            ElseIf Number_Month_Copper(5) = 4 Then
 
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel4, Cel4.Offset(Number_Month_Copper(5) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel5, Cel5.Offset(Number_Month_Copper(5) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel6, Cel6.Offset(Number_Month_Copper(5) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel7, Cel7.Offset(Number_Month_Copper(5) - 1, 0)))
 
            ElseIf Number_Month_Copper(5) = 5 Then
 
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel4, Cel4.Offset(Number_Month_Copper(5) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel5, Cel5.Offset(Number_Month_Copper(5) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel6, Cel6.Offset(Number_Month_Copper(5) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel7, Cel7.Offset(Number_Month_Copper(5) - 1, 0)))
 
            ElseIf Number_Month_Copper(5) = 6 Then
 
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel4, Cel4.Offset(Number_Month_Copper(5) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel5, Cel5.Offset(Number_Month_Copper(5) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel6, Cel6.Offset(Number_Month_Copper(5) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel7, Cel7.Offset(Number_Month_Copper(5) - 1, 0)))
 
            ElseIf Number_Month_Copper(5) = 7 Then
 
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel4, Cel4.Offset(Number_Month_Copper(5) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel5, Cel5.Offset(Number_Month_Copper(5) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel6, Cel6.Offset(Number_Month_Copper(5) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel7, Cel7.Offset(Number_Month_Copper(5) - 1, 0)))
 
            ElseIf Number_Month_Copper(5) = 8 Then
 
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel4, Cel4.Offset(Number_Month_Copper(5) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel5, Cel5.Offset(Number_Month_Copper(5) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel6, Cel6.Offset(Number_Month_Copper(5) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel7, Cel7.Offset(Number_Month_Copper(5) - 1, 0)))
 
            ElseIf Number_Month_Copper(5) = 9 Then
 
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel4, Cel4.Offset(Number_Month_Copper(5) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel5, Cel5.Offset(Number_Month_Copper(5) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel6, Cel6.Offset(Number_Month_Copper(5) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel7, Cel7.Offset(Number_Month_Copper(5) - 1, 0)))
 
            ElseIf Number_Month_Copper(5) = 10 Then
 
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel4, Cel4.Offset(Number_Month_Copper(5) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel5, Cel5.Offset(Number_Month_Copper(5) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel6, Cel6.Offset(Number_Month_Copper(5) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel7, Cel7.Offset(Number_Month_Copper(5) - 1, 0)))
 
            End If
 
            Set Cel1 = Cel1.Offset(1, 0)
 
            Set Cel4 = Cel4.Offset(Number_Month_Copper(5), 0)
            Set Cel5 = Cel5.Offset(Number_Month_Copper(5), 0)
            Set Cel6 = Cel6.Offset(Number_Month_Copper(5), 0)
            Set Cel7 = Cel7.Offset(Number_Month_Copper(5), 0)
 
    ...
 
 
    ElseIf Month_Copper(n) = 1 And n = 10 Then
 
        Cel1.Value = "November"
 
            If Number_Month_Copper(10) = 1 Then
 
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel4, Cel4.Offset(Number_Month_Copper(10) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel5, Cel5.Offset(Number_Month_Copper(10) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel6, Cel6.Offset(Number_Month_Copper(10) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel7, Cel7.Offset(Number_Month_Copper(10) - 1, 0)))
 
            ElseIf Number_Month_Copper(10) = 2 Then
 
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel4, Cel4.Offset(Number_Month_Copper(10) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel5, Cel5.Offset(Number_Month_Copper(10) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel6, Cel6.Offset(Number_Month_Copper(10) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel7, Cel7.Offset(Number_Month_Copper(10) - 1, 0)))
 
            ElseIf Number_Month_Copper(10) = 3 Then
 
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel4, Cel4.Offset(Number_Month_Copper(10) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel5, Cel5.Offset(Number_Month_Copper(10) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel6, Cel6.Offset(Number_Month_Copper(10) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel7, Cel7.Offset(Number_Month_Copper(10) - 1, 0)))
 
            ElseIf Number_Month_Copper(10) = 4 Then
 
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel4, Cel4.Offset(Number_Month_Copper(10) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel5, Cel5.Offset(Number_Month_Copper(10) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel6, Cel6.Offset(Number_Month_Copper(10) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel7, Cel7.Offset(Number_Month_Copper(10) - 1, 0)))
 
            ElseIf Number_Month_Copper(10) = 5 Then
 
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel4, Cel4.Offset(Number_Month_Copper(10) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel5, Cel5.Offset(Number_Month_Copper(10) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel6, Cel6.Offset(Number_Month_Copper(10) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel7, Cel7.Offset(Number_Month_Copper(10) - 1, 0)))
 
            ElseIf Number_Month_Copper(10) = 6 Then
 
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel4, Cel4.Offset(Number_Month_Copper(10) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel5, Cel5.Offset(Number_Month_Copper(10) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel6, Cel6.Offset(Number_Month_Copper(10) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel7, Cel7.Offset(Number_Month_Copper(10) - 1, 0)))
 
            ElseIf Number_Month_Copper(10) = 7 Then
 
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel4, Cel4.Offset(Number_Month_Copper(10) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel5, Cel5.Offset(Number_Month_Copper(10) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel6, Cel6.Offset(Number_Month_Copper(10) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel7, Cel7.Offset(Number_Month_Copper(10) - 1, 0)))
 
            ElseIf Number_Month_Copper(10) = 8 Then
 
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel4, Cel4.Offset(Number_Month_Copper(10) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel5, Cel5.Offset(Number_Month_Copper(10) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel6, Cel6.Offset(Number_Month_Copper(10) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel7, Cel7.Offset(Number_Month_Copper(10) - 1, 0)))
 
            ElseIf Number_Month_Copper(10) = 9 Then
 
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel4, Cel4.Offset(Number_Month_Copper(10) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel5, Cel5.Offset(Number_Month_Copper(10) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel6, Cel6.Offset(Number_Month_Copper(10) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel7, Cel7.Offset(Number_Month_Copper(10) - 1, 0)))
 
            ElseIf Number_Month_Copper(10) = 10 Then
 
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel4, Cel4.Offset(Number_Month_Copper(10) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel5, Cel5.Offset(Number_Month_Copper(10) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel6, Cel6.Offset(Number_Month_Copper(10) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel7, Cel7.Offset(Number_Month_Copper(10) - 1, 0)))
 
            End If
 
            Set Cel1 = Cel1.Offset(1, 0)
 
            Set Cel4 = Cel4.Offset(Number_Month_Copper(10), 0)
            Set Cel5 = Cel5.Offset(Number_Month_Copper(10), 0)
            Set Cel6 = Cel6.Offset(Number_Month_Copper(10), 0)
            Set Cel7 = Cel7.Offset(Number_Month_Copper(10), 0)
 
 
    ElseIf Month_Copper(n) = 1 And n = 11 Then
 
        Cel1.Value = "December"
 
            If Number_Month_Copper(11) = 1 Then
 
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel4, Cel4.Offset(Number_Month_Copper(11) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel5, Cel5.Offset(Number_Month_Copper(11) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel6, Cel6.Offset(Number_Month_Copper(11) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel7, Cel7.Offset(Number_Month_Copper(11) - 1, 0)))
 
            ElseIf Number_Month_Copper(11) = 2 Then
 
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel4, Cel4.Offset(Number_Month_Copper(11) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel5, Cel5.Offset(Number_Month_Copper(11) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel6, Cel6.Offset(Number_Month_Copper(11) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel7, Cel7.Offset(Number_Month_Copper(11) - 1, 0)))
 
            ElseIf Number_Month_Copper(11) = 3 Then
 
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel4, Cel4.Offset(Number_Month_Copper(11) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel5, Cel5.Offset(Number_Month_Copper(11) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel6, Cel6.Offset(Number_Month_Copper(11) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel7, Cel7.Offset(Number_Month_Copper(11) - 1, 0)))
 
            ElseIf Number_Month_Copper(11) = 4 Then
 
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel4, Cel4.Offset(Number_Month_Copper(11) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel5, Cel5.Offset(Number_Month_Copper(11) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel6, Cel6.Offset(Number_Month_Copper(11) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel7, Cel7.Offset(Number_Month_Copper(11) - 1, 0)))
 
            ElseIf Number_Month_Copper(11) = 5 Then
 
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel4, Cel4.Offset(Number_Month_Copper(11) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel5, Cel5.Offset(Number_Month_Copper(11) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel6, Cel6.Offset(Number_Month_Copper(11) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel7, Cel7.Offset(Number_Month_Copper(11) - 1, 0)))
 
            ElseIf Number_Month_Copper(11) = 6 Then
 
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel4, Cel4.Offset(Number_Month_Copper(11) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel5, Cel5.Offset(Number_Month_Copper(11) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel6, Cel6.Offset(Number_Month_Copper(11) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel7, Cel7.Offset(Number_Month_Copper(11) - 1, 0)))
 
            ElseIf Number_Month_Copper(11) = 7 Then
 
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel4, Cel4.Offset(Number_Month_Copper(11) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel5, Cel5.Offset(Number_Month_Copper(11) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel6, Cel6.Offset(Number_Month_Copper(11) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel7, Cel7.Offset(Number_Month_Copper(11) - 1, 0)))
 
            ElseIf Number_Month_Copper(11) = 8 Then
 
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel4, Cel4.Offset(Number_Month_Copper(11) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel5, Cel5.Offset(Number_Month_Copper(11) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel6, Cel6.Offset(Number_Month_Copper(11) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel7, Cel7.Offset(Number_Month_Copper(11) - 1, 0)))
 
            ElseIf Number_Month_Copper(11) = 9 Then
 
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel4, Cel4.Offset(Number_Month_Copper(11) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel5, Cel5.Offset(Number_Month_Copper(11) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel6, Cel6.Offset(Number_Month_Copper(11) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel7, Cel7.Offset(Number_Month_Copper(11) - 1, 0)))
 
            ElseIf Number_Month_Copper(11) = 10 Then
 
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel4, Cel4.Offset(Number_Month_Copper(11) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel5, Cel5.Offset(Number_Month_Copper(11) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel6, Cel6.Offset(Number_Month_Copper(11) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel7, Cel7.Offset(Number_Month_Copper(11) - 1, 0)))
 
            End If
 
            Set Cel1 = Cel1.Offset(1, 0)
 
            Set Cel4 = Cel4.Offset(Number_Month_Copper(11), 0)
            Set Cel5 = Cel5.Offset(Number_Month_Copper(11), 0)
            Set Cel6 = Cel6.Offset(Number_Month_Copper(11), 0)
            Set Cel7 = Cel7.Offset(Number_Month_Copper(11), 0)
 
    End If
 
Next n
 
End Sub

**Daniel.C** · 08/07/2015, 12h22

Bonjour,

Code :

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

1
2
3
4
5
6
            If Number_Month_Copper(0) = 1 Then
 
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel4, Cel4.Offset(Number_Month_Copper(0) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel5, Cel5.Offset(Number_Month_Copper(0) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel6, Cel6.Offset(Number_Month_Copper(0) - 1, 0)))
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel7, Cel7.Offset(Number_Month_Copper(0) - 1, 0)))

Ou je n'ai pas les yeux en face des trous, ou tu mets successivement 4 valeurs différentes dans la même cellule ?

**Tchapeto** · 08/07/2015, 12h36

Je crois que tu n'as pas les yeux en face des trous :p
Cel4, Cel5, Cel6 et Cel7 correspondent a des differentes colonnes.

**Daniel.C** · 08/07/2015, 12h44

Après avoir bu une tasse de café corsé, je confirme que les valeurs sont bien différentes, mais que tu les mets dans la même cellule :

Code :

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

Cel1.Offset(0, 1).Value

un partout, la balle au centre.

**Tchapeto** · 08/07/2015, 12h49

HAHAHA OUUIIIII
Tu as raison !!!!
Je vais changer ca tout de suite !

Mais saurais-tu comment raccourcir mon code ? Avec Case is peut etre ?

**joe.levrai** · 08/07/2015, 12h54

Bonjour,

à vu d'œil, tout ton code est simplifiable en 2 ou trois select case imbriqués ...

peut-on voir la procédure First_Tab également ? Et bien sûr la modification de ton code qui tient compte de la remarque soulevée par Daniel.C

un classeur exemple serait également bienvenu, pour rapidement te proposer une solution sans questionnement superflu.

**Daniel.C** · 08/07/2015, 12h56

Et là, je ne vois pas pourquoi tu testes puisque le résultat est le même ? Erreur de copier / Coller ?

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
            If Number_Month_Copper(0) = 1 Then
 
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel4, Cel4.Offset(Number_Month_Copper(0) - 1, 0)))
 
            ElseIf Number_Month_Copper(0) = 2 Then
 
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel4, Cel4.Offset(Number_Month_Copper(0) - 1, 0)))
 
            ElseIf Number_Month_Copper(0) = 3 Then
 
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel4, Cel4.Offset(Number_Month_Copper(0) - 1, 0)))
 
            ElseIf Number_Month_Copper(0) = 4 Then
 
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel4, Cel4.Offset(Number_Month_Copper(0) - 1, 0)))
 
            ElseIf Number_Month_Copper(0) = 5 Then
 
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel4, Cel4.Offset(Number_Month_Copper(0) - 1, 0)))

**Tchapeto** · 08/07/2015, 13h00

Que veux-tu dire par classeur exemple ?

Voici la procedure First_Tab

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
First_Tab()
 
Dim Cel1 As Range
Dim Cel2 As Range
Dim Cel3 As Range
Dim Cel4 As Range
Dim Plage As Range
Dim Cel As Range
Dim mydate As Date
Dim Month_Copper(11) As Integer
Dim Month_Brent(11) As Integer
Dim Month_Crude(11) As Integer
Dim Number_Month_Copper(11) As Integer
Dim Number_Month_Brent(11) As Integer
Dim Number_Month_Crude(11) As Integer
 
 
Set Cel1 = Sheet3.Range("J20")
Set Cel2 = Sheet3.Range("J27")
Set Cel3 = Sheet3.Range("J34")
Set Cel4 = Sheet3.Range("K2")
Set Plage = Sheet2.Range("B2:B30")
 
 
Month_Copper(0) = 0
Month_Copper(1) = 0
Month_Copper(2) = 0
Month_Copper(3) = 0
Month_Copper(4) = 0
Month_Copper(5) = 0
Month_Copper(6) = 0
Month_Copper(7) = 0
Month_Copper(8) = 0
Month_Copper(9) = 0
Month_Copper(10) = 0
Month_Copper(11) = 0
 
Month_Brent(0) = 0
Month_Brent(1) = 0
Month_Brent(2) = 0
Month_Brent(3) = 0
Month_Brent(4) = 0
Month_Brent(5) = 0
Month_Brent(6) = 0
Month_Brent(7) = 0
Month_Brent(8) = 0
Month_Brent(9) = 0
Month_Brent(10) = 0
Month_Brent(11) = 0
 
Month_Crude(0) = 0
Month_Crude(1) = 0
Month_Crude(2) = 0
Month_Crude(3) = 0
Month_Crude(4) = 0
Month_Crude(5) = 0
Month_Crude(6) = 0
Month_Crude(7) = 0
Month_Crude(8) = 0
Month_Crude(9) = 0
Month_Crude(10) = 0
Month_Crude(11) = 0
 
 
Number_Month_Copper(0) = 0
Number_Month_Copper(1) = 0
Number_Month_Copper(2) = 0
Number_Month_Copper(3) = 0
Number_Month_Copper(4) = 0
Number_Month_Copper(5) = 0
Number_Month_Copper(6) = 0
Number_Month_Copper(7) = 0
Number_Month_Copper(8) = 0
Number_Month_Copper(9) = 0
Number_Month_Copper(10) = 0
Number_Month_Copper(11) = 0
 
Number_Month_Brent(0) = 0
Number_Month_Brent(1) = 0
Number_Month_Brent(2) = 0
Number_Month_Brent(3) = 0
Number_Month_Brent(4) = 0
Number_Month_Brent(5) = 0
Number_Month_Brent(6) = 0
Number_Month_Brent(7) = 0
Number_Month_Brent(8) = 0
Number_Month_Brent(9) = 0
Number_Month_Brent(10) = 0
Number_Month_Brent(11) = 0
 
Number_Month_Crude(0) = 0
Number_Month_Crude(1) = 0
Number_Month_Crude(2) = 0
Number_Month_Crude(3) = 0
Number_Month_Crude(4) = 0
Number_Month_Crude(5) = 0
Number_Month_Crude(6) = 0
Number_Month_Crude(7) = 0
Number_Month_Crude(8) = 0
Number_Month_Crude(9) = 0
Number_Month_Crude(10) = 0
Number_Month_Crude(11) = 0
 
 
 
 
For Each Cel In Plage
 
    If InStr(Cel.Value, "LP_LME") <> 0 Then
 
        mydate = Cel.Offset(0, 4).Value
 
            If Month(mydate) = 1 Then
 
                Month_Copper(0) = 1
                Number_Month_Copper(0) = Number_Month_Copper(0) + 1
 
            ElseIf Month(mydate) = 2 Then
 
                Month_Copper(1) = 1
                Number_Month_Copper(1) = Number_Month_Copper(1) + 1
 
            ElseIf Month(mydate) = 3 Then
 
                Month_Copper(2) = 1
                Number_Month_Copper(2) = Number_Month_Copper(2) + 1
 
            ElseIf Month(mydate) = 4 Then
 
                Month_Copper(3) = 1
                Number_Month_Copper(3) = Number_Month_Copper(3) + 1
 
            ElseIf Month(mydate) = 5 Then
 
                Month_Copper(4) = 1
                Number_Month_Copper(4) = Number_Month_Copper(4) + 1
 
            ElseIf Month(mydate) = 6 Then
 
                Month_Copper(5) = 1
                Number_Month_Copper(5) = Number_Month_Copper(5) + 1
 
            ElseIf Month(mydate) = 7 Then
 
                Month_Copper(6) = 1
                Number_Month_Copper(6) = Number_Month_Copper(6) + 1
 
            ElseIf Month(mydate) = 8 Then
 
                Month_Copper(7) = 1
                Number_Month_Copper(7) = Number_Month_Copper(7) + 1
 
            ElseIf Month(mydate) = 9 Then
 
                Month_Copper(8) = 1
                Number_Month_Copper(8) = Number_Month_Copper(8) + 1
 
            ElseIf Month(mydate) = 10 Then
 
                Month_Copper(9) = 1
                Number_Month_Copper(9) = Number_Month_Copper(9) + 1
 
            ElseIf Month(mydate) = 11 Then
 
                Month_Copper(10) = 1
                Number_Month_Copper(10) = Number_Month_Copper(10) + 1
 
            ElseIf Month(mydate) = 12 Then
 
                Month_Copper(11) = 1
                Number_Month_Copper(11) = Number_Month_Copper(11) + 1
 
            End If
 
    End If
 
 
    If InStr(Cel.Value, "CO_ICE") <> 0 Then
 
        mydate = Cel.Offset(0, 4).Value
 
            If Month(mydate) = 1 Then
 
                Month_Brent(0) = 1
                Number_Month_Brent(0) = Number_Month_Brent(0) + 1
 
            ElseIf Month(mydate) = 2 Then
 
                Month_Brent(1) = 1
                Number_Month_Brent(1) = Number_Month_Brent(1) + 1
 
            ElseIf Month(mydate) = 3 Then
 
                Month_Brent(2) = 1
                Number_Month_Brent(2) = Number_Month_Brent(2) + 1
 
            ElseIf Month(mydate) = 4 Then
 
                Month_Brent(3) = 1
                Number_Month_Brent(3) = Number_Month_Brent(3) + 1
 
            ElseIf Month(mydate) = 5 Then
 
                Month_Brent(4) = 1
                Number_Month_Brent(4) = Number_Month_Brent(4) + 1
 
            ElseIf Month(mydate) = 6 Then
 
                Month_Brent(5) = 1
                Number_Month_Brent(5) = Number_Month_Brent(5) + 1
 
            ElseIf Month(mydate) = 7 Then
 
                Month_Brent(6) = 1
                Number_Month_Brent(6) = Number_Month_Brent(6) + 1
 
            ElseIf Month(mydate) = 8 Then
 
                Month_Brent(7) = 1
                Number_Month_Brent(7) = Number_Month_Brent(6) + 1
 
            ElseIf Month(mydate) = 9 Then
 
                Month_Brent(8) = 1
                Number_Month_Brent(8) = Number_Month_Brent(8) + 1
 
            ElseIf Month(mydate) = 10 Then
 
                Month_Brent(9) = 1
                Number_Month_Brent(9) = Number_Month_Brent(9) + 1
 
            ElseIf Month(mydate) = 11 Then
 
                Month_Brent(10) = 1
                Number_Month_Brent(10) = Number_Month_Brent(10) + 1
 
            ElseIf Month(mydate) = 12 Then
 
                Month_Brent(11) = 1
                Number_Month_Brent(11) = Number_Month_Brent(11) + 1
 
            End If
 
    End If
 
 
    If InStr(Cel.Value, "CL_NYM") <> 0 Then
 
        mydate = Cel.Offset(0, 4).Value
 
            If Month(mydate) = 1 Then
 
                Month_Crude(0) = 1
                Number_Month_Crude(0) = Number_Month_Crude(0) + 1
 
            ElseIf Month(mydate) = 2 Then
 
                Month_Crude(1) = 1
                Number_Month_Crude(1) = Number_Month_Crude(1) + 1
 
            ElseIf Month(mydate) = 3 Then
 
                Month_Crude(2) = 1
                Number_Month_Crude(2) = Number_Month_Crude(2) + 1
 
            ElseIf Month(mydate) = 4 Then
 
                Month_Crude(3) = 1
                Number_Month_Crude(3) = Number_Month_Crude(3) + 1
 
            ElseIf Month(mydate) = 5 Then
 
                Month_Crude(4) = 1
                Number_Month_Crude(4) = Number_Month_Crude(4) + 1
 
            ElseIf Month(mydate) = 6 Then
 
                Month_Crude(5) = 1
                Number_Month_Crude(5) = Number_Month_Crude(5) + 1
 
            ElseIf Month(mydate) = 7 Then
 
                Month_Crude(6) = 1
                Number_Month_Crude(6) = Number_Month_Crude(6) + 1
 
            ElseIf Month(mydate) = 8 Then
 
                Month_Crude(7) = 1
                Number_Month_Crude(7) = Number_Month_Crude(7) + 1
 
            ElseIf Month(mydate) = 9 Then
 
                Month_Crude(8) = 1
                Number_Month_Crude(8) = Number_Month_Crude(8) + 1
 
            ElseIf Month(mydate) = 10 Then
 
                Month_Crude(9) = 1
                Number_Month_Crude(9) = Number_Month_Crude(9) + 1
 
            ElseIf Month(mydate) = 11 Then
 
                Month_Crude(10) = 1
                Number_Month_Crude(10) = Number_Month_Crude(10) + 1
 
            ElseIf Month(mydate) = 12 Then
 
                Month_Crude(11) = 1
                Number_Month_Crude(11) = Number_Month_Crude(11) + 1
 
            End If
 
    End If
 
Next Cel

Daniel a moins que je n'ai pas compris mon propre code, ce qui est possible, les lignes de code que tu as copie ne font pas la meme chose, elles ne font pas la somme de cellule sur la meme plage.

**antonysansh** · 08/07/2015, 13h13

Bonjours tlm,

Quand tu déclares Dim Month_Copper(11) As Integer, les 12 valeurs de ton tableau sont déjà à 0 donc pas besoin d'initialiser les tableaux (déjà quelque lignes de gagnées)

**Tchapeto** · 08/07/2015, 13h15

Ha parfait merci beaucoup

C'est deja ca

**Daniel.C** · 08/07/2015, 13h52

Daniel a moins que je n'ai pas compris mon propre code, ce qui est possible, les lignes de code que tu as copie ne font pas la meme chose, elles ne font pas la somme de cellule sur la meme plage.

La cellule cible, c'est toujours :

Code :

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

Cel1.Offset(0, 1).Value

et la somme, c'est toujours :

Code :

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

Application.Sum(Range(Cel4, Cel4.Offset(Number_Month_Copper(0) - 1, 0)))

Où est la différence ?

**Tchapeto** · 08/07/2015, 13h58

Voila a quoi correspond quelques lignes de mon code maintenant :

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
ElseIf Month_Copper(n) = 1 And n = 2 Then
 
        Cel1.Value = "March"
 
            If Number_Month_Copper(2) = 1 Then
 
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel4, Cel4.Offset(Number_Month_Copper(2) - 1, 0)))
                Cel1.Offset(0, 2).Value = Application.Sum(Range(Cel5, Cel5.Offset(Number_Month_Copper(2) - 1, 0)))
                Cel1.Offset(0, 3).Value = Application.Sum(Range(Cel6, Cel6.Offset(Number_Month_Copper(2) - 1, 0)))
                Cel1.Offset(0, 4).Value = Application.Sum(Range(Cel7, Cel7.Offset(Number_Month_Copper(2) - 1, 0)))
 
            ElseIf Number_Month_Copper(2) = 2 Then
 
                Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel4, Cel4.Offset(Number_Month_Copper(2) - 1, 0)))
                Cel1.Offset(0, 2).Value = Application.Sum(Range(Cel5, Cel5.Offset(Number_Month_Copper(2) - 1, 0)))
                Cel1.Offset(0, 3).Value = Application.Sum(Range(Cel6, Cel6.Offset(Number_Month_Copper(2) - 1, 0)))
                Cel1.Offset(0, 4).Value = Application.Sum(Range(Cel7, Cel7.Offset(Number_Month_Copper(2) - 1, 0)))

Dans le premier groupe de 4 lignes colles, dans la premiere ligne je fais la somme de (K2:K2) et je met le resultat dans la case de droite de Cel1, dans la deuxieme ligne je fais la somme de (L2:L2) et je met le resultat 2 cases a droite de Cel4.
Dans le deuxieme groupe de 4 ligne colles, dans la premiere ligne je fais la somme de (K2:K3) et je met le resultat dans la case de droite de Cel1, dans la deuxieme ligne je fais la somme de (L2:L3) et je met le resultat 2 cases a droite de Cel4.

En esperant avoir ete clair.

**Daniel.C** · 08/07/2015, 14h17

Bon.

Poste maintenant ton code modifié.

**Tchapeto** · 08/07/2015, 14h30

Le dernier code que je t'ai envoye est le code modifie enfin juste une partie parce que sinon ca se repete ce n'est que les indices qui changent. J'ai juste modifie les OffSet.

**Daniel.C** · 08/07/2015, 14h39

Oui, mais je n'ai pas envie de modifier la totalité du code pour le reprendre ensuite. Si ça se répète, répète-le.

**Tchapeto** · 08/07/2015, 15h34

J'ai reussi a simplifier le code mais j'ai le message d'erreur suivant : Object variable or With variable not set sur la ligne ou est ecrit "Set Cel5 = Cel5.Offset(Number_Month_Copper(IntMonth), 0)" .

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
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
Sub Copper()
 
Dim Cel1 As Range
Dim Cel2 As Range
Dim Cel3 As Range
Dim Cel4 As Range
Dim Cel5 As Range
Dim Cel6 As Range
Dim Cel7 As Range
Dim Plage As Range
Dim Cel As Range
Dim mydate As Date
Dim IntMonth As Integer
Dim Month_Copper(11) As Integer
Dim Month_Brent(11) As Integer
Dim Month_Crude(11) As Integer
Dim Number_Month_Copper(11) As Integer
Dim Number_Month_Brent(11) As Integer
Dim Number_Month_Crude(11) As Integer
 
 
Set Cel1 = Sheet3.Range("J20")
Set Cel2 = Sheet3.Range("J27")
Set Cel3 = Sheet3.Range("J34")
Set Cel4 = Sheet3.Range("K2")
Set Cel5 = Sheet3.Range("L2")
Set Cel6 = Sheet3.Range("M2")
Set Cel7 = Sheet3.Range("N2")
 
Set Plage = Sheet2.Range("B2:B30")
 
 
For Each Cel In Plage
 
    If InStr(Cel.Value, "LP_LME") <> 0 Then
 
        mydate = Cel.Offset(0, 4).Value
 
            If Month(mydate) = 1 Then
 
                Month_Copper(0) = 1
                Number_Month_Copper(0) = Number_Month_Copper(0) + 1
 
            ElseIf Month(mydate) = 2 Then
 
                Month_Copper(1) = 1
                Number_Month_Copper(1) = Number_Month_Copper(1) + 1
 
            ElseIf Month(mydate) = 3 Then
 
                Month_Copper(2) = 1
                Number_Month_Copper(2) = Number_Month_Copper(2) + 1
 
            ElseIf Month(mydate) = 4 Then
 
                Month_Copper(3) = 1
                Number_Month_Copper(3) = Number_Month_Copper(3) + 1
 
            ElseIf Month(mydate) = 5 Then
 
                Month_Copper(4) = 1
                Number_Month_Copper(4) = Number_Month_Copper(4) + 1
 
            ElseIf Month(mydate) = 6 Then
 
                Month_Copper(5) = 1
                Number_Month_Copper(5) = Number_Month_Copper(5) + 1
 
            ElseIf Month(mydate) = 7 Then
 
                Month_Copper(6) = 1
                Number_Month_Copper(6) = Number_Month_Copper(6) + 1
 
            ElseIf Month(mydate) = 8 Then
 
                Month_Copper(7) = 1
                Number_Month_Copper(7) = Number_Month_Copper(7) + 1
 
            ElseIf Month(mydate) = 9 Then
 
                Month_Copper(8) = 1
                Number_Month_Copper(8) = Number_Month_Copper(8) + 1
 
            ElseIf Month(mydate) = 10 Then
 
                Month_Copper(9) = 1
                Number_Month_Copper(9) = Number_Month_Copper(9) + 1
 
            ElseIf Month(mydate) = 11 Then
 
                Month_Copper(10) = 1
                Number_Month_Copper(10) = Number_Month_Copper(10) + 1
 
            ElseIf Month(mydate) = 12 Then
 
                Month_Copper(11) = 1
                Number_Month_Copper(11) = Number_Month_Copper(11) + 1
 
            End If
 
    End If
 
 
    If InStr(Cel.Value, "CO_ICE") <> 0 Then
 
        mydate = Cel.Offset(0, 4).Value
 
            If Month(mydate) = 1 Then
 
                Month_Brent(0) = 1
                Number_Month_Brent(0) = Number_Month_Brent(0) + 1
 
            ElseIf Month(mydate) = 2 Then
 
                Month_Brent(1) = 1
                Number_Month_Brent(1) = Number_Month_Brent(1) + 1
 
            ElseIf Month(mydate) = 3 Then
 
                Month_Brent(2) = 1
                Number_Month_Brent(2) = Number_Month_Brent(2) + 1
 
            ElseIf Month(mydate) = 4 Then
 
                Month_Brent(3) = 1
                Number_Month_Brent(3) = Number_Month_Brent(3) + 1
 
            ElseIf Month(mydate) = 5 Then
 
                Month_Brent(4) = 1
                Number_Month_Brent(4) = Number_Month_Brent(4) + 1
 
            ElseIf Month(mydate) = 6 Then
 
                Month_Brent(5) = 1
                Number_Month_Brent(5) = Number_Month_Brent(5) + 1
 
            ElseIf Month(mydate) = 7 Then
 
                Month_Brent(6) = 1
                Number_Month_Brent(6) = Number_Month_Brent(6) + 1
 
            ElseIf Month(mydate) = 8 Then
 
                Month_Brent(7) = 1
                Number_Month_Brent(7) = Number_Month_Brent(6) + 1
 
            ElseIf Month(mydate) = 9 Then
 
                Month_Brent(8) = 1
                Number_Month_Brent(8) = Number_Month_Brent(8) + 1
 
            ElseIf Month(mydate) = 10 Then
 
                Month_Brent(9) = 1
                Number_Month_Brent(9) = Number_Month_Brent(9) + 1
 
            ElseIf Month(mydate) = 11 Then
 
                Month_Brent(10) = 1
                Number_Month_Brent(10) = Number_Month_Brent(10) + 1
 
            ElseIf Month(mydate) = 12 Then
 
                Month_Brent(11) = 1
                Number_Month_Brent(11) = Number_Month_Brent(11) + 1
 
            End If
 
    End If
 
 
    If InStr(Cel.Value, "CL_NYM") <> 0 Then
 
        mydate = Cel.Offset(0, 4).Value
 
            If Month(mydate) = 1 Then
 
                Month_Crude(0) = 1
                Number_Month_Crude(0) = Number_Month_Crude(0) + 1
 
            ElseIf Month(mydate) = 2 Then
 
                Month_Crude(1) = 1
                Number_Month_Crude(1) = Number_Month_Crude(1) + 1
 
            ElseIf Month(mydate) = 3 Then
 
                Month_Crude(2) = 1
                Number_Month_Crude(2) = Number_Month_Crude(2) + 1
 
            ElseIf Month(mydate) = 4 Then
 
                Month_Crude(3) = 1
                Number_Month_Crude(3) = Number_Month_Crude(3) + 1
 
            ElseIf Month(mydate) = 5 Then
 
                Month_Crude(4) = 1
                Number_Month_Crude(4) = Number_Month_Crude(4) + 1
 
            ElseIf Month(mydate) = 6 Then
 
                Month_Crude(5) = 1
                Number_Month_Crude(5) = Number_Month_Crude(5) + 1
 
            ElseIf Month(mydate) = 7 Then
 
                Month_Crude(6) = 1
                Number_Month_Crude(6) = Number_Month_Crude(6) + 1
 
            ElseIf Month(mydate) = 8 Then
 
                Month_Crude(7) = 1
                Number_Month_Crude(7) = Number_Month_Crude(7) + 1
 
            ElseIf Month(mydate) = 9 Then
 
                Month_Crude(8) = 1
                Number_Month_Crude(8) = Number_Month_Crude(8) + 1
 
            ElseIf Month(mydate) = 10 Then
 
                Month_Crude(9) = 1
                Number_Month_Crude(9) = Number_Month_Crude(9) + 1
 
            ElseIf Month(mydate) = 11 Then
 
                Month_Crude(10) = 1
                Number_Month_Crude(10) = Number_Month_Crude(10) + 1
 
            ElseIf Month(mydate) = 12 Then
 
                Month_Crude(11) = 1
                Number_Month_Crude(11) = Number_Month_Crude(11) + 1
 
            End If
 
    End If
 
Next Cel
 
For n = 0 To 11
 
    If Month_Copper(n) = 1 And n = 0 Then
        Cel1.Value = "January"
        IntMonth = 0
    ElseIf Month_Copper(n) = 1 And n = 1 Then
        Cel1.Value = "February"
        IntMonth = 1
    ElseIf Month_Copper(n) = 1 And n = 2 Then
        Cel1.Value = "March"
        IntMonth = 2
    ElseIf Month_Copper(n) = 1 And n = 3 Then
        Cel1.Value = "April"
        IntMonth = 3
    ElseIf Month_Copper(n) = 1 And n = 3 Then
        Cel1.Value = "May"
        IntMonth = 4
    ElseIf Month_Copper(n) = 1 And n = 5 Then
        Cel1.Value = "June"
        IntMonth = 5
    ElseIf Month_Copper(n) = 1 And n = 6 Then
        Cel1.Value = "July"
        IntMonth = 6
    ElseIf Month_Copper(n) = 1 And n = 7 Then
        Cel1.Value = "August"
        IntMonth = 7
    ElseIf Month_Copper(n) = 1 And n = 8 Then
        Cel1.Value = "September"
        IntMonth = 8
    ElseIf Month_Copper(n) = 1 And n = 9 Then
        Cel1.Value = "October"
        IntMonth = 9
    ElseIf Month_Copper(n) = 1 And n = 10 Then
        Cel1.Value = "November"
        IntMonth = 10
    ElseIf Month_Copper(n) = 1 And n = 11 Then
        Cel1.Value = "December"
        IntMonth = 11
 
    End If
 
    Cel1.Offset(0, 1).Value = Application.Sum(Range(Cel4, Cel4.Offset(Number_Month_Copper(IntMonth) - 1, 0)))
    Cel1.Offset(0, 2).Value = Application.Sum(Range(Cel5, Cel5.Offset(Number_Month_Copper(IntMonth) - 1, 0)))
    Cel1.Offset(0, 3).Value = Application.Sum(Range(Cel6, Cel6.Offset(Number_Month_Copper(IntMonth) - 1, 0)))
    Cel1.Offset(0, 4).Value = Application.Sum(Range(Cel7, Cel7.Offset(Number_Month_Copper(IntMonth) - 1, 0)))
 
    Set Cel1 = Cel1.Offset(1, 0)
    Set Cel4 = Cel4.Offset(Number_Month_Copper(IntMonth), 0)
    Set Cel5 = Cel5.Offset(Number_Month_Copper(IntMonth), 0)
    Set Cel6 = Cel6.Offset(Number_Month_Copper(IntMonth), 0)
    Set Cel7 = Cel7.Offset(Number_Month_Copper(IntMonth), 0)
 
Next n
 
 
 
For n = 0 To 11
 
    If Month_Brent(n) = 1 And n = 0 Then
 
        Cel2.Value = "February"
        Set Cel2 = Cel2.Offset(1, 0)
 
    ElseIf Month_Brent(n) = 1 And n = 1 Then
 
        Cel2.Value = "March"
        Set Cel2 = Cel2.Offset(1, 0)
 
    ElseIf Month_Brent(n) = 1 And n = 2 Then
 
        Cel2.Value = "April"
        Set Cel2 = Cel2.Offset(1, 0)
 
    ElseIf Month_Brent(n) = 1 And n = 3 Then
 
        Cel2.Value = "May"
        Set Cel2 = Cel2.Offset(1, 0)
 
    ElseIf Month_Brent(n) = 1 And n = 4 Then
 
        Cel2.Value = "June"
        Set Cel2 = Cel2.Offset(1, 0)
 
    ElseIf Month_Brent(n) = 1 And n = 5 Then
 
        Cel2.Value = "July"
        Set Cel2 = Cel2.Offset(1, 0)
 
    ElseIf Month_Brent(n) = 1 And n = 6 Then
 
        Cel2.Value = "August"
        Set Cel2 = Cel2.Offset(1, 0)
 
    ElseIf Month_Brent(n) = 1 And n = 7 Then
 
        Cel2.Value = "September"
        Set Cel2 = Cel2.Offset(1, 0)
 
    ElseIf Month_Brent(n) = 1 And n = 8 Then
 
        Cel2.Value = "October"
        Set Cel2 = Cel2.Offset(1, 0)
 
    ElseIf Month_Brent(n) = 1 And n = 9 Then
 
        Cel2.Value = "November"
        Set Cel2 = Cel2.Offset(1, 0)
 
    ElseIf Month_Brent(n) = 1 And n = 10 Then
 
        Cel2.Value = "December"
        Set Cel2 = Cel2.Offset(1, 0)
 
    ElseIf Month_Brent(n) = 1 And n = 11 Then
 
        Cel2.Value = "January"
        Set Cel2 = Cel2.Offset(1, 0)
 
    End If
 
Next n
 
 
For n = 0 To 11
 
    If Month_Crude(n) = 1 And n = 0 Then
 
        Cel3.Value = "February"
        Set Cel3 = Cel3.Offset(1, 0)
 
    ElseIf Month_Crude(n) = 1 And n = 1 Then
 
        Cel3.Value = "March"
        Set Cel3 = Cel3.Offset(1, 0)
 
    ElseIf Month_Crude(n) = 1 And n = 2 Then
 
        Cel3.Value = "April"
        Set Cel3 = Cel3.Offset(1, 0)
 
    ElseIf Month_Crude(n) = 1 And n = 3 Then
 
        Cel3.Value = "May"
        Set Cel3 = Cel3.Offset(1, 0)
 
    ElseIf Month_Crude(n) = 1 And n = 4 Then
 
        Cel3.Value = "June"
        Set Cel3 = Cel3.Offset(1, 0)
 
    ElseIf Month_Crude(n) = 1 And n = 5 Then
 
        Cel3.Value = "July"
        Set Cel3 = Cel3.Offset(1, 0)
 
    ElseIf Month_Crude(n) = 1 And n = 6 Then
 
        Cel3.Value = "August"
        Set Cel3 = Cel3.Offset(1, 0)
 
    ElseIf Month_Crude(n) = 1 And n = 7 Then
 
        Cel3.Value = "September"
        Set Cel3 = Cel3.Offset(1, 0)
 
    ElseIf Month_Crude(n) = 1 And n = 8 Then
 
        Cel3.Value = "October"
        Set Cel3 = Cel3.Offset(1, 0)
 
    ElseIf Month_Crude(n) = 1 And n = 9 Then
 
        Cel3.Value = "November"
        Set Cel3 = Cel3.Offset(1, 0)
 
    ElseIf Month_Crude(n) = 1 And n = 10 Then
 
        Cel3.Value = "December"
        Set Cel3 = Cel3.Offset(1, 0)
 
    ElseIf Month_Crude(n) = 1 And n = 11 Then
 
        Cel3.Value = "January"
        Set Cel3 = Cel3.Offset(1, 0)
 
    End If
 
Next n
 
End Sub

**Daniel.C** · 08/07/2015, 15h59

Remplace

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
If Month(mydate) = 1 Then
 
                Month_Copper(0) = 1
                Number_Month_Copper(0) = Number_Month_Copper(0) + 1
 
            ElseIf Month(mydate) = 2 Then
 
                Month_Copper(1) = 1
                Number_Month_Copper(1) = Number_Month_Copper(1) + 1
 
            ElseIf Month(mydate) = 3 Then
 
                Month_Copper(2) = 1
                Number_Month_Copper(2) = Number_Month_Copper(2) + 1
 
            ElseIf Month(mydate) = 4 Then
 
                Month_Copper(3) = 1
                Number_Month_Copper(3) = Number_Month_Copper(3) + 1
 
            ElseIf Month(mydate) = 5 Then
 
                Month_Copper(4) = 1
                Number_Month_Copper(4) = Number_Month_Copper(4) + 1
 
            ElseIf Month(mydate) = 6 Then
 
                Month_Copper(5) = 1
                Number_Month_Copper(5) = Number_Month_Copper(5) + 1
 
            ElseIf Month(mydate) = 7 Then
 
                Month_Copper(6) = 1
                Number_Month_Copper(6) = Number_Month_Copper(6) + 1
 
            ElseIf Month(mydate) = 8 Then
 
                Month_Copper(7) = 1
                Number_Month_Copper(7) = Number_Month_Copper(7) + 1
 
            ElseIf Month(mydate) = 9 Then
 
                Month_Copper(8) = 1
                Number_Month_Copper(8) = Number_Month_Copper(8) + 1
 
            ElseIf Month(mydate) = 10 Then
 
                Month_Copper(9) = 1
                Number_Month_Copper(9) = Number_Month_Copper(9) + 1
 
            ElseIf Month(mydate) = 11 Then
 
                Month_Copper(10) = 1
                Number_Month_Copper(10) = Number_Month_Copper(10) + 1
 
            ElseIf Month(mydate) = 12 Then
 
                Month_Copper(11) = 1
                Number_Month_Copper(11) = Number_Month_Copper(11) + 1
 
            End If
 
    End If

par :

Code :

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

1
2
    Month_Copper(Month(mydate) - 1) = 1
    Number_Month_Copper(Month(mydate) - 1) = Number_Month_Copper(Month(mydate) - 1) + 1

**Tchapeto** · 08/07/2015, 16h09

Ha oui tres bonne idee ! (y)

Je viens de modifier le code mais j'ai toujours le meme message d'erreur et toujours sur la meme ligne

**Daniel.C** · 08/07/2015, 17h17

Le problème vient de tes données. Quelles sont es valeurs de "n", "IntMonth" et "Number_Month_Copper(IntMonth)" ainsi que l'adresse de Cel5 au moment du plantage ?

**clementmarcotte** · 08/07/2015, 20h37

Envoyé par Tchapeto

Bonjour a tous,

Elle est compose de plein de ElseIf et donc je me suis pose la question si utilise Case Is pourrait me raccourcir mon code. Pourriez-vous me dire si telle est le cas comment changer mon code en mettant Case is ?

À la base, remplacer un bloc If then else, par un bloc Select Case, ne devrait pas réduire, ou si peu, le nombre de lignes. Même qu'à l'époque de QuickBasic (pour MSDOS) quelqu'un avait fait un test. Il avait utilisé un bloc IF et un Bloc Select Case. Les deux blocs une fois compilés étaient rigoureusement identique.

Le principal avantage de Select Case est la possibilité de programmer rapidement des tests sur des valeurs uniques et des variables uniques sans devoir rabouter des milles de tests pleins de or et de and. Mais un Select Case est parfaitement incapable de tester sans simagrées de tout genre deux variables dans le même bloc

par exemple

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
If i = 3 or i = 36 or i = 894 then
    Nom = "Hector"
elseif i = 543 then
    Nom = "Ernestine"
else
    Nom = "Rien pantoute"
end if
 
select case i
    case 3,36,894
        Nom = "Hector"
    case 543
        Nom = "Ernestine"
    case else
        Nom = "Rien pantoute"
End select

sont équivalents, même que Select case a une ligne de plus

Mais avoir l'équivalent de ceci est impossible dans un select case

Code :

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

1
2
3
4
5
6
7
If i = 3 or i = 36 or i = 894 then
    Nom = "Hector"
elseif Isidore = 543 then
    Nom = "Ernestine"
else
    Nom = "Rien pantoute"
end if

Case is a la place de ElseIf

Macros et VBA Excel

Discussions similaires

Partager

Partager