Discussion :

[Julien_59]

Bonjour à tous,

N'ayant que des connaissances plutôt basique en VBA je bloque sur un petit problème et je me permets de solliciter votre aide. Notre ERP a des petits problèmes de paramétrage et les lors de nos extractions les formules ne sont pas les bonnes (il y a des décalages dans les plages de données sélectionnées...) ! En attendant je souhaiterai retraiter ces formules à l'aide d'une macro.

Mon problème consiste à réaliser à l'aide d'une macro une succession de formule "Somme". Voici comment mon fichier se présente :


J'ai une cinquantaine de fichier standardisés à retraiter, la seule différence est que j'ai plus ou moins de charges dans les sous catégories (mais les sous catégories restent les mêmes d'un fichier à l'autre).

Ce que je veux faire :
C'est que dés qu'il trouve "Sous catégorie 1", il fasse la somme de la ligne d'en dessous jusqu'a la ligne au dessus de "Sous catégorie 2". Et que dés qu'il trouve "Total Groupe 1", il fasse la somme des "Sous catégorie 1 2 et 3".

Bien entendu j'ai plus de sous catégorie et de groupe. Mais je pense qu'avec ça je pourrai facilement me débrouiller pour l'adapter à mon fichier.

Par avance un grand merci pour votre aide

Bien cordialement & à bientot !

[sogedic]

Une formule + un tableau croisé dynamique te conviendrais ?

[Julien_59]

Merci pour cette première réponse rapide,

Alors en fait plusieurs problèmes suite à cette proposition :
=> La première est que les "Sous catégories" ont des noms bien spécifiques, totalement différent des unes aux autres mais ces noms sont standardisés d'un fichier à l'autre.
=> La deuxième est qu'avec une telle solution, je devrais créer plus de 50x une nouvelle colonne, une formule (potentiellement à adapter à chaque fichier), et créer un TCD (pour chacun des fichiers). Alors qu'avec une macro je pourrai cliquer sur un bouton et la lancer sur tous les fichiers .xls du répertoire (vu que tout est standardisé - ça je sais faire ).
=> De plus cette demande me permettra en fonction des totaux obtenus de retravailler directement manuellement la base pour réaliser des ajustements et obtenir les résultats souhaités, ce qui est embêtant avec un TCD qui devra être actualisé.

C'est pourquoi une macro me simplifierai grandement la vie mais je ne vois pas du tout comment m'y prendre. Est-ce que cela te parait compliqué à mettre en place ?

Par avance merci,

Bien cdt

[sogedic]

Il me semble que tu devrais nous proposer un début de macros que nous t'aiderions à finir …
Le but du forum est d'apprendre a savoir faire … soi-même

[Pierre Fauconnier]

[…]=> La première est que les "Sous catégories" ont des noms bien spécifiques, totalement différent des unes aux autres mais ces noms sont standardisés d'un fichier à l'autre.[…]

Si je comprends bien, les intitulés Sous-Catégorie 1 et autres de ton illustration ne sont pas ceux que tu as réellement? On va devoir jouer à Madame Soleil pour connaître les bons?

En supposant une structure telle que tu l'illustres (la somme des cellules en dessous jusqu'à la catégorie suivante et la dernière cellule faisant le total général), on pourrait tenter la chose suivante, illustrée sur base du tableau suivant

Code :

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

1
2
3
4
5
6
7
8
9
10
11
Sub Test()
  Dim formulaRange As Range
  Dim Counter As Long
  Dim ValueRange As Range
 
  Set formulaRange = Range("b:b").SpecialCells(xlCellTypeFormulas) ' Liste des cellules formulées de B:B
  For Counter = 1 To formulaRange.Areas.Count - 1 ' Boucle sur chaque zone de FormulaRange sauf la dernière, contenant chacune une seule cellule
    Set ValueRange = Range(formulaRange.Areas(Counter)(2), formulaRange.Areas(Counter + 1)(0)) ' Récupération de la plage des données sous la formule
    formulaRange.Areas(Counter).Formula = "=sum(" & ValueRange.Address & ")" ' Recréation de la formule avec l'adresse de la plage de données
  Next
End Sub

Mais si tu dois réaliser cela pour chaque extraction de ton erp => change de erp ou zigouille le programmeur qui t'a développé les extractions (Plus sérieusement, à terme, il faut faire corriger les extractions aux frais de la boite qui a développé le ERP et/ou les extractions)

[Julien_59]

@ Pierre Fauconnier
- Alors je ne peux pas extraire de tables de données brutes, je ne peux que modéliser des tableaux de bord pour réaliser des extractions, et c'est très limités (Business Object me manque...)
- Pour moi les noms des catégories ne sont pas importants. C'est pour ça qu'ils sont fictifs dans mon exemple, tout comme les charges qui y sont associées bien évidemment. Je mettrai les noms corrects dans la macro.

@ Sogedic
Généralement je fais des macros très basiques avec l’assistant, et je modifie le code pour l'adapter à mes besoins.
Mais dans ce cas je ne sais même pas par ou commencer, d'où ma question, est-ce que c'est facilement réalisable ?

La seule chose dont je suis capable c'est de vous expliquer clairement ce que je veux faire pour avoir un début de solution sur ce petit exemple, et après je pourrai l'adapter à mon fichier.

Comment je vois la chose :
- On se place en L2C2, on descend jusqu’à trouver : "Sous catégorie 1" (imaginons en L4C2)
- Une fois que c'est trouvé je veux qu'il fasse la somme dans L4C3 des charges liés à "Sous catégorie 1", c'est à dire des lignes comprises en la "Sous catégorie 1" et la "Sous catégorie 2"
- Et ainsi de suite, On se place en L2C2, on descend jusqu'a trouver :"Sous catégorie 2","Sous catégorie 3" etc...
- Et quand il trouve "Total groupe 1", je veux la somme des sous catégorie en L"X"C3

Je vais essayer de rechercher sur des tutos un début de solution

Bien cdt,

[Pierre Fauconnier]

Salut.

[coup de gueule]
Ce qui serait chouette, c'est que les développeurs de solutions ERP ne se mêlent pas de faire de l'Excel puisqu'ils n'y connaissent souvent rien, mais se contentent, lors des extractions, de proposer des "tables à plat" pouvant être analysées grâce aux outils natifs d'Excel…
[/coup de gueule]

Ne peux-tu pas extraire les données brutes de ton ERP et les traiter toi-même dans Excel?

[Pierre Fauconnier]

La solution que je donne crée uniquement les totaux par catégorie. S'il faut également réaliser le total général, ma proc peut s'adapter assez facilement.

S'il faut réaliser cela sur plusieurs fichiers, on insère cette proc dans une boucle qui traite chaque fichier.

Je suppose que la première sous-catégorie n'est pas en B2, et on devra dès lors déterminer comment on détermine la colonne à traiter. A mon avis, on devrait faire cela au sein d'un classeur dans lequel on définit les types d'extraction et la (les) colonne(s) à traiter par type d'extraction…

PS: J'ai parfois des envies de meurtre… (pas sur toi, hein…)

[RyuAutodidacte]

Bonjour,

Pour commencer :
1- y'a t-il plusieurs groupes sur une même feuille ?
2- y'a t-il plusieurs feuilles d'un même classeur contenants des groupes ?
3- est ce que plusieurs groupes peuvent se retrouver sur la même colonne

PS : plus on a de détails mieux c'est …

si des détails il y a en plus des questions déjà posées, elles sont les bienvenues …

Edit : est ce que dans la structure on aura bien à chaque fois :

Groupe X
Sous catégorie x
…
…
…
Sous catégorie x
…
…
Sous catégorie x
…
…
Total Groupe X

[Julien_59]

@Pierre Fauconnier,
Je vais commencer a faire des tests avec ton début de solution
Je joins également mon fichier pour que vous ayez un visuel sur ce que je souhaite faire

@RyuAutodidacte
1- y'a t-il plusieurs groupes sur une même feuille ? Oui, j'ai au total 8 groupes
2- y'a t-il plusieurs feuilles d'un même classeur contenants des groupes ? Je n'ai qu'une feuille par classeur (nom de la feuille identique à tous les fichiers)
3- est ce que plusieurs groupes peuvent se retrouver sur la même colonne Tous les groupes, toutes les sous catégories sont dans la même colonne

Edit : est ce que dans la structure on aura bien à chaque fois (ton exemple ci-dessus) : Oui c'est exactement ça

Pour en revenir au fichier ci-joint (il n'y a pas tous les groupes, j'ai allégé le fichier), c'est la colonne "L" qui est à retraiter. Pour info la colonne A est vide et très réduite (fait automatiquement lors de l'export : pourquoi ? aucune idée). Dans tous les fichiers le point de départ peut être défini en L17 (première catégorie), jusque là tout est standardisé, après seul le nombre de ligne varie en fonction des comptes concernés, donc la position des autres catégories varient d'un fichier à l'autre.

[Pierre Fauconnier]

Si tous les groupes sont dans la même colonne, il faudra arriver à identifier les ruptures de groupes (par exemple avec des cellules vides).

Dès lors, la macro que je propose devrait circonscrire sa zone de traitement à un groupe, pour exclure toujours la dernière formule qui calcule le total du groupe, et elle serait appelée par la macro qui scannerait les groupes…

PS: Je n'ai pas encore regardé ton fichier

[sogedic]

Une piste, les fichiers a lire doivent être dans un sous repertoire "export" du repertoire ou tu enregistreras le fichier
… a adapter

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
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
 
Option Explicit
 
Sub lirefichiers()
 
    Dim Fichier As String, Chemin As String
    Dim wb As Workbook
 
    Chemin = ThisWorkbook.Path & "\Export\*.xls*"
    Fichier = Dir(Chemin & "*.xls")
 
    Do While Fichier <> ""
 
        Set wb = Workbooks.Open(Chemin & Fichier)
 
        calculSom wb
 
        wb.Close savechanges:=True
        Set wb = Nothing
    'Fichier suivant
    Fichier = Dir
    Loop
 
 
End Sub
 
Sub testcal()
    Dim wb As Workbook
    Set wb = ThisWorkbook
    calculSom wb
End Sub
 
Sub calculSom(wb As Workbook)
    Dim nbLg As Long, tabVal As Variant, i As Long
    Dim tabRes As Variant
    Dim somT As Double, som As Double
    Dim isom As Long
 
    With ThisWorkbook.Sheets(1)
        nbLg = .Cells(Application.Rows.Count, 2).End(xlUp).Row
        tabVal = .Range("B1:L" & nbLg).Value
        ReDim tabRes(1 To nbLg, 1 To 1) ' n lignes, 1 colonne
 
        'Calculs
        For i = 17 To nbLg
            If tabVal(i, 1) = "" Then Exit For
 
            If tabVal(i, 1) Like "*TOTAL*" Then
                tabRes(i - 16, 1) = somT
                somT = 0
                '--
                tabRes(isom - 16, 1) = som
                isom = i + 1
                som = 0
                i = i + 1
                GoTo next_i
            End If
 
            If Not tabVal(i, 1) Like "*N*COMPTE*" Then
                If i > 17 Then
                    tabRes(isom - 16, 1) = som
                End If
                isom = i
                som = 0
            Else
                tabRes(i - 16, 1) = tabVal(i, 11)          'L
                som = som + CDbl(tabVal(i, 11))
                somT = somT + CDbl(tabVal(i, 11))
            End If
 
 
 
next_i:
        Next i
        tabRes(isom, 1) = som
 
        'memoire
         Set tabVal = Nothing
 
        'Resultats
        '.Range("L17:L" & nbLg).Value = tabRes
        .Range("N17:N" & nbLg).Value = tabRes ' pour test
        Set tabRes = Nothing
    End With
 
End Sub

Bien cordialement … aussi

[RyuAutodidacte]

Re,

Ta structure finale de tes fichiers n'est pas exactement le reflet de la capture d'écran qui est une représentation schématique/grossière.

Ce qui change la façon de coder, puisque l'on doit prendre en compte l'agencement des données.

Pas le temps de coder qq ch là, je verrais ce soir …

[Pierre Fauconnier]

Une question…

Dans l'extraction, les formules existent au niveau des lignes de somme mais sont fausses, ou bien tu as uniquement une valeur numérique?

[sogedic]

Bonsoir

J'ai modifie et testé mon code avec ton fichier d'exemple
ca marche … sous reserve d'avoir en colonne B des reperes
- TOTAL
- N° comptes
- les autres lignes sont considérees comme des totaux de N°Comptes
donc
si dans les lignes N°comptes il y a directement 641 il faut remplacer le code like "N°" par like "*6*"

[RyuAutodidacte]

Bonjour,

J'ai modifie et testé mon code avec ton fichier d'exemple
ca marche … sous reserve d'avoir en colonne B des reperes
- TOTAL
- N° comptes
- les autres lignes sont considérees comme des totaux de N°Comptes

J'ai bien peur qu'à ce niveau là les fausses données ne soit pas de type fixe et que N° COMPTE soit juste une représentation, ex. :

N° COMPTE - Libellé Compte

AZ-456X20J- Nom du compte Tartampion

Il faut espérer que "GROUPE X … TOTAL GROUPE X" etc … , la mise en forme couleur, le bold et le reste soit OK

L'idéal serait que le décalage soit identique sur tous les classeurs, il suffirait alors de chopper les formules et d'y appliquer le décalage inverses dans celles-ci.

Dans le cas où le décalage se fait de façon aléatoire, il faut espérer que les repères en colonne B soit OK.
Je me suis servi de la couleur en autre (j'aurais pu utiliser le texte en Bold à la place entre les 2 bornes début et fin =>) comme repère et de "GROUPE X … TOTAL GROUPE X" etc … pour le début et fin :

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
Sub FormulaSum()
Dim AllGpe, Grp, TA, DL&, DF_G As Boolean, VA, i&, ColGroupe As New Collection, Col_G, Vgpe, NomGpe$, Groupe$, Som$, SomTotal$
 
    Grp = Array("GROUPE", "TOTAL GROUPE")
    TA = Array("TA", "TOTAL TA")
    AllGpe = Array(Grp, TA)
 
    DL = Cells(Rows.Count, "B").End(xlUp).Row:      DF_G = False
    With ActiveSheet.Range(Cells(1), Cells(DL, "M"))
        For Each AG In AllGpe
            VA = .Columns.Item(2).Value
            For i = 16 To UBound(VA)
                If VA(i, 1) > "" Then
                    If VA(i, 1) Like AG(Abs(DF_G)) & "*" Then
                        If DF_G = False Then
                            NomGpe = VA(i, 1)
                            ColGroupe.Add VA(i, 1), NomGpe
                            DF_G = True
                        Else: DF_G = False
                            Groupe = ColGroupe(NomGpe) & " _ " & .Cells(i, 12).Address
                            ColGroupe.Remove NomGpe:    ColGroupe.Add Groupe, NomGpe
                        End If
                    ElseIf DF_G = True Then
                        If Cells(i, 2).Interior.Color <> RGB(255, 255, 255) Then
                            Groupe = ColGroupe(NomGpe) & " _ " & .Cells(i, 12).Address
                            ColGroupe.Remove NomGpe:    ColGroupe.Add Groupe, NomGpe
                        End If
                    End If
                End If
            Next
            Application.ScreenUpdating = False
                For Each Col_G In ColGroupe
                    Vgpe = Split(Col_G, " _ ")
                    For i = 1 To UBound(Vgpe) - 1
                        .Range(Vgpe(i)).FormulaLocal = "=Somme(" & .Range(Vgpe(i)).Offset(1).Address & ":" & .Range(Vgpe(i + 1)).Offset(-1).Address & ")"
                        Som = Som & "+" & Vgpe(i)
                    Next
                        .Range(Vgpe(UBound(Vgpe))).FormulaLocal = "=" & Mid(Som, 2)
                        SomTotal = SomTotal & "+" & Vgpe(UBound(Vgpe))
                        Som = ""
                Next
                .Cells(DL, 12).FormulaLocal = "=" & Mid(SomTotal, 2)
            Application.ScreenUpdating = True
            Set ColGroupe = Nothing
        Next
    End With
End Sub

PS : Le code concernant l'ouverture et la fermeture des fichiers est donné par @sogedic
@Julien_59 : infos : on peut trouver se code dans la F.a.q. comme bien d'autres codes ou explications de code vba (ex : les boucles) … (C'est sympas de la part de @sogedic d'avoir donné le code tout fait pour l'ouverture et la fermeture des fichiers );
faire des recherches est important (Faq, net, …), comme proposé un code dans sa demande, même si celui-ci vient de l'enregistreur de macro

Edit : Modification de texte

[sogedic]

Moi qui m'attendais à un merci,
Bref

[RyuAutodidacte]

Edit : bon tu as modifié ton post ci-dessus

Moi qui m'attendais à un merci

Ce n'est pas moi qui doit de te donner un remerciement, mais c'est au demandeur de le faire …
PS : j'ai commencer mon code vendredi soir (cf post #14), puis par à-coup Samedi puis fin de matinée aujourd'hui … comme toi j'y ai passé du temps aussi
(je ne suis pas là pour faire un concours, je propose une solution parmi d'autres)

Sache qu'en général il est préférable que le demandeur vienne faire sa demande en montrant le code qu'il a fait (même si celui-ci vient de l'enregistreur de macro),
et qu'à partir de celui-ci on lui donne de l'aide sur les points bloquants