Microsoft 365 Calcul d'une somme entre deux bornes Vba

[Piment]

Bonjour à tous, Soan,
Je crée un nouveau fil de discussion car je n'ai pas eu d'aide sur mon précédent fil.
Avant tout, merci aux internautes qui ont eu la gentillesse de m'aider sur ce Forum, qui n'a pas son pareil.
Je fais appel à nouveau à vos compétences.
Ceux qui m'ont précédemment aidé sur ce fichier verront que je l'ai quelque peu fait évoluer.
Je pense être dans la dernière ligne droite.
Donc, voici ma difficulté:
Dans la feuille VentilationCouts, je voudrais que la colonne C 7 additionne les lignes se trouvant entre les deux zones vertes: additionne les dépenses de chaque "Cellule DGS" "Cellule Secrétariat..." ect...
Les Services additionnent toutes les "Cellules" qui sont à l'intérieur du Service: "Cellule DGS", "Cellule Secrétariat...", "Cellule Police..." ect.
Les Directions additionnent tous les services qui sont à l'intérieur de la Direction.
J'a beaucoup cherché, mais je n'y arrive pas.
J'ai une erreur #Nom? en C7 gérée par la ligne de code de la formule de calcul de l'USF Nouveau_Numero.
Je suis bloqué sur cette ligne de code, et donc pas beaucoup avancé.
Aussi, je serais heureux de pouvoir bénéficier d'une aide de la communauté.
Ci-joint le fichier.
Merci à vous.

 

[Piment]

Bonjour Jean-Marie, bonjour le Forum,
Je reviens vers vous car je n'ai toujours pas trouvé le début d'un commencement de solution pour faire le calcul des sommes des cellules d'un Service et des Services d'une Direction.
J'ai vainement cherché! J'avais pensé à la notion Parents/Enfants/Petits-enfants, mais je ne sais absolument pas comment faire. Je me permet de rappeler que je suis novice en VBA, même si j'ai énormément appris grâce au Forum.
Malgré tout j'ai un peu avancé dans l'élaboration de mon fichier, comme vous pourrez le constater avec le fichier joint.
Certes je pourrais utiliser les formules de calcul directement dans les cellules "Services" et "Directions", mais ce n'est pas le but recherché. Je voudrais vraiment rendre ce fichier opérationnel avec du VBA.
Alors je vous sollicite à nouveau.
Gros Merci pour votre aide.

 

[ChTi160]

Bonjour Piment
pourrais tu me mettre en exemple le cas du 001 (Ligne Direction Générale des Services)
Ligne 6 à 25 juste cela avec les sommes la Ou elles doivent être (peut être avec des Formules) ce qui me permettra de voir comment faire et a partir de quoi !
met des commentaires etc etc
je pense faire une recherche par Couleur de fond des cellules !
il me faut donc savoir ce qui doit être sommé et ou cela doit être mis dans le Tableau (Colonnes)
merci par avance
Jean marie

 

[Piment]

Bonjour Jean-Marie,
Heureux de te lire de nouveau.
Je te mets le fichier en pièce jointe.
L'idée de faire une recherche par couleur de fond des cellules est bonne, Job75 m'avait fait une proposition dans ce sens pour le calcul des "Cellules" (cellules vertes), mais je n'avais pas retenu sa proposition dans la mesure où si on change la couleur de fond des cellules, le programme va planter. J'avoue également que je n'avais pas bien compris son code. Je me suis appuyé sur son code pour essayer de le reproduire pour le Calcul des Services en m'appuyant sur le principe de "cellules parents", mais je n'y suis pa

 

[Piment]

je ne sais pas ce qui s'est passé, mais le post a été envoyé alors que je n'avais fini: l'insolent!
Donc je disais que je n'y étais pas arrivé à le transposer. J'ai fait un paquet de recherche sur cette histoire de cellule parent mais sans succès. Il est vrai que ce principe collerait bien avec ce que je veux obtenir: le Père fait le total des Fils, les Fils font les totaux des Petits-Fils: Père=Direction, Fils=Service, Petit-Fils=Cellule.
Le total des Cellules est réglé à ce jour et fonctionne parfaitement, ainsi que les calculs liés aux Cellules.
Il me reste à faire les calculs liés aux Direction et aux Services.
J'ai bien compris que cette affaire de calcul n'est apparemment pas simple. Mais je suis convaincu que ce n'est pas impossible, et qu'il y aura forcément une solution.
N'hésite pas à m'interpeller si besoin.
Avec toute ma reconnaissance!

 

[job75]

Bonjour Piment, ChTi160,

Le fichier du post #31 en retour avec cette macro dans le code de la feuille ""VentilationCouts" :

Private Sub Worksheet_Change(ByVal Target As Range)
Dim coulDirection&, coulService&, coulCellule&, nlig&, tablo#(), liste&(), i&, j%, s#, k&, coul&, v, TOTAL#
coulDirection = [A6].Interior.Color 'rouge
coulService = [A7].Interior.Color 'bleu
coulCellule = [A8].Interior.Color 'vert
nlig = Range("A1", UsedRange).Rows.Count
ReDim tablo(1 To 12) 'pour les 12 colonnes C à N (fait gagner du temps)
'---liste des couleurs (fait gagner du temps)
ReDim liste(1 To nlig)
For i = 6 To nlig
    liste(i) = Cells(i, 1).Interior.Color
Next
Application.ScreenUpdating = False
Application.EnableEvents = False 'désactive les évènements
'---vert (cellule)---
For i = 8 To nlig
    If liste(i) = coulCellule Then
        For j = 3 To 14 'colonnes C à N
            s = 0
            For k = i + 1 To nlig
                coul = liste(k)
                If coul = coulDirection Or coul = coulService Or coul = coulCellule Then Exit For
                v = Cells(k, j)
                If IsNumeric(v) Then s = s + CDbl(v)
                If j = 3 Then Cells(k, 15) = Application.Sum(Range(Cells(k, 3), Cells(k, 14))) 'total des cellules incolores
            Next k
            tablo(j - 2) = s
        Next j
        Range(Cells(i, 3), Cells(i, 14)) = tablo
        Cells(i, 15) = Application.Sum(tablo)
    End If
Next i
'---bleu (service)---
For i = 7 To nlig
    If liste(i) = coulService Then
        For j = 3 To 14
            s = 0
            For k = i + 1 To nlig
                coul = liste(k)
                If coul = coulDirection Or coul = coulService Then Exit For
                If coul = coulCellule Then
                    v = Cells(k, j)
                    If IsNumeric(v) Then s = s + CDbl(v)
                End If
            Next k
            tablo(j - 2) = s
        Next j
        Range(Cells(i, 3), Cells(i, 14)) = tablo
        Cells(i, 15) = Application.Sum(tablo)
    End If
Next i
'---rouge (direction)---
For i = 6 To nlig
    If liste(i) = coulDirection Then
        For j = 3 To 14
            s = 0
            For k = i + 1 To nlig
                coul = liste(k)
                If coul = coulDirection Then Exit For
                If coul = coulService Then
                    v = Cells(k, j)
                    If IsNumeric(v) Then s = s + CDbl(v)
                End If
            Next k
            tablo(j - 2) = s
        Next j
        Range(Cells(i, 3), Cells(i, 14)) = tablo
        Cells(i, 15) = Application.Sum(tablo)
        TOTAL = TOTAL + Cells(i, 15)
    End If
Next i
[O5] = TOTAL
Application.EnableEvents = True 'réactive les évènements
End Sub

Elle se déclenche quand on modifie ou valide une cellule quelconque.

Comme on le voit elle étudie les cellules en s'appuyant sur leurs couleurs.

PS : les macros dans ThisWorkbook étant casse-pieds je les ai désactivées.

A+

 

[Piment]

Job75 bonjour,
Heureux de te relire et grand merci pour ta production. Elle fait exactement ce que je voulais.
Si j'ai bien compris, le calcul se fait sur la base des couleurs de fond de la cellule. Donc dans l'absolue si je change la couleur des Services, le calcul ne devrait pas se faire. Hors même si je change la couleur des services, le calcul se fait! ????
Je n'ai pas encore regardé en profondeur tes codes pour me les approprier (ce sera fait très rapidement).
N'y aurait-il pas moyen d'obtenir cet excellent résultat autrement que par les couleurs (notion Parent, Fils...).
Cela permettrait d'avoir des solutions à adapter selon les besoins.
Je me mets à essayer de comprendre tes lignes de codes et je reviens vers toi.
Gros merci à toi.

 

[ChTi160]

Re
Beau travail deJob75 !
donc si j'ai bien compris ton Tableau est Fixe et ne subiras pas d'ajout de Ligne "Cellule DGS" n'a qu'une Ligne "B" c'est ça ?
jean marie

 

[job75]

Hors même si je change la couleur des services, le calcul se fait! ????

Bien sûr puisque les couleurs de référence utilisées sont celles des cellules A6 A7 A8.

donc si j'ai bien compris ton Tableau est Fixe et ne subiras pas d'ajout de Ligne

Le tableau est dynamique, on peut ajouter ou supprimer des lignes comme on veut.

 

[974RE]

Jean-Marie bonjour,
Effectivement, le tableau est dynamique, on rajoute un nouveau contact, ou on en supprime.
Job75, je m'en suis rendu compte en essayant de comprendre tes codes. En faite, il prend la couleur que l'on aura mis. Ce qui évite tout plantage puisque la couleur n'est pas définie par avance et donc figée! Ingénieux le zaffaire!
Jean-Marie, ne lâche pas le bout, car j'aimerais assez que l'on trouve une solution avec cette histoire de cellule Parent. J'aimerais bien avoir une solution supplémentaire qui me permettrait de pouvoir adapter en fonction du besoin.
Merci à vous pour le mal que vous vous donnez à aider les novices comme moi.
J'aimerais assez à avoir votre avis de pro sur le fichier dans sa globalité, même s'il n'est pas tout à fait terminé. Cela me permettra de l'améliorer.
Merci encore.

 

[job75]

N'y aurait-il pas moyen d'obtenir cet excellent résultat autrement que par les couleurs (notion Parent, Fils...).
Cela permettrait d'avoir des solutions à adapter selon les besoins.

Oui, voyez ce fichier (2) et la macro :

Private Sub Worksheet_Change(ByVal Target As Range)
Dim nlig&, tablo#(), dico As Object, c As Range, i&, j%, s#, k&, v, TOTAL#
nlig = Range("A1", UsedRange).Rows.Count
ReDim tablo(1 To 12) 'pour les 12 colonnes C à N (fait gagner du temps)
'---liste des Directions---
Set dico = CreateObject("Scripting.Dictionary")
For Each c In [ZListDirection] 'plage nommée
    dico(c.Value) = ""
Next c
Application.ScreenUpdating = False
Application.EnableEvents = False 'désactive les évènements
'---Cellule---
For i = 8 To nlig
    If Cells(i, 2) Like "Cellule*" Then
        For j = 3 To 14 'colonnes C à N
            s = 0
            For k = i + 1 To nlig
                v = Cells(k, 2)
                If dico.exists(v) Or v Like "Service*" Or v Like "Cellule*" Then Exit For
                v = Cells(k, j)
                If IsNumeric(v) Then s = s + CDbl(v)
                If j = 3 Then Cells(k, 15) = Application.Sum(Range(Cells(k, 3), Cells(k, 14))) 'total des cellules incolores
            Next k
            tablo(j - 2) = s
        Next j
        Range(Cells(i, 3), Cells(i, 14)) = tablo
        Cells(i, 15) = Application.Sum(tablo)
    End If
Next i
'---Service---
For i = 7 To nlig
    If Cells(i, 2) Like "Service*" Then
        For j = 3 To 14
            s = 0
            For k = i + 1 To nlig
                v = Cells(k, 2)
                If dico.exists(v) Or v Like "Service*" Then Exit For
                If v Like "Cellule*" Then
                    v = Cells(k, j)
                    If IsNumeric(v) Then s = s + CDbl(v)
                End If
            Next k
            tablo(j - 2) = s
        Next j
        Range(Cells(i, 3), Cells(i, 14)) = tablo
        Cells(i, 15) = Application.Sum(tablo)
    End If
Next i
'---Direction---
For i = 6 To nlig
    If dico.exists(Cells(i, 2).Value) Then
        For j = 3 To 14
            s = 0
            For k = i + 1 To nlig
                v = Cells(k, 2)
                If dico.exists(v) Then Exit For
                If v Like "Service*" Then
                    v = Cells(k, j)
                    If IsNumeric(v) Then s = s + CDbl(v)
                End If
            Next k
            tablo(j - 2) = s
        Next j
        Range(Cells(i, 3), Cells(i, 14)) = tablo
        Cells(i, 15) = Application.Sum(tablo)
        TOTAL = TOTAL + Cells(i, 15)
    End If
Next i
[O5] = TOTAL
Application.EnableEvents = True 'réactive les évènements
End Sub

Elle étudie les cellules en s'appuyant maintenant sur les textes en colonne B.

Elle s'exécute chez moi en 0,26 seconde contre 0,29 seconde pour celle du post #35.

 

[Piment]

Bonjour Job75, bonjour Jean-Marie,
Job75 merci beaucoup pour ta production. J'ai testé tes codes ce matin en arrivant au bureau, et cela fonctionne pour une partie du tableau. en effet, si je rentre un nombre dans la cellule informatique de la Direction "Administration Générale" (C29), le total Service et Cellule (C27 et C28) se font mais le total "Direction Administration Général" (C26) lui ne se fait pas. Par contre le total de la Direction Générale des Services (C6) lui prend en compte le nombre qui a été rajouté en C29.
Or, chaque Direction doit faire le calcul des Services qui sont dans la Direction en question. Chaque bloc de Direction contient un nombre de services qui contient un nombre de cellules.
La zone bleu-marine (C5 à N5) reçoit le calcul de toutes les Directions du Mois: exemple:
C5= C6+C26+C39+C46+C79+C103+C107 cela me donne le montant des dépenses du Mois, que je réutilise pour l'analyse des données.
Si tu pouvais trouver le temps de me mettre des commentaires face à tes lignes de codes, cela augmenterait considérablement ton capital de sympathie, qui est déjà très conséquent.
Cela me permettra de comprendre le pourquoi des choses.
Dans l'attente de ton retour, je vais m'atteler à essayer de comprendre tes lignes de codes.
Merci encore.

 

[job75]

Bonjour Piment, le forum,

si je rentre un nombre dans la cellule informatique de la Direction "Administration Générale" (C29), le total Service et Cellule (C27 et C28) se font mais le total "Direction Administration Général" (C26) lui ne se fait pas. Par contre le total de la Direction Générale des Services (C6) lui prend en compte le nombre qui a été rajouté en C29.

Sur le fichier (2) du post #40 si j'entre 500 en C29 il s'inscrit bien 500 en C28 C27 et C26.

Si ce n'est pas le cas sur votre fichier c'est que le texte en B26 n'existe pas dans la plage ZListDirection de l feuille Parametres.

Ce doit être Administration Générale sans espace superflu et avec les accents, vérifiez bien.

C'est l'inconvénient d'utiliser les textes comme référence, il peut y avoir des erreurs alors qu'en utilisant les couleurs c'est peu probable.

Pour ce qui est des explications les commentaires dans le code sont clairs : on étudie dans l'ordre les cellules contenant "Cellule" puis "Service" puis un texte de la liste ZListDirection.

La hiérarchie de ce que vous appelez parents-enfants est ainsi bien respectée.

A+

 

[Piment]

Bonjour Piment, le forum,

Sur le fichier (2) du post #40 si j'entre 500 en C29 il s'inscrit bien 500 en C28 C27 et C26.

Si ce n'est pas le cas sur votre fichier c'est que le texte en B26 n'existe pas dans la plage ZListDirection de l feuille Parametres.

Ce doit être Administration Générale sans espace superflu et avec les accents, vérifiez bien.

C'est l'inconvénient d'utiliser les textes comme référence, il peut y avoir des erreurs alors qu'en utilisant les couleurs c'est peu probable.

Pour ce qui est des explications les commentaires dans le code sont clairs : on étudie dans l'ordre les cellules contenant "Cellule" puis "Service" puis un texte de la liste ZListDirection.

La hiérarchie de ce que vous appelez parents-enfants est ainsi bien respectée.

A+

Effectivement, il y avait bien un espace supplémentaire après chaque mot dans la plage ZlistDirection de la feuille Parametres.
Autant pour moi.
C'est vrai que d'utiliser les couleurs on évite ces problèmes de frappe.
Tout fonctionne parfaitement.
Très gros merci pour le temps que vous m'avez consacré.
Je vais pouvoir continuer à avancer.

 

[job75]

Bonjour Piment, le forum,

Ici j'ai remplacé les 3 boucles For i par une seule inversée For i = nlig To 6 Step -1.

Cela ne fait pratiquement rien gagner en temps de calcul mais c'est plus logique.

Fichier (1 bis) avec les couleurs, fichier (2 bis) avec les textes.

A+

 

[Piment]

Bonjour Job75,
Si j'ai bien compris au lieu des 3 boucles définies pour Cellule, Service, Direction, il n'y en a plus qu'une pour les 3, et la boucle démarre de la dernière ligne pour remonter, et cela d'un pas, d'où le - 1.
Je n'ai pas encore tout compris dans tes codes et je les tests en essayant de faire les calculs pour les cellules des colonnes P Q R et S des lignes Cellule Service et Direction.
Merci beaucoup pour toute l'aide que vous m'apportez.