IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Macros et VBA Excel Discussion :

Calcul avec plages de cellules variables [XL-2010]


Sujet :

Macros et VBA Excel

Vue hybride

Message précédent Message précédent   Message suivant Message suivant
  1. #1
    Membre averti
    Femme Profil pro
    Étudiant
    Inscrit en
    Mars 2015
    Messages
    23
    Détails du profil
    Informations personnelles :
    Sexe : Femme
    Âge : 35
    Localisation : France, Pyrénées Atlantiques (Aquitaine)

    Informations professionnelles :
    Activité : Étudiant
    Secteur : Enseignement

    Informations forums :
    Inscription : Mars 2015
    Messages : 23
    Par défaut Calcul avec plages de cellules variables
    Bonjour tout le monde,
    J’ai besoin de votre aide pour une macro qui me calcule la quantité produite par semaine :

    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
    '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 : 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
    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é
    Fichiers attachés Fichiers attachés

  2. #2
    Expert confirmé

    Homme Profil pro
    Curieux
    Inscrit en
    Juillet 2012
    Messages
    5 169
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Curieux
    Secteur : Arts - Culture

    Informations forums :
    Inscription : Juillet 2012
    Messages : 5 169
    Billets dans le blog
    5
    Par défaut
    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 : 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
    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

  3. #3
    Membre averti
    Femme Profil pro
    Étudiant
    Inscrit en
    Mars 2015
    Messages
    23
    Détails du profil
    Informations personnelles :
    Sexe : Femme
    Âge : 35
    Localisation : France, Pyrénées Atlantiques (Aquitaine)

    Informations professionnelles :
    Activité : Étudiant
    Secteur : Enseignement

    Informations forums :
    Inscription : Mars 2015
    Messages : 23
    Par défaut
    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 !!

  4. #4
    Expert confirmé

    Homme Profil pro
    Curieux
    Inscrit en
    Juillet 2012
    Messages
    5 169
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Curieux
    Secteur : Arts - Culture

    Informations forums :
    Inscription : Juillet 2012
    Messages : 5 169
    Billets dans le blog
    5
    Par défaut
    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

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. Réponses: 5
    Dernier message: 22/03/2014, 12h20
  2. Somme dépendant de plage de cellules variable
    Par Talieth dans le forum Excel
    Réponses: 4
    Dernier message: 23/04/2007, 10h37
  3. appliquer une macro a une plage de cellule variable
    Par johndeuf dans le forum Macros et VBA Excel
    Réponses: 4
    Dernier message: 10/11/2006, 16h18
  4. [VBA-E]Selection plage de cellules variable
    Par julien5 dans le forum Macros et VBA Excel
    Réponses: 3
    Dernier message: 23/05/2006, 14h51
  5. [VBA-E] graphique avec plage de donnees variable
    Par gigs dans le forum Macros et VBA Excel
    Réponses: 13
    Dernier message: 03/04/2006, 14h50

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo