Créer une somme automatique en fonction d'un caractère

Le 02/12/2021 à 10:12

Bonjour,

Je travaille sur des fichiers qui me demande de vérifier ligne par ligne (10 000 lignes en moyenne) l'unité de conversion d'une cellule pour laquelle je dois modifier/calculer manuellement la somme à chaque fois pour l'ajuster au nouveau calcul. En + de modifier la cellule cela a un impact sur ma somme totale du produit ou alors parfois la bécane me sort des sommes en * que je dois calculer manuellement.

Pour identifier mes corrections j'ai une colonne avec une formule mais cela me demande de les filtrer puis de défiltrer et retrouver la ligne etc.

Je tente de trouver une solution pour gagner du temps pour que toutes mes lignes "Result" se calculent automatiquement (qu'elles fassent les sommes de leurs lignes si il y en a plusieurs ou celle du dessus quand y en a qu'une). J'ai cherché des solutions en me disant de créer une formule qui va additionner ou faire la somme des lignes précédentes jusqu'à ce qu'elle rencontre une nouvelle ligne "Result" mais je n'arrive pas à construire ma formule ou alors il faudrait passer par une VBA.

Pour m'aider à gagner du temps ; j'aimerai qu'en fait sur mes lignes "Result" elles fassent l'addition de toutes les lignes précédentes jusqu'à ce qu'elles rencontrent le précédent "Result" de manière automatique et surtout que quand je modifie quelque chose sur une ligne qu'elles puissent se calculer toute seule (les lignes "Result")

Je vous joins un exemple en fichier ci-joint, si quelqu'un peut m'aider ça serait cool.

Ou je recopie la formule ou je recopie la VBA en l'adaptant à mon tableau d'origine

Merci à vous !

Au plaisir

10vba-formule-result.xlsx (25.02 Ko)


Exemple :

Référence Désignation CA Qté

852123 Bouteille de gaz 5€ 2

852124 Bouteille d'eau 1€ 2

Result 6€ 4

852124 Oignon 0,5 € 10

Result 0,5€ 10

Le 02/12/2021 à 10:37

bonjour,

une proposition VBA

Option Explicit
Sub aargh()
    Dim dl As Long 'variable qui contiendra dernière ligne
    Dim i As Long ' variable qui contiendra le numéro de la ligne en cours
    Dim somme As Double ' variable qui contiendra la somme en cours
    Dim col As String 'variable qui contiendra l'identifiant de la colonne en cours

    With Sheets("Analyse des écarts - Cumul") ' feuille sur laquelle appliquer la macro
        col = "L" ' colonne sur laquelle appliquer la macro
        dl = .Cells(Rows.Count, col).End(xlUp).Row 'dernière ligne contenant des données en colonne col
        For i = 12 To dl 'on parcourt toutes les lignes de la ligne 12 à la dernière, le numéro de ligne en cours est dans i
            If .Cells(i, 1) = "Result" Then
            'si Result en colonne 1 (A) ligne i
                .Cells(i, col) = somme 'on écrit la somme calculée jusqu'à présent en ligne i colonne col
                somme = 0 'on remet la somme à 0
            Else
                'si pas Result en colonne 1
                somme = somme + .Cells(i, col) 'on ajoute le montant de la colonne col à la somme en cours
            End If
        Next i  'ligne suivante
        ' clôture l'indication de la feuille sur laquelle on travaille
    End With
End Sub
Le 02/12/2021 à 10:40

Bonjour à tous !

Une proposition en L13 (à recopier vers le bas) :

=SI(A13="Result";SOMME(DECALER($L$1;MAX(($A$11:A12="Result")*LIGNE($A$11:A12);1);;MAX(LIGNE()-1-MAX(($A$11:A12="Result")*LIGNE($A$11:A12);1);1)));"")

On peut utiliser la fonction LET pour améliorer la lisibilité et la maintenance de la formule proposée.
Mais nous sommes ignorants de la version Excel que vous utilisez (à renseigner dans votre profil !).

Le 02/12/2021 à 10:59

Trop bien H2s04 c'est exactement ce que je voulais ! Whoua ça va me sauver la vie ! Enfin gagner du temps

Comment puis-je faire la même chose mais sur mes autres colonnes ?

Je dois faire plusieurs macros pour chaque colonne ? ou est-ce que je dois mettre à la suite en changement uniquement col = "L" ?

Afin que je puisse l'adapter et comprendre la formule peux-tu me l'expliquer ?

Merci à vous deux !

J'utilise Excel en version française de Microsoft 365 version2102

image
Le 02/12/2021 à 11:12

re-bonjour,

Je dois faire plusieurs macros pour chaque colonne ? ou est-ce que je dois mettre à la suite en changement uniquement col = "L" ?

Si tu fais plusieurs macros, tu devras les lancer séparément ou prévoir une macro qui lance toutes ces macros.

Si tu fais une seule macro, toutes les colonnes seront mises à jour en lançant la macro. (ce n'est peut-être pas ce que tu souhaites)

A toi de choisir.

Le 02/12/2021 à 11:21

Si justement j'aimerai qu'en lançant la macro en une fois (je crée un bouton en affectant la macro) toutes les colonnes soient calculées.

Comment puis-je faire ? (j'ai mis ceci dans la macro mais cela ne fonctionne pas

col = "N" ' colonne sur laquelle appliquer la macro
col = "O"
col = "P"
col = "Q"
col = "R"
col = "S"
col = "T"
col = "U"
col = "V"
col = "W"
dl = .Cells(Rows.Count, col).End(xlUp).Row

Le 02/12/2021 à 11:31

re-bonjour,

voici une manière de faire

Option Explicit
Sub aargh()
    Dim dl As Long 'variable qui contiendra dernière ligne
    Dim i As Long ' variable qui contiendra le numéro de la ligne en cours
    Dim somme As Double ' variable qui contiendra la somme en cours
    Dim col As String 'variable qui contiendra l'identifiant de la colonne en cours
    Dim listecolonne As Variant 'variable qui contiendra la liste des colonnes à traiter
    Dim j As Long ' variable qui contiendra le numéro de la colonne (dans la liste) en cours de traitement

    With Sheets("Analyse des écarts - Cumul") ' feuille sur laquelle appliquer la macro
        listecolonne = Split("N,O,P,Q,R,S,T,V,W", ",") 'liste des colonnes sur lesquelles appliquer la macro

        For j = 1 To UBound(listecolonne) 'on prend les colonnes une à une
            col = listecolonne(j)
            dl = .Cells(Rows.Count, col).End(xlUp).Row 'dernière ligne contenant des données en colonne col

            For i = 12 To dl 'on parcourt toutes les lignes de la ligne 12 à la dernière, le numéro de ligne en cours est dans i
                If .Cells(i, 1) = "Result" Then
                    'si Result en colonne 1 (A) ligne i
                    .Cells(i, col) = somme 'on écrit la somme calculée jusqu'à présent en ligne i colonne col
                    somme = 0 'on remet la somme à 0
                Else
                    'si pas Result en colonne 1
                    somme = somme + .Cells(i, col) 'on ajoute le montant de la colonne col à la somme en cours
                End If
            Next i  'ligne suivante

        Next j
        ' clôture l'indication de la feuille sur laquelle on travaille
    End With
End Sub
Le 02/12/2021 à 11:49

J'ai recopié ton code en essayant de comprendre ce à quoi cela correspond dans mon fichier que je souhaite intégrer.

Les colonnes que je souhaite sont N,O,P,Q,R,S,T,V,W et la colonne où se trouve le caractère "Result" correspond à la 10ème colonne dans mon tableau.

Mais cela ne fonctionne pas Qu'ai-je fait de pas correct ? (je n'arrive pas à recopier comme toi la formule dans le post )

image
Le 02/12/2021 à 12:04

Bonjour tout le monde,

h2so4 t'avait mis : listecolonne = Split("N,O,P,Q,R,S,T,V,W", ",")

et tu as enlevé le , ",") qui indique le délimiteur. Si tu changes cela, ça devrait fonctionner.

Le 02/12/2021 à 12:07

Merci

J'ai modifié mais cette fois-ci il me calcule que la colonne O et il se mets à planter

Le 02/12/2021 à 12:25

Regarde le fichier ci-joint C'est le fichier de base

Et dans mon fichier "origine" où je veux transposer cette macro ; il ne me calcule que la colonne O...et se mets à planter

13vba-formule-result.xlsm (34.04 Ko)
Le 02/12/2021 à 12:32

re-bonjour

au temps pour moi, ... voici une version corrigée

Sub aargh()
    Dim dl As Long 'variable qui contiendra dernière ligne
    Dim i As Long ' variable qui contiendra le numéro de la ligne en cours
    Dim somme As Double ' variable qui contiendra la somme en cours
    Dim col As String 'variable qui contiendra l'identifiant de la colonne en cours
    Dim listecolonne As Variant 'variable qui contiendra la liste des colonnes à traiter
    Dim j As Long ' variable qui contiendra le numéro de la colonne (dans la liste) en cours de traitement

    With Sheets("Analyse des écarts - Cumul") ' feuille sur laquelle appliquer la macro
        listecolonne = Split("N,O,P,Q,R,S,T,U,V,W", ",") 'liste des colonnes sur lesquelles appliquer la macro

        For j = LBound(listecolonne) To UBound(listecolonne) 'on prend les colonnes une à une
            col = listecolonne(j)
            somme = 0
            dl = .Cells(Rows.Count, col).End(xlUp).Row 'dernière ligne contenant des données en colonne col

            For i = 12 To dl 'on parcourt toutes les lignes de la ligne 12 à la dernière, le numéro de ligne en cours est dans i
                If .Cells(i, 10) = "Result" Then
                    'si Result en colonne 1 (A) ligne i
                    .Cells(i, col) = somme 'on écrit la somme calculée jusqu'à présent en ligne i colonne col
                    somme = 0 'on remet la somme à 0
                Else
                    'si pas Result en colonne 1
                    somme = somme + .Cells(i, col) 'on ajoute le montant de la colonne col à la somme en cours
                End If
            Next i  'ligne suivante

        Next j
        ' clôture l'indication de la feuille sur laquelle on travaille
    End With
End Sub
Le 02/12/2021 à 12:48

Merci beaucoup ! Ca semble bien marché !

Dernière petite demande, pour les lignes où elles sont vides, est-il possible de laisser vide au lieu de 0 ?

image
Le 02/12/2021 à 12:53

Mince, je te faisais une confiance aveugle, je n'avais pas regardé le reste

h2so4 a écrit :

re-bonjour

au temps pour moi, ... voici une version corrigée

Le 02/12/2021 à 12:59

rebonjour,

code adapté plus correction bug somme sur dernière ligne

Sub aargh()
    Dim dl As Long 'variable qui contiendra dernière ligne
    Dim i As Long ' variable qui contiendra le numéro de la ligne en cours
    Dim somme As Double ' variable qui contiendra la somme en cours
    Dim col As String 'variable qui contiendra l'identifiant de la colonne en cours
    Dim listecolonne As Variant 'variable qui contiendra la liste des colonnes à traiter
    Dim j As Long ' variable qui contiendra le numéro de la colonne (dans la liste) en cours de traitement

    With Sheets("Analyse des écarts - Cumul") ' feuille sur laquelle appliquer la macro
        listecolonne = Split("N,O,P,Q,R,S,T,U,V,W", ",") 'liste des colonnes sur lesquelles appliquer la macro
        dl = .Cells(Rows.Count, 10).End(xlUp).Row 'dernière ligne contenant des données en colonne 10
        For j = LBound(listecolonne) To UBound(listecolonne) 'on prend les colonnes une à une
            col = listecolonne(j)
            somme = 0

            For i = 12 To dl 'on parcourt toutes les lignes de la ligne 12 à la dernière, le numéro de ligne en cours est dans i
                If .Cells(i, 10) = "Result" Then
                    'si Result en colonne 1 (A) ligne i
                    If somme = 0 Then
                        .Cells(i, col) = ""
                    Else
                        .Cells(i, col) = somme 'on écrit la somme calculée jusqu'à présent en ligne i colonne col
                    End If
                    somme = 0 'on remet la somme à 0
                Else
                    'si pas Result en colonne 1
                    somme = somme + .Cells(i, col) 'on ajoute le montant de la colonne col à la somme en cours
                End If
            Next i  'ligne suivante

        Next j
        ' clôture l'indication de la feuille sur laquelle on travaille
    End With
End Sub
Le 02/12/2021 à 13:03

H2s04 : Grâce à ta correction TOUT FONCTIONNE à merveille ! Whoua ! Je viens de gagner un précieux temps à toi !

MERCI BEAUCOUP ! Top du top!

Merci à tous d'avoir participé

Le 03/12/2021 à 11:39

Re-Bonjour !

J'avais une question pour compléter la macro ; dans mon tableau sur les lignes à additionner j'ai des * à cause de mon extractions ; du coup ma macro s'arrête et bug. Est-il possible de faire quelque chose ?

Car à part filtrer mes colonnes à la recherche d'une étoile * et de la supprimer manuellement je n'ai pas trouvé comment faire autrement.

Autrement la macro fonctionne très bien.

Merci beaucoup

Le 03/12/2021 à 12:19

bonjour,

(non testé)

Sub aargh()
    Dim dl As Long 'variable qui contiendra dernière ligne
    Dim i As Long ' variable qui contiendra le numéro de la ligne en cours
    Dim somme As Double ' variable qui contiendra la somme en cours
    Dim col As String 'variable qui contiendra l'identifiant de la colonne en cours
    Dim listecolonne As Variant 'variable qui contiendra la liste des colonnes à traiter
    Dim j As Long ' variable qui contiendra le numéro de la colonne (dans la liste) en cours de traitement

    With Sheets("Analyse des écarts - Cumul") ' feuille sur laquelle appliquer la macro
        listecolonne = Split("N,O,P,Q,R,S,T,U,V,W", ",") 'liste des colonnes sur lesquelles appliquer la macro
        dl = .Cells(Rows.Count, 10).End(xlUp).Row 'dernière ligne contenant des données en colonne 10
        For j = LBound(listecolonne) To UBound(listecolonne) 'on prend les colonnes une à une
            col = listecolonne(j)
            somme = 0

            For i = 12 To dl 'on parcourt toutes les lignes de la ligne 12 à la dernière, le numéro de ligne en cours est dans i
                If .Cells(i, 10) = "Result" Then
                    'si Result en colonne 1 (A) ligne i
                    If somme = 0 Then
                        .Cells(i, col) = ""
                    Else
                        .Cells(i, col) = somme 'on écrit la somme calculée jusqu'à présent en ligne i colonne col
                    End If
                    somme = 0 'on remet la somme à 0
                Else
                    'si pas Result en colonne 1
                    On Error Resume Next
                    somme = somme + .Cells(i, col) 'on ajoute le montant de la colonne col à la somme en cours
                    On Error GoTo 0
                End If
            Next i  'ligne suivante

        Next j
        ' clôture l'indication de la feuille sur laquelle on travaille
    End With
End Sub
Le 03/12/2021 à 13:45

Merci h2so4 ! Cela fonctionne très bien !

Rechercher des sujets similaires à "creer somme automatique fonction caractere"