Somme - besoin d'une macro

[Julien.B]

Bonjour a tous

j'ai une base de données qui me donne un fichier xls avec uniquement des valeurs.
Sur certaines lignes, j'ai des sommes intermédiaires en valeur, puis des sommes de ces sommes à la fin, en valeur toujours. Le fichier ne contient que des valeurs, aucune formule. Le fichier répète ces blocs de données pour tous les produits (intitulé ZZ)
Je cherche en fait a grouper les lignes blanches pour ne faire apparaitre que les lignes jaunes et vertes, et l'intitulé des colonnes.

je pensais faire un groupement automatique avec les sous-totaux, mais pour cela il faudrait que je puisse mettre des sommes de maniere automatique sur les lignes où il y a l'intitulé somme.

Quelqu'un pourrait-il m'aider, a moins que vous ne pensiez à une autre solution.

merci d'avance

Julien

 

[Gorfael]

Re : Somme - besoin d'une macro

Salut Julien et le forum
Je n'utilise pas les couleurs : une différence, invisible à l'oeil, l'est pour excel. Je me suis basé sur les sommes en colonne E :

Sub Test()
'Déclaration ===========================
Dim Cel As Range
Dim X As Long, Tablo() As Long
Dim Str_1 As String
'initialisation ========================
ReDim Tablo(0)
Tablo(0) = Range("E1").End(xlDown).Row - 1
'recherche des sommes ==================
Set Cel = Range("E:E").Find("Somme")
Do
    ReDim Preserve Tablo(UBound(Tablo) + 1)
    Tablo(UBound(Tablo)) = Cel.Row
    Set Cel = Range("E:E").FindNext(Cel)
    If Cel.Row = Tablo(1) And UBound(Tablo) > 1 Then Exit Do
Loop
If UBound(Tablo) > 1 And Tablo(UBound(Tablo)) = Tablo(UBound(Tablo) - 1) + 1 Then
    For X = 1 To UBound(Tablo) - 1
        Str_1 = Str_1 & Tablo(X) & "; F"
    Next X
    Set Cel = Range("F" & Tablo(UBound(Tablo)))
    Cel.FormulaLocal = "=somme(F" & Left(Str_1, Len(Str_1) - 3) & ")"
    Cel.AutoFill Destination:=Range(Cel, Cells(Cel.Row, [A1].SpecialCells(xlCellTypeLastCell).Column))
End If
For X = 1 To UBound(Tablo) - 1
    Set Cel = Range("F" & Tablo(X))
    Cel.FormulaLocal = "=somme(F" & Tablo(X - 1) + 1 & ":F" & Tablo(X) - 1 & ")"
    Cel.AutoFill Destination:=Range(Cel, Cells(Cel.Row, [A1].SpecialCells(xlCellTypeLastCell).Column))
Next X
End Sub

ça fonctionne sur le bout de fichier donné : ça fait la somme des lignes au-dessus. la dernière somme fait l'addition des lignes contenant le mot Somme en E (ce qui n'est pas le cas pour le fichier d'essai)
A+

 

[mth]

Re : Somme - besoin d'une macro

Bonjour Julien.B

Pour ne faire apparaître que les totaux, peut-être le filtre automatique peut-il t'aider?

Tu sélectionnes la ligne 3 puis tu vas dans le menu Données Filtre/Filtre automatique.
Une fois que tu vois apparaître les petites flèches dans le coin inférieur droit de tes cellules de titre, tu cliques sur celle qui est en E3, et dans la liste qui s'affiche tu sélectionnes Somme
Seules les lignes de totaux apparaîtrons.

Pour réafficher tout, tu recliques sur la même petite flèche, et là tu choisis (Tous)

Une image pour que tu voies les menus :




Bonne journée à toi,

mth

Edit: Bonjour Gorfael je m'incline là

 

[Julien.B]

Re : Somme - besoin d'une macro

Merci Gorfael pour ce coup de main

ca fonctionne tres bien sur ce fichier, je vais tester sur le fichier original

MTH, merci pour ta réponse. j'y avais pensé mais en fait je ne souhaite pas cacher les lignes, juste les grouper de manière a faire apparaitre le détail des sommes qui m'interesse

merci a vous deux

bonne journée

 

[JHA]

Re : Somme - besoin d'une macro

Bonjour Julien.B, le forum,

une autre solution avec sommeprod()
en F19

=SOMMEPROD(($E4:$E18="Somme :")*(F4:F18))
tirer vers la droite

JHA

 

[Julien.B]

Re : Somme - besoin d'une macro

Salut Julien et le forum
Je n'utilise pas les couleurs : une différence, invisible à l'oeil, l'est pour excel. Je me suis basé sur les sommes en colonne E :

Sub Test()
'Déclaration ===========================
Dim Cel As Range
Dim X As Long, Tablo() As Long
Dim Str_1 As String
'initialisation ========================
ReDim Tablo(0)
Tablo(0) = Range("E1").End(xlDown).Row - 1
'recherche des sommes ==================
Set Cel = Range("E:E").Find("Somme")
Do
    ReDim Preserve Tablo(UBound(Tablo) + 1)
    Tablo(UBound(Tablo)) = Cel.Row
    Set Cel = Range("E:E").FindNext(Cel)
    If Cel.Row = Tablo(1) And UBound(Tablo) > 1 Then Exit Do
Loop
If UBound(Tablo) > 1 And Tablo(UBound(Tablo)) = Tablo(UBound(Tablo) - 1) + 1 Then
    For X = 1 To UBound(Tablo) - 1
        Str_1 = Str_1 & Tablo(X) & "; F"
    Next X
    Set Cel = Range("F" & Tablo(UBound(Tablo)))
    Cel.FormulaLocal = "=somme(F" & Left(Str_1, Len(Str_1) - 3) & ")"
    Cel.AutoFill Destination:=Range(Cel, Cells(Cel.Row, [A1].SpecialCells(xlCellTypeLastCell).Column))
End If
For X = 1 To UBound(Tablo) - 1
    Set Cel = Range("F" & Tablo(X))
    Cel.FormulaLocal = "=somme(F" & Tablo(X - 1) + 1 & ":F" & Tablo(X) - 1 & ")"
    Cel.AutoFill Destination:=Range(Cel, Cells(Cel.Row, [A1].SpecialCells(xlCellTypeLastCell).Column))
Next X
End Sub

ça fonctionne sur le bout de fichier donné : ça fait la somme des lignes au-dessus. la dernière somme fait l'addition des lignes contenant le mot Somme en E (ce qui n'est pas le cas pour le fichier d'essai)
A+

Gorfael

merci encore pour ton aide.
mais malheureusement lorsque j'applique cette macro au fichier original elle bug.
Mon fichier comporte 1700 lignes, constituées de centaines de blocs comme celui que j'ai joint.
Mais, lorsque je lance la macro,
il semble que cela bug sur la ligne suivante
Cel.FormulaLocal = "=Somme(F" & Left(Str_1, Len(Str_1) - 3) & ")"

aurais-tu une idée?

merci d'avance

Julien

 

[LapinVert]

Re : Somme - besoin d'une macro

Formule à mettre en F13 => =SOUS.TOTAL(9;F4:F12)
Formule à mettre en F18 => =SOUS.TOTAL(9;F14:F17)
Formule à mettre en F19 => =SOUS.TOTAL(9;F4:F18)
Puis copier ces formules tout le long des lignes 13, 18 et 19.
OK pour toi ?

 

[liloucmoi]

Re : Somme - besoin d'une macro

Hello tous,

il semble que cela bug sur la ligne suivante
Cel.FormulaLocal = "=Somme(F" & Left(Str_1, Len(Str_1) - 3) & ")"

formulaLocal, c'est la formule dans la langue d'utilisation, si tu as un excel en francais, il ne connnait pas Left par exemple.

Essaye de changer par ceci :

Cel.Formula = "=Somme(F" & Left(Str_1, Len(Str_1) - 3) & ")"

Xldment.

Lilou

PS : Bonjour LapinVert, une petite formule de politesse en ouverture et fermeture de message, ca ne fait pas de mal.