CODE VBA pour mise en forme et calculs

**titilex** · 19/07/2019, 12h12

Bonjour à tous,

Voila je vous expose mon nouveau problème pour lequel mes capacités de codage sont limitées.
Je cherche à faire une mise en page automatique à partir d'un appui bouton. Lorsque je clique sur le bouton, toute la mise en forme se réalise automatiquement.
Le contexte :
Le but, c'est de pouvoir faire un état de stock à la demande, sans faire de calcul d'apothicaire.
Je me suis crée un fichier qui me fait une compilation de l'ensemble des dépôts en appelant les données de plusieurs fichier.
Dans mon fichier de travail, j'ai crée une page DATA où toutes mes données se compilent dans l'ordre d'ouverture des fichiers sources.
Dans ma page DATA, j'ai une liste avec des désignations. Certaines désignations apparaissent plusieurs fois dans la page, en face j'ai un état de stock quoi qu'il arrive.

1) Opération N°1, faire un tri par ordre alphabétique.
Pour cette partie c'est bon.

2) Opération N°2, surligner les lignes par groupe, alternativement en bleu, puis en jaune.
Par exemple, si sur ma 1ere ligne, j'ai une désignation, la ligne complète est surlignée en bleu. Puis sur les lignes 2 à 5 une autre désignation, ces 4 lignes sont surlignées en jaune.
Et ainsi de suite jusqu'à la dernière ligne où il y a quelque chose d'écrit.
La subtilité, c'est qu'il peut y avoir plusieurs fois la même référence, même en premier poste.
Il y a une sous opération qui consiste à surligner toute la colonne H en noir. (cette partie facile, il n'y a pas de variable).

3) Opération N°3, la partie calcul. La mon but c'est d'avoir un total par sous-groupes surlignés.
En gros si j'ai une référence sur la 1ere ligne, il faut faire la somme de la ligne de la colonne C à G. Dans ce cas de figure de C1 à G1.
Si j'ai une référence sur les 3eres lignes, le calcul devra se faire de C1 à G3.
Et ainsi de suite jusqu'au traitement de tout les sous-groupes.

Si ma requête n'est pas claire, n’hésitez pas à me faire vos remarques.

Merci d'avance pour votre aide.

**Tylert** · 22/07/2019, 11h40

Pour l'opération n°2, il faut faire les choses suivantes:

Pour chaque ligne:
Si la ligne précédente à la même désignation que celle traitée: colorier avec la couleur courante.
Sinon, changer la couleur courante, et colorier la ligne avec la couleur courante.

Ce qui se traduit comme suit (Non optimisé pour Excel et non testé):

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
 
Dim CouleurCourante as integer
Dim cptLignes as integer
Dim DesignationPrecedente as string
 
 
'les valeurs de couleurs sont fixées arbitrairement, a toi de trouver ce qui correspond au jaune ou au bleu
CouleurCourante = RGB(255, 0, 0)
'J''ai considéré que la désignation se trouvait dans la colonne A
DesignationPrecedente = Range("A1").value
 
i = 1
While range("A" & i).value <> "" do 
   if DesignationPrecedente <> Range("A" & i).value then
      DesignationPrecedente = Range("A" & i).value
      if CouleurCourante = RGB(255, 0, 0) then
         CouleurCourante = RGB(0, 0, 255)
      else
         CouleurCourante = RGB(255, 0, 0)
      end if
   end if
   row(i).Interior.Color = CouleurCourante
   i = i + 1
Wend

Pour la partie 3, tu peux le faire dans la même boucle en retenant le numéro de la ligne ou tu as la 1ere fois une nouvelle valeur et en sommant la valeur de la colonne concernée à une variable de somme tant qu'on ne change pas de désignation. Si il y a un changement de désignation, tu ecris le résultat la ou tu en as besoin et tu reinitialise ta variable de somme avec la valeur de la colonne concernée.

Easy

**titilex** · 22/07/2019, 14h31

Salut Tylert,

Merci pour ton retour. J'ai intégré ton code dans mon fichier et j'ai une erreur de compilation qui ressort.
J'ai bien observé ton code, mais je n'y comprends malheureusement pas grand chose.
J'ai compris le principe de ce que tu voulais faire. Mais je ne sais pas l’interpréter en code.

**Tylert** · 22/07/2019, 15h09

le code compilé sans erreur:

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
 
Dim CouleurCourante As Integer
Dim cptLignes As Integer
Dim DesignationPrecedente As String
Dim i As Integer
 
'les valeurs de couleurs sont fixées arbitrairement, a toi de trouver ce qui correspond au jaune ou au bleu
CouleurCourante = RGB(255, 0, 0)
'J'ai considéré que la désignation se trouvait dans la colonne A
DesignationPrecedente = Range("A1").Value
 
i = 1
While Range("A" & i).Value <> ""
'Si la valeur de la designation n'est pas la même que celle de la ligne précédente, alors on la change et on retient la nouvelle designation pour la prochaine ittération   
If DesignationPrecedente <> Range("A" & i).Value Then
      'on retient la valeur de la nouvelle désignation
      DesignationPrecedente = Range("A" & i).Value
'Si on était jaune, on passe bleu pour la couleur a utiliser pour colorier
      If CouleurCourante = RGB(255, 0, 0) Then
         CouleurCourante = RGB(0, 0, 255)
      Else
'sinon, on passe jaune
         CouleurCourante = RGB(255, 0, 0)
      End If
   End If
'On colorie la ligne
   Rows(i).Interior.Color = CouleurCourante
' on passe à la ligne suivante
   i = i + 1
Wend

Ca m'apprendra de coder en direct dans ce forum sans passer par VBA

**titilex** · 23/07/2019, 10h57

Salut Tylert,

Bon le code bloque encore.
Je me suis crée un fichier vierge avec une liste aléatoire afin de travailler que sur cette partie de code.
Là ça bloque sur la ligne 20 de ton code.
J'ai une ''Erreur d’exécution 6 : Dépassement de capacité''.
J'ai la prmière ligne qui se colore, mais pas la suite.
Par ailleurs, j'apprécie tes explications pas à pas, cela me permet de comprendre comment avance le code.

De plus il y a un truc que je ne comprends pas dans les déclarations :

Code :

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

Dim cptLignes As Integer

Je ne le vois pas apparaître plus loin dans le code.
J'ai joins mon fichier exemple.

Merci d'avance pour ton retour.
Alex

**Tylert** · 23/07/2019, 16h15

Sans avoir vraiment regardé:

Je pense que la valeur retournée par RGB(0,0,255) est trop grande pour un integer, donc passe en long pour le type de couleurCourante.

Pour l'indice non utilisé, ca devrait remplacer l'indice i. En codant j'ai cru que j'avais oublié de définir mon indice et j'ai créé la variable i alors que j'avais déjà cptLignes.

**titilex** · 24/07/2019, 09h23

C'est quoi la différence entre Integer et Long ?

**Tylert** · 24/07/2019, 09h41

En gros quand tu type une variable en integer, tu lui alloues une certaine capacité de stockage, même chose en long, sauf que long peut stocker de plus grand nombres.

Integer data type (Visual Basic)
‎Holds signed 32-bit (4-byte) integers that range in value from -2,147,483,648 through 2,147,483,647.

Long data type (Visual Basic)
Holds signed 64-bit (8-byte) integers ranging in value from -9,223,372,036,854,775,808 through 9,223,372,036,854,775,807 (9.2...E+18).

Comme ton erreur c'était un depassement de capacité sur l'assignation d'une valeur à une variable, j'ai pensé qu'il fallait changer le type de la variable pour agrandir sa capacité.

**titilex** · 24/07/2019, 09h43

En tout cas, c'est nickel, ça marche parfaitement !

Maintenant la patie un peu moins drôle, le calcul

Pour la partie 3, tu peux le faire dans la même boucle en retenant le numéro de la ligne ou tu as la 1ere fois une nouvelle valeur et en sommant la valeur de la colonne concernée à une variable de somme tant qu'on ne change pas de désignation. Si il y a un changement de désignation, tu ecris le résultat la ou tu en as besoin et tu reinitialise ta variable de somme avec la valeur de la colonne concernée.
Easy

Je ne sais pas comment faire pour réaliser cette partie là.

**Menhir** · 24/07/2019, 09h53

Envoyé par titilex

C'est quoi la différence entre Integer et Long ?

Peut-être pourrais-tu faire l'effort de lire l'aide VBA

:
https://docs.microsoft.com/fr-fr/off...eger-data-type
https://docs.microsoft.com/fr-fr/off...long-data-type

**titilex** · 24/07/2019, 10h27

Salut Menhir,

On a pas toujours les bons réflexes.
Perso cela fait tout juste 5 mois que je me suis mis sur excel. Donc je découvre petit à petit.

J'apprends vite, et j'apprends mieux lorsque j'ai des explications '' personnalisées''.

Je vais lire tes liens.

**titilex** · 24/07/2019, 10h51

En gros quand tu type une variable en integer, tu lui alloues une certaine capacité de stockage, même chose en long, sauf que long peut stocker de plus grand nombres.

Integer data type (Visual Basic)
‎Holds signed 32-bit (4-byte) integers that range in value from -2,147,483,648 through 2,147,483,647.

Long data type (Visual Basic)
Holds signed 64-bit (8-byte) integers ranging in value from -9,223,372,036,854,775,808 through 9,223,372,036,854,775,807 (9.2...E+18).

Comme ton erreur c'était un depassement de capacité sur l'assignation d'une valeur à une variable, j'ai pensé qu'il fallait changer le type de la variable pour agrandir sa capacité.

Merci pour l'explication, on peut donc considérer les variables Long et Integer comme une boite (ou un tirroir) avec plus ou moins de capacité.

**Tylert** · 24/07/2019, 11h23

Code mis à jour pour la partie 3:

A noter que j'ai décidé arbitrairement qu'on stockait la somme pour une désignation en colonne H sur la 1ere ligne de la désignation.
La somme est typée variant. Pas sur que le stockage de nombre décimaux dans une variable de ce type fonctionne parfaitement...
Je n'ai pas testé
Un spécialiste d'Excel Optimiserait le code pour utiliser des fonctions Excel (Ici je recode tout pratiquement).

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
'Variable pour retenir en quelle couleur je dois colorier ma ligne
Dim CouleurCourante As Long
'compteur pour avancer dans les lignes du tableau
Dim cptLignes As Integer
'Stockage de la désignation précédente pour savoir si on a changé de désignation ou pas quand on change de ligne
Dim DesignationPrecedente As String
'Indice de la 1ere ligne dans laquelle on a trouvé une désignation pour se souvenir ou il faut stocker la somme
Dim IndLigneStockage As Integer
'Variable de stockage de la somme pour une désignation
Dim SommeDesignation As Variant
 
 
'les valeurs de couleurs sont fixées arbitrairement, a toi de trouver ce qui correspond au jaune ou au bleu
CouleurCourante = RGB(255, 0, 0)
'J'ai considéré que la désignation se trouvait dans la colonne A
DesignationPrecedente = Range("A1").Value
'on intialise la somme à 0 et l'indice de stockage pour la 1ere ligne
SommeDesignation = 0
IndLigneStockage = 1
 
cptLignes = 1
While Range("A" & cptLignes).Value <> ""
    'Si la valeur de la designation n'est pas la même que celle de la ligne précédente, alors on la change et on retient la nouvelle designation pour la prochaine ittération
    If DesignationPrecedente <> Range("A" & cptLignes).Value Then
        'on retient la valeur de la nouvelle désignation
        DesignationPrecedente = Range("A" & cptLignes).Value
        'Si on était jaune, on passe bleu pour la couleur a utiliser pour colorier
        If CouleurCourante = RGB(255, 0, 0) Then
            CouleurCourante = RGB(0, 0, 255)
        Else
        'sinon, on passe jaune
            CouleurCourante = RGB(255, 0, 0)
        End If
 
        'On doit aussi dans ce cas ajouter la valeur des colonnes C à G à la somme pour la désignation
        SommeDesignation = SommeDesignation + Range("C" & cptLignes) + Range("D" & cptLignes) + Range("E" & cptLignes) + Range("F" & cptLignes) + Range("G" & cptLignes)
 
    'Si on a pas la même valeur de désignation, on stocke la somme pour la désignation sur la ligne pour laquelle on a trouvé la 1ere désignation (IndLigneStockage) et on initialise indlignestockage et sommeDesignation pour la nouvelle désignation
    Else
        Range("H" & IndLigneStockage).Value = SommeDesignation
        SommeDesignation = Range("C" & cptLignes) + Range("D" & cptLignes) + Range("E" & cptLignes) + Range("F" & cptLignes) + Range("G" & cptLignes)
        IndLigneStockage = cptLignes
    End If
 
    'On colorie la ligne
    Rows(cptLignes).Interior.Color = CouleurCourante
    ' on passe à la ligne suivante
    cptLignes = cptLignes + 1
Wend

**titilex** · 24/07/2019, 16h01

Salut Tylert,

J'ai testé ton code, est l'empilement des sommes ne se fait pas correctement.
Sur la première ligne où j'ai qu'une désignation, il me fait la somme du premier groupe + 2éme + 3éme.
Et plusieurs des calculs de somme se font en faisant des cumuls de plusieurs groupes.

J'ai essayé de faire des modifs, mais je me retrouve avec des erreurs partout.

J'ai mis mon fichier de tests pour lequel j'ai mis des valeurs sur les colonnes C à G.
Si toi ou quelqu'un d'autres à un peu de temps à perdre là-dessus, cela m'aiderai bien.

**Tylert** · 25/07/2019, 09h06

J'avais inversé les lignes de codes en fonction de la condition...
du coup au lieu de sommer quand on changait de désignation et de mettre sur la premiere ligne de la désignation, j'écrivais la somme à chaque fois qu'on gardait la même désignation.
Y'avait un pattern dans le tableau rempli avec le mauvais code tu aurais du voir cela!!

le code corrigé (et testé avec ton fichier test):

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
 
Private Sub CommandButton1_Click()
 
'Variable pour retenir en quelle couleur je dois colorier ma ligne
Dim CouleurCourante As Long
'compteur pour avancer dans les lignes du tableau
Dim cptLignes As Integer
'Stockage de la désignation précédente pour savoir si on a changé de désignation ou pas quand on change de ligne
Dim DesignationPrecedente As String
'Indice de la 1ere ligne dans laquelle on a trouvé une désignation pour se souvenir ou il faut stocker la somme
Dim IndLigneStockage As Integer
'Variable de stockage de la somme pour une désignation
Dim SommeDesignation As Variant
 
 
'les valeurs de couleurs sont fixées arbitrairement, a toi de trouver ce qui correspond au jaune ou au bleu
CouleurCourante = RGB(255, 255, 0)
'J'ai considéré que la désignation se trouvait dans la colonne A
DesignationPrecedente = Range("A1").Value
'on intialise la somme à 0 et l'indice de stockage pour la 1ere ligne
SommeDesignation = 0
IndLigneStockage = 1
 
cptLignes = 1
While Range("A" & cptLignes).Value <> ""
    'Si la valeur de la designation n'est pas la même que celle de la ligne précédente, alors on la change et on retient la nouvelle designation pour la prochaine ittération
    If DesignationPrecedente <> Range("A" & cptLignes).Value Then
        'on retient la valeur de la nouvelle désignation
        DesignationPrecedente = Range("A" & cptLignes).Value
        'Si on était jaune, on passe bleu pour la couleur a utiliser pour colorier
        If CouleurCourante = RGB(255, 255, 0) Then
            CouleurCourante = RGB(0, 255, 255)
        Else
        'sinon, on passe jaune
            CouleurCourante = RGB(255, 255, 0)
        End If
 
 
        'Si on a pas la même valeur de désignation, on stocke la somme pour la désignation sur la ligne pour laquelle on a trouvé la 1ere désignation (IndLigneStockage) et on initialise indlignestockage et sommeDesignation pour la nouvelle désignation
 
        Range("I" & IndLigneStockage).Value = SommeDesignation
        SommeDesignation = Range("C" & cptLignes) + Range("D" & cptLignes) + Range("E" & cptLignes) + Range("F" & cptLignes) + Range("G" & cptLignes)
        IndLigneStockage = cptLignes
    Else
        'Si on a pas la même valeur, On doit dans ce cas ajouter la valeur des colonnes C à G à la somme pour la désignation
        SommeDesignation = SommeDesignation + Range("C" & cptLignes) + Range("D" & cptLignes) + Range("E" & cptLignes) + Range("F" & cptLignes) + Range("G" & cptLignes)
    End If
 
    'On colorie la ligne
    Rows(cptLignes).Interior.Color = CouleurCourante
    ' on passe à la ligne suivante
    cptLignes = cptLignes + 1
Wend
'Columns("H:H").Select
'    With Selection.Interior
'        .Pattern = xlSolid
'        .PatternColorIndex = xlAutomatic
'        .ThemeColor = xlThemeColorLight1
'        .TintAndShade = 0
'        .PatternTintAndShade = 0
'    End With
End Sub

**titilex** · 20/08/2019, 09h05

Salut Tylert,

Après un petit arrêt bien mérité, me voila d'attaque pour avancer sur mes sujets.
Effectivement, cela fonctionne plutôt bien.

Y'avait un pattern dans le tableau rempli avec le mauvais code tu aurais du voir cela!!

C'est quoi un pattern ?

**Tylert** · 20/08/2019, 22h01

Envoyé par titilex

Salut Tylert,

Après un petit arrêt bien mérité, me voila d'attaque pour avancer sur mes sujets.
Effectivement, cela fonctionne plutôt bien.

C'est quoi un pattern ?

un schéma