XL 2010 Créer ListObject + Ligne Total

[cathodique]

Bonjour,

Je transforme une plage en tableau structuré en utilisant le bout de code ci-dessous.
Mais je rencontre une difficulté (en fait 2):
1- Pourquoi lors de l'activation de la ligne 'Totals', le mot 'Total' ne s'affiche pas en 1ère colonne
2- Aucun résultat ne s'affiche dans la ligne total

With ShBilan
' Définir la plage pour le ListObject
      Set Rng = .Range("A" & dl + 1).Resize(UBound(Tall), UBound(Tall, 2))
      ' Créer le ListObject
      .ListObjects.Add(xlSrcRange, Rng, , xlYes).Name = "Tall"
      .ListObjects("Tall").TableStyle = "TableStyleLight16"
      .ListObjects("Tall").ShowAutoFilter = False
      ' Activer la ligne Totaux
      .ListObjects("Tall").ShowTotals = True
      ' Ajouter des formules de somme dans la ligne Totaux pour chaque colonne
      For i = 2 To .ListObjects("Tall").ListColumns.Count
         .ListObjects("Tall").ListColumns(i).TotalsCalculation = xlTotalsSum
      Next i
End With

En vous remerciant par avance.

Bon week-end.

 

[job75]

Bonjour cathodique,

A la fin remplace la boucle par :

With .ListObjects("Tall").Range
    For i = 2 To .Columns.Count
        .Cells(.Rows.Count, i) = "=SUBTOTAL(109," & "[" & .Cells(1, i) & "])"
    Next i
End With

A+

 

[cathodique]

Bonjour cathodique,

A la fin remplace la boucle par :

With .ListObjects("Tall").Range
    For i = 2 To .Columns.Count
        .Cells(.Rows.Count, i) = "=SUBTOTAL(109," & "[" & .Cells(1, i) & "])"
    Next i
End With

A+

Bonjour @job75 ,

Merci beaucoup. Mais le code plante, erreur 1004: Erreur définie par l'application ou par l'objet

Bonne journée.

 

[job75]

Bah le code que j'ai donné fonctionne très bien chez moi, exécute la macro du fichier joint.

 

[job75]

Ah oui dans ton fichier il y a des apostrophes dans les titres, il faut les doubler :

      With .ListObjects("Tall").Range
         For i = 2 To .Columns.Count
            .Cells(.Rows.Count, i) = "=SUBTOTAL(109," & "[" & Replace(.Cells(1, i), "'", "''") & "])"
         Next i
      End With

 

[cathodique]

Merci beaucoup. En fait, le fichier joint n'est pas mon fichier de travail.
Je pense m'en sortir avec ce que tu m'as envoyé.
Si je coule, je reviendrais.

Encore merci.
Bonne journée.

 

[cathodique]

Ah oui dans ton fichier il y a des apostrophes dans les titres, il faut les doubler :

      With .ListObjects("Tall").Range
         For i = 2 To .Columns.Count
            .Cells(.Rows.Count, i) = "=SUBTOTAL(109," & "[" & Replace(.Cells(1, i), "'", "''") & "])"
         Next i
      End With

Stp, comment t'es-tu rendu compte qu'il y avait des apostrophes dans les titres.

Tout le tableau est obtenu par code. Je ne vois pas les apostrophes.
Pour infos, les titres de lignes et de colonnes ont été extraites de 2 dictionnaires.
Est-ce la cause de la présence de ces apostrophes?

En tout cas, chapeau ton code fonctionne parfaitement.

 

[job75]

Tout le tableau est obtenu par code. Je ne vois pas les apostrophes.

Ils sont en C3 et F3 après le d...

 

[cathodique]

Ils sont en C3 et F3 après le d...

J'ai mal compris. J'ai cru que les titres étaient encadrés par des apostrophes non visibles.
Merci.

 

[TooFatBoy]

Bonjour,

1- Pourquoi lors de l'activation de la ligne 'Totals', le mot 'Total' ne s'affiche pas en 1ère colonne

Bizarre : chez moi il s'affiche.

2- Aucun résultat ne s'affiche dans la ligne total

Bizarre : chez moi il y a un total qui s'affiche, mais seulement pour la dernière colonne.


La ligne des totaux que tu ajoutes en fin de TS est capable de calculer toute seule la somme d'une colonne, donc inutile d'y mettre une formule, autant laisser Excel déterminer ladite formule. 😉

Je te propose donc ceci :

Sub MonTbS()
'
    With shBilan

        ' Définir la plage pour le ListObject
        Set Rng = .Range("A3:J6")

        ' Créer le Tableau Structuré
        .ListObjects.Add(xlSrcRange, Rng, , xlYes).Name = "Tall"
        .ListObjects("Tall").TableStyle = "TableStyleLight16"
        .ListObjects("Tall").ShowAutoFilter = False

        ' Activer la ligne Totaux
        .ListObjects("Tall").ShowTotals = True

        ' Écrire "TOTAUX" dans la colonne "Espèce"
        Range("Tall[[#Totals],[Espèce]]").Value = "TOTAUX"

        ' Mettre un total dans la ligne des totaux, de la colonne "Entrée" à la colonne "Décès"
        For Each Cellule In Range("Tall[[#Headers],[Entrée]:[Décès]]")
            Range("Tall[#Totals]").ListObject.ListColumns(Cellule.Value).TotalsCalculation = xlTotalsCalculationSum
        Next Cellule

   End With

End Sub

 

[Staple1600]

Bonsoir le fil

@cathodique
Une variante syntaxique (avec contrainte à la fin (*) )

Sub Essai_A()
With Feuil1.ListObjects.Add(xlSrcRange, Feuil1.Range("A3:J6"), , xlYes)
    .Name = "Tall"
    .TableStyle = "TableStyleLight16"
    .ShowAutoFilter = 0: .ShowTotals = -1
    .TotalsRowRange.FillRight 'Dommage !
    .TotalsRowRange.Cells(1).Value = "TOTAUX"
End With
End Sub

(*)

Spoiler

qui oblige à jouer de la souris
Pas trouvé de syntaxe VBA pour éviter la boucle comme le fait @TooFatBoy

EDITION: Avec une boucle pour éviter le désagrément indiqué dans le SPOILER

Sub Essai_B()
With Feuil3.ListObjects.Add(xlSrcRange, Feuil3.Range("A3:J6"), , xlYes)
    .Name = "Tall": .ShowAutoFilter = 0: .ShowTotals = -1
    c = .ListColumns.Count
    For i = 1 To c
        If Application.WorksheetFunction.IsNumber(.ListColumns(i).DataBodyRange(1)) Then
            .ListColumns(i).TotalsCalculation = xlTotalsCalculationSum
        End If
    Next i
    .TotalsRowRange(1).Value = "TOTAUX": .TableStyle ="TableStyleLight16"
End With
End Sub

 

[job75]

Bonjour cathodique,

Puisque la formule existe en J7, avec AutoFill on évite la boucle :

Sub MonTbS()
    With ShBilan
        ' Définir la plage pour le ListObject
        Set Rng = .Range("A3:J6")
        ' Créer le ListObject
        .ListObjects.Add(xlSrcRange, Rng, , xlYes).Name = "Tall"
        .ListObjects("Tall").TableStyle = "TableStyleLight16"
        .ListObjects("Tall").ShowAutoFilter = False
        ' Activer la ligne Totaux
        .ListObjects("Tall").ShowTotals = True
        ' Ajouter des formules de somme dans la ligne Totaux pour chaque colonne
        With .ListObjects("Tall").Range
            .Cells(.Rows.Count, .Columns.Count).AutoFill .Cells(.Rows.Count, 2).Resize(, .Columns.Count - 1), xlFillValues
        End With
    End With
End Sub

Nota : le calcul était en mode manuel, je l'ai passé en automatique.

A+

 

[cathodique]

Messieurs Bonjour, @job75 😉, @TooFatBoy 😉, @Staple1600 😉,

Excusez mon manque de réactivité.
Je suis un peu dépassé. Je testerai chacune de vos propositions et reviendrai aux nouvelles.
Connaissance vos compétences, je sais que le résultat sera au RDV.

Merci beaucoup.

Bonne fin de dimanche.

Edit: tout est ok. Messieurs, je vous remercie.

 

[patricktoulon]

bonjour
quand on arrive à la formile et l'autofill c'est un ts alors utilisez les noms

Sub MonTbS()
    With ShBilan
        ' Définir la plage pour le ListObject
        Set Rng = .Range("A3:J6")
        ' Créer le ListObject
        .ListObjects.Add(xlSrcRange, Rng, , xlYes).Name = "Tall"
        .ListObjects("Tall").TableStyle = "TableStyleLight16"
        .ListObjects("Tall").ShowAutoFilter = False
        ' Activer la ligne Totaux
        .ListObjects("Tall").ShowTotals = True
        ' Ajouter des formules de somme dans la ligne Totaux pour chaque colonne
        With Range("Tall[[#Totals],[Entrée]]")
            .FormulaR1C1 = "=SUM(R[-3]C:R[-1]C)"
            .AutoFill Destination:=Range("Tall[[#Totals],[Entrée]:[Décès]]"), Type:=xlFillDefault
        End With
   End With
   End Sub

 

[TooFatBoy]

J'avais cherché une syntaxe pour mettre toutes les sommes sur la lignes des totaux, sans utiliser de boucle, mais n'en trouvant pas je m'étais résolu à utiliser une boucle. 🙁

En utilisant la solution de job75 donnée en #12 n'utilisant pas de boucle, ma proposition deviendrait celle-ci :

Sub MonTbS()
'
    With shBilan

        ' Définir la plage pour le ListObject
        Set Rng = .Range("A3:J6")

        ' Créer le Tableau Structuré
        .ListObjects.Add(xlSrcRange, Rng, , xlYes).Name = "Tall"
        .ListObjects("Tall").TableStyle = "TableStyleLight16"
        .ListObjects("Tall").ShowAutoFilter = False

        ' Activer la ligne Totaux
        .ListObjects("Tall").ShowTotals = True

        ' Écrire "TOTAUX" dans la colonne "Espèce"
        Range("Tall[[#Totals],[Espèce]]").Value = "TOTAUX"

        ' Mettre un total dans la ligne des totaux de la colonne "Entrée"
        .ListObjects("Tall").ListColumns("Entrée").TotalsCalculation = xlTotalsCalculationSum
        ' Recopier la cellule de totaux de la colonne "Entrée", de la colonne "Entrée" à la colonne "Décès"
        Range("Tall[[#Totals],[Entrée]]").AutoFill Range("Tall[[#Totals],[Entrée]:[Décès]]"), xlFillValues

   End With

End Sub