Calcul avec plages de cellules variables

Version imprimable

1 pièce(s) jointe(s)

Bonjour tout le monde,
J’ai besoin de votre aide pour une macro qui me calcule la quantité produite par semaine :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
'Mois de janvier
Range("G26:G40").FormulaLocal = "=Somme(F7:J7)"
'Mois de fevrier
Range("H26:H40").FormulaLocal = "=Somme(K7:N7)"
'Mois de mars
Range("I26:I40").FormulaLocal = "=Somme(O7:S7)"
'Mois de avril
Range("J26:J40").FormulaLocal = "=Somme(T7:W7)"
'Mois de mai
Range("K26:K40").FormulaLocal = "=Somme(X7:AA7)"
'Mois de juin
Range("L26:L40").FormulaLocal = "=Somme(AB7:AF7)"
'Mois de juillet
Range("M26:M40").FormulaLocal = "=Somme(AG7:AJ7)"
'Mois de aout
Range("N26:N40").FormulaLocal = "=Somme(AK7:AN7)"
'Mois de septembre
Range("O26:O40").FormulaLocal = "=Somme(AO7:AS7)"
'Mois de octobre
Range("P26:P40").FormulaLocal = "=Somme(AT7:AW7)"
'Mois de novembre
Range("Q26:Q40").FormulaLocal = "=Somme(AX7:BB7)"
'Mois de decembre
Range("R26:R40").FormulaLocal = "=Somme(BC7:BE7)"

Le problème c’est que le nombre de semaine peut varier par exemple le mois de janvier peut passer de 5 semaines à 4 semaines donc le calcul de F7 :J7 à F7 :I7.

J'ai essayé le code suivant mais je n'arrive pas a voir les formules dans mes cellules cibles et le résultat est faux et je ne sais pas d'ou provient l'erreur :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
Sub additionner()
    Dim a As Integer
    Dim b As Integer
    Dim c As Integer
    Dim d As Integer
    Dim e As Integer
    Dim f As Integer
    Dim g As Integer
    Dim h As Integer
    Dim i As Integer
    Dim j As Integer
    Dim k As Integer
    Dim l As Integer
  a = Sheets("Parametres").Cells(3, 5).Value
  b = Sheets("Parametres").Cells(4, 5).Value
  c = Sheets("Parametres").Cells(5, 5).Value
  d = Sheets("Parametres").Cells(6, 5).Value
  e = Sheets("Parametres").Cells(7, 5).Value
  f = Sheets("Parametres").Cells(8, 5).Value
  g = Sheets("Parametres").Cells(9, 5).Value
  h = Sheets("Parametres").Cells(10, 5).Value
  i = Sheets("Parametres").Cells(11, 5).Value
  j = Sheets("Parametres").Cells(12, 5).Value
  k = Sheets("Parametres").Cells(13, 5).Value
  l = Sheets("Parametres").Cells(14, 5).Value
 
'Mois de janvier
Range("G26:G40").Formula = Application.Sum(Range(Cells(7, 6), Cells(7, 5 + a)))
'Mois de fevrier
Range("H26:H40").Formula = Application.Sum(Range(Cells(7, 6 + a), Cells(7, 5 + a + b)))
'Mois de mars
Range("I26:I40").Formula = Application.Sum(Range(Cells(7, 6 + a + b), Cells(7, 5 + a + b + c)))
'Mois de avril
Range("J26:J40").Formula = Application.Sum(Range(Cells(7, 6 + a + b + c), Cells(7, 5 + a + b + c + d)))
'Mois de mai
Range("K26:K40").Formula = Application.Sum(Range(Cells(7, 6 + a + b + c + d), Cells(7, 5 + a + b + c + d + e)))
'Mois de juin
Range("L26:L40").Formula = Application.Sum(Range(Cells(7, 6 + a + b + c + d + e), Cells(7, 5 + a + b + c + d + e + f)))
'Mois de juillet
Range("M26:M40").Formula = Application.Sum(Range(Cells(7, 6 + a + b + c + d + e + f), Cells(7, 5 + a + b + c + d + e + f + g)))
'Mois de aout
Range("N26:N40").Formula = Application.Sum(Range(Cells(7, 6 + a + b + c + d + e + f + g), Cells(7, 5 + a + b + c + d + e + f + g + h)))
'Mois de septembre
Range("O26:O40").Formula = Application.Sum(Range(Cells(7, 6 + a + b + c + d + e + f + g + h), Cells(7, 5 + a + b + c + d + e + f + g + h + i)))
'Mois de octobre
Range("P26:P40").Formula = Application.Sum(Range(Cells(7, 6 + a + b + c + d + e + f + g + h + i), Cells(7, 5 + a + b + c + d + e + f + g + h + i + j)))
'Mois de novembre
Range("Q26:Q40").Formula = Application.Sum(Range(Cells(7, 6 + a + b + c + d + e + f + g + h + i + j), Cells(7, 5 + a + b + c + d + e + f + g + h + i + j + k)))
'Mois de decembre
Range("R26:R40").Formula = Application.Sum(Range(Cells(7, 6 + a + b + c + d + e + f + g + h + i + j + k), Cells(7, 5 + a + b + c + d + e + f + g + h + i + j + k + l)))
 
End sub

Merci pour votre temps

p.s. : fichier en pj pour plus de clarté :)

Bonjour,

voici une solution un peu plus ... optimisée ;)

l'idée, c'est de mettre dans un tableau virtuel chaque nombre de semaines pour tes mois
on obtient un tableau avec 12 nombres (les 12 "combien de semaines pour chacun des 12 mois")

ensuite, on va utiliser ces données pour naviguer dans ton tableau de données, et écrire les formules dans ton tableau de synthèse

c'est testé et fonctionnel sur le fichier transmis !

d'ailleurs ... tu préfères pas avoir directement le résultat plutôt que les formules ??

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
Sub MesSemaines()
Dim TabSem
Dim i As Long
Dim ColDepart As Long
 
' récupération des nombres de semaines de chaque mois
With Worksheets("Parametres")
    TabSem = Application.WorksheetFunction.Transpose(.Range(.Cells(3, 5), .Cells(14, 5)))
End With
 
With Worksheets("Feuil2")
    ' les données démarrent en colonne F
    ColDepart = 6
    ' pour chaque mois
    For i = LBound(TabSem) To UBound(TabSem)
        ' écriture de la formule des totaux
        .Range(.Cells(27, 6 + i), .Cells(40, 6 + i)).Formula = _
                "=SUM(" & .Range(.Cells(7, ColDepart), .Cells(7, ColDepart + TabSem(i) - 1)).Address(False, False) & ")"
 
                ' autre méthode d'écriture
                '"=SUM(" & .Cells(7, ColDepart).Address(False, False) & ":" & .Cells(7, ColDepart + TabSem(i) - 1).Address(False, False) & ")"
 
        ' on décale la colonne de départ des données quand on change de mois
        ' en fait, on ajoute le nombre de semaines sur le mois qu'on vient de traiter
        ColDepart = ColDepart + TabSem(i)
    Next i
End With
 
End Sub

11/06/2015, 17h43
shipuden64

Holala c'est parfait c'est vrai que je n'arrive pas à pousser assez loin la reflexion pour optimiser mes codes mais la c'est juste magique :)))
Je te remercie.
En fait les formules je veux me les garder pour pouvoir vérifier les cellules sélectionnées.
Merci !! :D
11/06/2015, 18h15
joe.levrai

Je pense que tu souffres du même symptôme que moi, quand j'ai appris dans un fond de garage à faire des macros tout seul.
Tu fonces tête baissée et tu écris de la ligne de code au kilomètre ... en ajoutant des variables à chaque fois que t'es coincée

Pour résoudre ton problème, il faut simplement modifier ta démarche, et "perdre" du temps à analyser comment aller droit au but.
Pour se faire, on identifie les données essentielles, avec lesquelles on peut obtenir le résultat

ici en l'occurrence, je me suis posé 10 minutes pour réaliser qu'avec uniquement le nombre de semaines de chaque mois, on pouvait naviguer sur l'ensemble de ta feuille de synthèse (puisque sa structure est fixe) et écrire les formules

et comble de la chance, ces nombres de semaines sont stockées dans ta feuille de synthèse !

Une fois que tu as fini cette étape, écrire la macro devient plus simple, tu ne dois plus chercher de variables, mais uniquement utiliser ton fil d'ariane (les numéros de semaines)

J'ai réalisé le remplissage de la première cellule de janvier
Puis, j'ai introduit la plage complète du mois pour remplir tout janvier
Ensuite, j'ai introduit une boucle pour remplir l'ensemble des mois

ça m'a pris du temps d'adopter cette démarche (plus d'un an !), mais c'est aujourd'hui payant