[XL-2003] Ajout d'une formule Sum [Résolu]

[korni184]

Bonjour,

Pouvez-vous m'aider à ajouter à mon moteur une formule "Sum" dans mon tableau excel ?

Tableau excel
A1 : USD12500
A2 : USD5236,12
A3 : USD4122,56
A4 : USD7852,45
A5 : USD4525,41
A6 : EUR7894,00
A7 : EUR1234,10
A8 : EUR4561,10
A9 : EUR4512,52
A10 : EUR1241,
A11 : GBP4512,
A12 : GBP4872
A13: GBP4512

Dans la colonne B : le montant seul
Dans la colonne C : La devise Seul

Donc j'ai fait en sorte qu'il y est deux sauts de lignes lorsque la devise de la ligne est différente de la devise sur la ligne précédente.

Je voudrais ajouter sur la première ligne blanche en A6 logiquement : le terme Total en colonne B la somme des différentes sommes, et en colonne C la devise de la somme.

Est-ce que cela est possible ? Bien sur, je voudrai une somme et non pas un cumul. Car si je dois retirer un chiffre de mon tableau, je voudrais que les totaux s'ajustent.

Merci d'avance pour votre aide.

Bien à vous

Code :

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
 
Sub Macro1() 
 
' Macro recorded 16/11/2011
' Keyboard Shortcut: Ctrl+a 
 
'Nomination des variables 
 Dim Montant As String 
 Dim Devise As String 
 Dim DevisePrec As String 
 Dim Cpt As String 
 
'Suppression des espaces blancs 
 Columns("A:A").Select 
 Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _ 
 SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ 
 ReplaceFormat:=False 
 
'Démarrage du compteur 
 Cpt = 1 
 
'Positionnement de départ 
 Range("a1").Select 
 DevisePrec = Mid(Range("A" & Cpt), 1, 3) 
 
'Démarrage de la boucle 
 Do While ActiveCell.Value <> "" 
 
'Positionnement du montant dans la colonne A 
 Montant = Replace(Mid(Range("A" & Cpt), 4, Len(Range("A" & Cpt).Value) - 4), ",", ".") 
 
'Positionnement de la devise dans la colonne A 
 Devise = Mid(Range("A" & Cpt), 1, 3) 
 
'Ajout du montant dans la colonne B 
 Range("B" & Cpt).Value = Val(Montant) 
 
'Ajout de la devise dans la colonne C 
 Range("C" & Cpt).Value = Devise 
 
  If Cpt > 1 Then 
 
    If Devise <> DevisePrec Then 
 
      Selection.EntireRow.Insert 
      Selection.EntireRow.Insert 
      ActiveCell.Offset(1, 0).Select 
      Cpt = Cpt + 1 
      DevisePrec = Devise 
 
     End If 
 
  End If 
 
'Saut de ligne 
 ActiveCell.Offset(1, 0).Select 
 
'Fin du compteur 
 Cpt = Cpt + 1 
 
 Loop 
 
'Ajout des décimales colonne B 
 Range("B:B").Select 
 Selection.NumberFormat = "0.00" 
 Columns("B:B").EntireColumn.AutoFit 
 
End Sub

[ZebreLoup]

De même que tu stockes DevisePrec, tu pourrais stocker la dernière position avant changement de devise, pour ensuite faire un

Code :

ActiveCell.Formula = "=SUM(B" & LastCpt & ":B" & Cpt & ")"

Bien sûr c'est une exemple, je te laisse vérifier que ce n'est pas LastCpt + 1 ou - 1, de même pour Cpt pour tomber sur le bon range.

Et j'ai repris ta manière de coder. Pour ma part, j'évite absolument les ActiveCell. Je bouclerais plutôt sur un Cells(Cpt, 2) ou Range("A1").Offset(Cpt, 0)

[korni184]

Sujet apars,

Lorsque j'ajoute ma variable

Code :

Dim LastCpt as string"

Ou est-ce que je peux démarrer ce compteur et ou dois-je inserer dans ma boucle ceci ?

Code :

ActiveCell.Formula = "=SUM(B" & LastCpt & ":B" & Cpt & ")"

J'ai fait comme ceci :

Cependant il affiche dans chaque cellule ou doit se trouvé les totaux :

B0:B...
Et il reprend toujours B0

Code :

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
 
' Macro recorded 16/11/2011
' Keyboard Shortcut: Ctrl+a 
 
'Nomination des variables 
 Dim Montant As String 
 Dim Devise As String 
 Dim DevisePrec As String 
 Dim Cpt As String 
 Dim LastCpt as string
 
'Suppression des espaces blancs 
 Columns("A:A").Select 
 Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _ 
 SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ 
 ReplaceFormat:=False 
 
'Démarrage du compteur 
 Cpt = 1 
 LastCpt = 0
 
'Positionnement de départ 
 Range("a1").Select 
 DevisePrec = Mid(Range("A" & Cpt), 1, 3) 
 
'Démarrage de la boucle 
 Do While ActiveCell.Value <> "" 
 
'Positionnement du montant dans la colonne A 
 Montant = Replace(Mid(Range("A" & Cpt), 4, Len(Range("A" & Cpt).Value) - 4), ",", ".") 
 
'Positionnement de la devise dans la colonne A 
 Devise = Mid(Range("A" & Cpt), 1, 3) 
 
'Ajout du montant dans la colonne B 
 Range("B" & Cpt).Value = Val(Montant) 
 
'Ajout de la devise dans la colonne C 
 Range("C" & Cpt).Value = Devise 
 
  If Cpt > 1 Then 
 
    If Devise <> DevisePrec Then 
 
      Selection.EntireRow.Insert 
      Selection.EntireRow.Insert 
      ActiveCell.Offset(1, 0).Select 
      ActiveCell.Formula = "=SUM(B" & LastCpt & ":B" & Cpt & ")"
      Cpt = Cpt + 1 
      DevisePrec = Devise 
 
     End If 
 
  End If 
 
'Saut de ligne 
 ActiveCell.Offset(1, 0).Select 
 
'Fin du compteur 
 Cpt = Cpt + 1 
 
 Loop 
 
'Ajout des décimales colonne B 
 Range("B:B").Select 
 Selection.NumberFormat = "0.00" 
 Columns("B:B").EntireColumn.AutoFit 
 
End Sub

[ZebreLoup]

Alors d'abord Cpt et LastCpt sont des Integer et pas des String.

Sinon tu as oublié de faire LastCpt = Cpt (ou Cpt + 1, Cpt - 1, ... je te laisse voir ce qui colle) au moment du DevisePrec = Devise

Comme ça ça marche :

Code :

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
' Macro recorded 16/11/2011
' Keyboard Shortcut: Ctrl+a
 
'Nomination des variables
 Dim Montant As String
 Dim Devise As String
 Dim DevisePrec As String
 Dim Cpt As Integer
 Dim LastCpt As Integer
 
'Suppression des espaces blancs
 Columns("A:A").Select
 Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
 SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
 ReplaceFormat:=False
 
'Démarrage du compteur
 Cpt = 1
 LastCpt = 1
 
'Positionnement de départ
 Range("a1").Select
 DevisePrec = Mid(Range("A" & Cpt), 1, 3)
 
'Démarrage de la boucle
 Do While ActiveCell.Value <> ""
 
'Positionnement du montant dans la colonne A
 Montant = Replace(Mid(Range("A" & Cpt), 4, Len(Range("A" & Cpt).Value) - 4), ",", ".")
 
'Positionnement de la devise dans la colonne A
 Devise = Mid(Range("A" & Cpt), 1, 3)
 
'Ajout du montant dans la colonne B
 Range("B" & Cpt).Value = Val(Montant)
 
'Ajout de la devise dans la colonne C
 Range("C" & Cpt).Value = Devise
 
  If Cpt > 1 Then
 
    If Devise <> DevisePrec Then
 
      Selection.EntireRow.Insert
      Selection.EntireRow.Insert
      ActiveCell.Offset(1, 0).Select
      ActiveCell.Formula = "=SUM(B" & LastCpt & ":B" & Cpt - 1 & ")"
      Cpt = Cpt + 1
      DevisePrec = Devise
        LastCpt = Cpt + 1
     End If
 
  End If
 
'Saut de ligne
 ActiveCell.Offset(1, 0).Select
 
'Fin du compteur
 Cpt = Cpt + 1
 
 Loop
 
'Ajout des décimales colonne B
 Range("B:B").Select
 Selection.NumberFormat = "0.00"
 Columns("B:B").EntireColumn.AutoFit

[korni184]

Bonjour,

Merci pour vos informations précieuses.

J'ai deux petites dernières questions en rapport avec ce même et unique sujet.

Pourquoi me conseillez vous ceci ? Quelle est la différence ?

Et j'ai repris ta manière de coder. Pour ma part, j'évite absolument les ActiveCell. Je bouclerais plutôt sur un Cells(Cpt, 2) ou Range("A1").Offset(Cpt, 0)

Concernant le moteur actuel si je veux que le résultat de ma somme s'affiche en colonne B et non en colonne A ? Comment dois-je faire ? Et pour le programme saute une ligne après et non avant ?

Merci d'avance

Bonne journée

[ZebreLoup]

En fait, c'est plus propre de travailler directement sur les objets feuilles, classeurs, cellules que de les sélectionner puis de travailler sur l'objet actif. D'une part, c'est plus rapide et d'autre part ça réduit considérablement la possibilité d'erreur. Surtout que si la procédure n'est pas immédiate et que l'utilisateur clique sur quelque chose pendant son exécution, tout plantera.

Quand on débute, on utilise beaucoup l'enregistreur de macro. Ce qui est une bonne chose pour découvrir les fonctions et objets disponibles. Mais il faut quand même lire je pense un ou deux tutos ou livres sur vba pour ensuite adapter ce qu'a sorti l'enregistreur de macro pour le transformer en vrai "code".

Après, vous me direz, tant que ça marche, qu'est-ce que ça peut faire ? Ce n'est pas faux, mais c'est surement une bonne habitude à prendre pour si un jour on se lance dans un programme plus complexe.

Après toutes ces belles paroles, je t'ai refait un code plus "propre" à mon sens, certains pourront surement l'améliorer encore.
J'ai essayé de beaucoup le commenter pour que tu comprennes exactement tout ce qui se passe.
Normalement, tu n'auras besoin de personne pour changer la colonne ou la ligne d'affiche de ta somme, ajouter 3 lignes au lieu de 2... (Pour le coup, je t'ai quand même mis la somme sur la première ligne vide et dans la colonne B)

J'en ai profité pour utiliser une sous-procédure avec passage par référence. Ce n'était pas essentiel ici, mais si tu as le courage, ça t'apprendra quelque chose qui peut s'avérer très pratique.

Code :

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
 
Option Explicit 'Oblige à déclarer les variables, réduit le risque d'erreur
 
Public Sub Test()
    'Déclaration des variables
    'On essaie d'utiliser des noms parlant pour s'y retrouver plus facilement
    Dim montant As Double 'C'est un montant et pas une chaine de caractère
    Dim devise As String
 
    'Les variables de position et de mémoire
    Dim devisePrecedente As String 'La dernière devise avant changement
    Dim ligneActuelle As Integer 'La ligne en cours sur laquelle on va boucler
    Dim ligneChangeDevise As Integer 'La première ligne du dernier changement de devise
 
    'On déclare la feuille sur laquelle on travaille pour que ça ne plante pas si on lance
    'la macro alors qu'on est sur une autre feuille
    Dim ws As Worksheet
    Set ws = Worksheets("Feuil1")
 
    'Suppression des espaces blancs
    ws.Range("A:A").Replace What:=" ", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
 
    'Initialisation des variables
    ligneActuelle = 1
 
    'Décomposition de la première ligne, les variables montant et devise prennent la bonne valeur
    DecomposeMontantDevise ws.Cells(ligneActuelle, 1).Value, montant, devise
    ws.Cells(ligneActuelle, 2).Value = montant 'Remplissage du montant en colonne 2
    ws.Cells(ligneActuelle, 3).Value = devise 'Remplissage de la devise en colonne 3
 
    devisePrecedente = devise
    ligneChangeDevise = 1 'On a notre première devise
 
    ligneActuelle = ligneActuelle + 1 'passage à la ligne suivante
 
    'Ensuite on continue tant qu'on n'a pas de cellule vide
    Do While ws.Cells(ligneActuelle, 1).Value <> ""
        'On récupère notre nouveau montant et nouvelle devise
        DecomposeMontantDevise ws.Cells(ligneActuelle, 1).Value, montant, devise
        ws.Cells(ligneActuelle, 2).Value = montant 'Remplissage du montant en colonne 2
        ws.Cells(ligneActuelle, 3).Value = devise 'Remplissage de la devise en colonne 3
 
        'Si il y a changement de devise
        If devise <> devisePrecedente Then
            'On insère les deux lignes blanches
            ws.Rows(ligneActuelle).EntireRow.Insert
            ws.Rows(ligneActuelle).EntireRow.Insert
            'On insère la formule sur la première ligne blanche et la deuxième colonne
            ws.Cells(ligneActuelle, 2).Formula = "=SUM(B" & ligneChangeDevise & ":B" & ligneActuelle - 1 & ")"
            'On indique le changement de devise
            ligneChangeDevise = ligneActuelle + 2 'on a ajouté deux lignes vide
            devisePrecedente = devise
            'On se place avant la nouvelle ligne
            ligneActuelle = ligneActuelle + 2
        End If
 
        ligneActuelle = ligneActuelle + 1 'passage à la ligne suivante
    Loop
 
    'Dernière somme
    ws.Cells(ligneActuelle, 2).Formula = "=SUM(B" & ligneChangeDevise & ":B" & ligneActuelle - 1 & ")"
 
    'Ajout des décimales colonne B et autofit en largeur
    ws.Range("B:B").NumberFormat = "#0.00"
    ws.Columns("B:B").EntireColumn.AutoFit
 
End Sub
 
'C'est une tâche à part entière, il peut être intéressant d'en faire une procédure séparée
'montant et devise sont des paramètres par référence, ils vont donc être modifiés par cette procédure dans
'la procédure appelante
Private Sub DecomposeMontantDevise(ByVal texte As String, ByRef montant As Double, ByRef devise As String)
    'Récupération du montant
    montant = Val(Replace(Mid(texte, 4, Len(texte) - 3), ",", "."))
 
    'Récupération de la devise
    devise = Mid(texte, 1, 3)
End Sub

[korni184]

Merci pour cette réponse de très haute qualité.

Je vais cliquer sur résolu, mais pouvez-vous me dire comment obtenir les deux décimales sur un montant ?

Code :

montant = Val(Replace(Mid(texte, 4, Len(texte) - 3), ",", "."))

Si un montant est USD2500, automatiquement il le transforme en 2500,00 USD. Idem pour USD2500,1 --> 2500,10 USD.

Cependant les doubles décimales (exemple USD2500,15) deviennent automatiquement 2500,10. Il ne fait aucun arrondi, il supprime la deuxième décimale et ajoute un 0.

Comment remedié à cela ?

J'ai déjà essayé avec des "selection.numberformat = "#0.00".. mais cela ne fonctionne pas

[ZebreLoup]

Il faut bien distinguer le nombre qu'on récupère et sa mise en forme. Je n'ai pas vérifié, mais il est possible qu'on tronque systématiquement le dernier chiffre quand on récupère le montant :

Code :

montant = Val(Replace(Mid(texte, 4, Len(texte) - 3), ",", "."))

Peut-être que le -3 doit être un -2 ?
Vérifie en mode pas en pas en mettant un espion sur montant que tout les nombres ont bien été récupérés.

Le format n'intervient qu'au moment de l'affichage.

[korni184]

merci !!!