Discussion :

[coeurdange]

J'ai un tableau contenant des sections de 4 lignes. Un bouton me permet d'ajouter une section supplémentaire au tableau grâce à du vba.



Je souhaiterais ajouter une ligne finale au tableau contenant le nombre total d'expert et une formule pour calculer le total. Cette somme doit être l'addition des cellules TOTAL de chaque section.

Ici ma fonction vba pour créer la section:

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
Sub AddANewPrivateExpert()
 
    Dim DerniereLigne As Long
    Dim Sh As Worksheet
 
    Set Sh = ActiveSheet
 
    With Sh
         DerniereLigne = .Cells(.Rows.Count, "F").End(xlUp).Row
         .Range(.Cells(DerniereLigne + 1, "A"), .Cells(DerniereLigne + 4, "F")).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
         .Range(.Cells(DerniereLigne - 3, "A"), .Cells(DerniereLigne, "F")).Copy Destination:=.Cells(DerniereLigne + 1, "A")
         .Range(.Cells(DerniereLigne + 1, "A"), .Cells(DerniereLigne + 1, "F")).Borders(xlEdgeTop).Weight = xlMedium
         .Cells(DerniereLigne + 1, "A") = .Cells(DerniereLigne - 3, "A") + 1
         .Range(.Cells(DerniereLigne + 2, "D"), .Cells(DerniereLigne + 3, "D")).ClearContents
    End With
    Set Sh = Nothing
 
End Sub

Je ne sais pas trop comment m'y prendre pour créer la ligne totale dynamiquement afin que cette ligne finale du tableau ne soit pas écrasée lors de l'ajout de nouvelles sections lors du clic sur le bouton.

Pourriez-vous m'aider svp ?

[MarcelG]

Salut,

La réponse est simple.
Utilise un tableau structuré en y affectant un total

Un bouton me permet d'ajouter une section supplémentaire au tableau grâce à du vb

Dans ce cas
Cette ligne de code

Code :

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

Worksheets(1).ListObjects("Tableau1").ListRows.Add

verra insérer une ligne juste avant le total

Dès que tu auras un nombre sur ce nouvel enregistrement, le Total s'en verra modifié

C'est maaagique.

[Pierre Fauconnier]

Salut.

Ton tableau n'est pas formé de manière à pouvoir le transformer en tableau structuré. Dans un tableau structuré, les lignes doivent avoir la même structure. Un tableau structuré est en fait "une boite avec des fiches dedans" (voir mon tuto).

Ici, ton tableau est un tableau est un tableau avec des insertions de sous-totaux "par blocs"... A part un process "VBA pur", je ne vois pas trop comment fonctionner. L'idée est de boucler sur les cellules de la deuxième colonne, et soit de calculer en VBA en ajoutant la valeur de la colonne 6 à une variable, soit de créer une plage avec UNION et les valeurs de la colonne 6 sur la ligne de total pour établir la somme par formule.

Voici un code qui permet cela, en mettant la valeur du total général

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Sub Test()
  Dim LastRow As Long
  Dim i As Long
  Dim rng As Range
 
  LastRow = Range("b1048576").End(xlUp).Row
  For i = 2 To LastRow
    If Range("b" & i).Value = "Total" Then
      If rng Is Nothing Then Set rng = Range("f" & i) Else Set rng = Union(rng, Range("f" & i))
    End If
  Next
  Range("b" & i) = "Total général"
  Range("f" & i).Value = Application.Sum(rng)
End Sub

Si tu veux placer une formule plutôt que la valeur calculée en VBA, tu peux remplacer la dernière ligne par celle-ci:

Code :

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

Range("f" & i).Formula = "=sum(" & rng.Address & ")"

[coeurdange]

Merci pour ton aide mais je ne comprends pas la ligne:

Code :

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

 LastRow = Range("b1048576").End(xlUp).Row

J'ai essayé cela mais ce la ne fonctionne pas. Pourrais-tu m'aider stp ?

[Pierre Fauconnier]

LastRow = Range("b1048576").End(xlUp).Row permet de retrouver la dernière ligne de la feuille sur laquelle il y a TOTAL en B (Au vu de ta copie d'écran, j'ai considéré que la colonne Name of the expert... était en colonne B. Tu dois bien entendu l'adapter à ton besoin.

Le problème rencontré ici, c'est que ton code est fortement lié à la position du tableau dans la feuille, puisque l'on rentre les lettres de colonnes en dur. Dans la mesure où l'on imagine que le tableau ne va pas être déplacé latéralement, ce n'est probablement pas trop grave. J'ai également considéré dans mon code que l'on commence à la ligne 2 comme première ligne de donnée du tableau. Là aussi, ce n'est pas top et tu dois adapter à ton cas. On pourrait (devrait) travailler avec des cellules nommées pour éviter ces problèmes.

Quoi qu'il en soit, ce que tu dois faire, c'est comprendre mon code pour l'adapter à ta situation et ton besoin. Mon code démarre à la première ligne du tableau, parcourt le tableau dans la colonne Name of the expert à la recherche du mot total et ajoute la cellule en F (dernière colonne du tableau) à une plage dont on calculera la somme des valeurs en fin de tableau, en ajoutant la ligne "total général". Normalement, on devra tester s'il y a déjà une ligne de total général pour ne pas en ajouter 2. Une bonne solution serait que cette ligne soit présente dès le début de ton tableau. Idéalement, elle sera aussi nommée de manière à pouvoir n'en modifier que la formule dans la dernière colonne.

Est-ce clair?

[MarcelG]

Salut Pierre,

J'ai été trop vite.
Je voulais signifier mon interrogation quant à une telle présentation des données.
Supposons qu'un item vienne s'ajouter dans un bloc, ce sera vite le b...azar.

Pour ma part, il serait préférable de dresser un tableau structuré par bloc, avec total, et d'établir un tableau structuré de synthèse, alimenté, lui, par procédure VBA

Si je considère un exemple simple



Même si les tableaux T_enfants et T_adultes évoluent, la gestion du tableau T_famille ne poserait pas de problème

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
Option Explicit
 
Public Sub Synth_tablostruc()
 
Dim tb As ListObject
Dim r As ListRow
 
With Worksheets(1)
        With .ListObjects("T_famille")
                If Not .DataBodyRange Is Nothing Then .DataBodyRange.Delete
        End With
        For Each tb In .ListObjects
                If tb.Name <> "T_famille" Then
                        Set r = .ListObjects("T_famille").ListRows.Add
                        With r
                                .Range(1, Worksheets(1).ListObjects("T_famille").ListColumns("Type de parenté").Index).Value = Replace(tb.Name, "T_", "")
                                .Range(1, Worksheets(1).ListObjects("T_famille").ListColumns("revenus_parenté").Index).Value = Range(tb.Name & "[[#Totals],[revenus]]").Value
                        End With
                        Set r = Nothing
                End If
        Next tb
End With
 
End Sub

A noter au passage, la formulation

Code :

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

Range("lenomdutabeaustructuré[[#Totals],[revenus]]").Value

Ce qui revient à une gestion assez souple, du moins pour mon avis

[coeurdange]

LastRow = Range("b1048576").End(xlUp).Row permet de retrouver la dernière ligne de la feuille sur laquelle il y a TOTAL en B (Au vu de ta copie d'écran, j'ai considéré que la colonne Name of the expert... était en colonne B. Tu dois bien entendu l'adapter à ton besoin.

Le problème rencontré ici, c'est que ton code est fortement lié à la position du tableau dans la feuille, puisque l'on rentre les lettres de colonnes en dur. Dans la mesure où l'on imagine que le tableau ne va pas être déplacé latéralement, ce n'est probablement pas trop grave. J'ai également considéré dans mon code que l'on commence à la ligne 2 comme première ligne de donnée du tableau. Là aussi, ce n'est pas top et tu dois adapter à ton cas. On pourrait (devrait) travailler avec des cellules nommées pour éviter ces problèmes.

Quoi qu'il en soit, ce que tu dois faire, c'est comprendre mon code pour l'adapter à ta situation et ton besoin. Mon code démarre à la première ligne du tableau, parcourt le tableau dans la colonne Name of the expert à la recherche du mot total et ajoute la cellule en F (dernière colonne du tableau) à une plage dont on calculera la somme des valeurs en fin de tableau, en ajoutant la ligne "total général". Normalement, on devra tester s'il y a déjà une ligne de total général pour ne pas en ajouter 2. Une bonne solution serait que cette ligne soit présente dès le début de ton tableau. Idéalement, elle sera aussi nommée de manière à pouvoir n'en modifier que la formule dans la dernière colonne.

Est-ce clair?

Merci pour les explications claires. Cependant j'optiens une erreur "Invalid procedure call argument" avec la ligne

Code :

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

Range("f" & i).Value = Application.Sum(rng)

Pourriez-vous m'aider avec cela ?

[coeurdange]

J'ai résolu mon problème gràce à ce code vba:

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
Sub AddANewPrivateExpert()
 
 
    Dim dl As Long
 
    With Sheets("Meeting Other Country").Range("PrivateExpertTable")
        dl = .Rows.Count
        .Rows(dl + 1 & ":" & dl + 4).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        .Rows(dl - 3 & ":" & dl).Copy Destination:=.Cells(dl + 1, 1)
        .Rows(dl + 1).Borders(xlEdgeTop).Weight = xlMedium
        .Cells(dl + 1, 1) = .Cells(dl - 3, 1) + 1
        .Range(.Cells(dl + 2, 4), .Cells(dl + 3, 4)).ClearContents
    End With
 
 
End Sub

[Pierre Fauconnier]

Ok, Marcel,

Mais ça ne correspond pas à la forme du tableau proposé au départ qui me pose problème, ceci dit... Ca ressemble à un "vieux" truc avec les outils de sous-total qui ne sont pas disponibles avec les tableaux structurés.

Perso, le tableau tel que présenté initialement ressemble plus à du reporting, qui n'est pas dans le périmètre Excel, selon moi. Ici, je verrai mieux quelque chose comme tu le présentes, mais alors, j'aimerais autant avoir tout dans le même tableau avec une colonne supplémentaire et me diriger vers un TCD. Tout dépendrait dès lors de la façon dont on veut présenter les données.

Dans ma réponse, j'ai essayé de coller au plus près de la présentation du tableau du demandeur