Discussion :

[Part30]

Bonjour à tous,

Je suis nouveau sur le forum, je ne suis pas très doué pour les Macros et j'ai un souci pour faire la sommes des valeurs en doublons qui sont sur la même ligne.

Alors je m'explique, j'ai un tableau sur lequel est référencé des données de stagiaires. Un stagiaire peut effectuer une formation dans le mois sur plusieurs sites différents avec un parcours sur 3 phases, mais il peut arriver que le stagiaire fasse un stage sur 2 sites identique entre la phase 1 et la phase 3.

Donc voici comment se présente mon tableau (je mets également le fichier en pièce jointe) :

OF dans le tableau = site.

- de la colonne A à I il y a tout les éléments concernant le stagiaire.
- de la colonne J à U il y a les sites sur lesquels le stagiaire a effectué ses stage avec le nombre d'heures

Je souhaite que dans la colonne V il y ai le total des heures effectuées sur le site qui est en doublon s'il y en a un et que s'il n'y a pas de site en doublon la cellule reste vide.

Je pense que vous allez mieux comprendre en regardant le fichier joint.

Merci pour votre aide.

Bien cordialement.

Tableau de suivi.xls

[MarcelG]

Bonjour et bienvenu sur ce Forum,

2 questions:
Si 1 site est mentionné 3 fois, la somme s'effectue-t-elle sur les 3? (tu évoques des doublons et je préfère poser la question)
Se peut-il que 2 sites différents soient en doublon sur la même ligne. (site1, site1, site3, site2, site2)?

Quant au processus.
Je me demande si une formulation Excel ne répondrait pas au besoin Personnellement, je ne la vois pas.
Si l'on envisage un développement en VBA, et dans la négative à ma 2ème question, alors j'envisagerais une démarche assez triviale.
Pour chaque ligne, dictionnaire sur les sites.
Pour chaque clé de celui-ci, compter le nombre de fois où il se trouve sur la ligne et sommer les heures si le nombre est supérieur à 1.

[Part30]

Bonjour MarcelG,

Tout d'abord merci pour votre réponse.

Concernant votre première question, oui, si un site est mentionné 3 fois, la somme doit s'effectuer sur les 3.

Pour la deuxième question, non, 2 sites différents ne peuvent pas être en doublon sur la même ligne.

D'ailleurs je viens d'y penser il faut également créer une nouvelle colonne dans laquelle sera mentionnée le nom du site qui est en doublons.

Merci pour l'aide que vous pourrez m'apporter.

Cordialement.

[MarcelG]

Salut,

Voici, Voilou,

N'oublie pas d'activer la Référence "Microsoft Scripting RunTime" dans l'éditeur VBE (Menu Outils\Références)

Tu consultes le code.
Autrement dit, tu regardes toutes les méthodes et propriétés qui y sont reportées, et tu reviens à la moindre question.

En colonne V : le site en doublon
En colonne W: le cumul des heures

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
Option Explicit
 
Public Sub somh_dbl()
 
'Activer la Référence Scripting RunTime
Dim dico As Scripting.Dictionary
 
Dim dernl As Integer, i As Integer
Dim col As Byte
Dim d As Variant
Dim nb As Byte, somh As Byte
 
With ThisWorkbook.Worksheets("Suivi heures Centre")
        dernl = .Cells(.Rows.Count, 10).End(xlUp).Row
        For i = 12 To dernl
                Set dico = CreateObject("Scripting.Dictionary")
                'Balayage des colonnes J à T
                'Dictionnaire des sites sur la ligne i, toutes les 2 colonnes
                For col = 10 To 20 Step 2
                        With .Cells(i, col)
                                If Not dico.Exists(.Value) Then dico.Add .Value, .Value
                        End With
                Next col
                'Pour chaque clé du dictionnaire, nombre dde présences sur la ligne et somme des heures
                For Each d In dico.Keys
                        nb = 0
                        somh = 0
                        'Sommation toutes les 2 colonnes de J à T
                        For col = 10 To 20 Step 2
                                If .Cells(i, col) = d Then
                                    nb = nb + 1
                                    somh = somh + .Cells(i, col + 1)
                                 End If
                          Next col
                          'Information des colonnes V (site en doublon) et W (somme des heures)
                          If nb > 1 Then
                                .Cells(i, 22).Value = d
                                .Cells(i, 23).Value = somh
                                Exit For
                          End If
                Next d
                Set dico = Nothing
                With .Cells(i, 23)
                        If .Value = 0 Then .Value = ""
                End With
        Next i
End With
 
End Sub

[Part30]

Bonjour,

J'ai essayé la macro et ça fonctionne parfaitement.
Maintenant je vais essayer de faire la même chose pour tous les autres mois, si je rencontre un souci je reviendrai vers toi.

Merci énormément de ton aide.

[MarcelG]

Bonjour Part, Bonjour le Forum,

Content que la procédure soit effective.

Si tous les mois, les feuilles ont la même structure, alors la procédure peut être argumentée du nom de la feuille, ou de la feuille elle-même.
Regarde ce que j’ai écrit dans cette discussion, ouverte par notre ami Antoine, au sujet des procédures argumentées.

N’oublie pas de placer cette discussion en mode « Résolue »

Bonne continuation.

[Part30]

Les autres mois sont sur la même feuille, ils sont collés à droite de celui pour lequel tu m'as donné la macro donc je pense qu'il va falloir modifier les colonnes sur chaque mois.

Si je place cette discussion en mode « Résolue », je pourrai toujours revenir dessus si j'ai un souci ?

[MarcelG]

Salut,

Si je place cette discussion en mode « Résolue », je pourrai toujours revenir dessus si j'ai un souci ?

Pas problème.
Je crois même que tu peux retirer ce mode.

Les autres mois sont sur la même feuille, ils sont collés à droite

Si l'espace entre tableaux mensuels est constant (Ce doit être le cas pour une certaine rigueur), alors il suffirait d'argumenter la colonne de départ.
Autrement dit, le nombre 10 utilisé dans la procédure pour désigner la 1ère colonne pourrait être variable.

Attention! Je me répète. Tous les tableaux doivent avoir la même structure.

Nous aurions alors un code du style

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
Option Explicit
 
Public Sub dbl_mensuels()
 
Dim i As Integer
 
For i = 10 To 165 Step 15
        Call somh_dbl(i)
Next i
 
End Sub
 
Public Sub somh_dbl(lacolonne As Integer)
 
'Activer la Référence Scripting RunTime
Dim dico As Scripting.Dictionary
 
Dim dernl As Integer, i As Integer
Dim col As Byte
Dim d As Variant
Dim nb As Byte, somh As Byte
 
With ThisWorkbook.Worksheets("Suivi heures Centre")
        dernl = .Cells(.Rows.Count, lacolonne).End(xlUp).Row
        For i = 12 To dernl
                Set dico = CreateObject("Scripting.Dictionary")
                'Balayage des colonnes J à T
                'Dictionnaire des sites sur la ligne i, toutes les 2 colonnes
                For col = lacolonne To (lacolonne + 10) Step 2
                        With .Cells(i, col)
                                If Not dico.Exists(.Value) Then dico.Add .Value, .Value
                        End With
                Next col
                'Pour chaque clé du dictionnaire, nombre dde présences sur la ligne et somme des heures
                For Each d In dico.Keys
                        nb = 0
                        somh = 0
                        'Sommation toutes les 2 colonnes de J à T
                        For col = lacolonne To (lacolonne + 10) Step 2
                                If .Cells(i, col) = d Then
                                    nb = nb + 1
                                    somh = somh + .Cells(i, col + 1)
                                 End If
                          Next col
                          'Information des colonnes V (site en doublon) et W (somme des heures)
                          If nb > 1 Then
                                .Cells(i, lacolonne + 12).Value = d
                                .Cells(i, lacolonne + 13).Value = somh
                                Exit For
                          End If
                Next d
                Set dico = Nothing
                With .Cells(i, lacolonne + 13)
                        If .Value = 0 Then .Value = ""
                End With
        Next i
End With
 
End Sub

Tu reviens si nécessaire.

[patricktoulon]

re
Bonjour
j'ai fait un petit test vite fait dans l'exemplaire que tu a donné
donc pour janvier ca donne ca

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
Sub test()
    Dim dico As Object, dico2 As Object,i&,c&,plage as range,elem
    For i = 12 To Cells(Rows.Count, "j").End(xlUp).Row
        Set dico = CreateObject("scripting.dictionary")
        Set dico2 = CreateObject("scripting.dictionary")
        Set plage = Range("j" & i).Resize(1, 12)
        For c = 1 To plage.Cells.Count Step 2
            If plage.Cells(c).Value <> "" Then
                dico(plage.Cells(c).Value) = Val(dico(plage.Cells(c).Value)) + Val(plage.Cells(c).Offset(0, 1).Value)
                dico2(plage.Cells(c).Value) = Val(dico2(plage.Cells(c).Value)) + 1
            End If
        Next
        For Each elem In dico
            If Val(dico2(elem)) > 1 Then Debug.Print "ligne " & i & " mot en double = " & elem & " : cumul heure = " & dico(elem)
            If Val(dico2(elem)) > 1 Then Cells(i, "j").Offset(0, 12).Value = dico(elem)
        Next
    Next
End Sub

le dico sert a stocker le cumul et le dico2 sert a pointer les sites en doublonsdans chaque lignes , il sont remis a zero pour chaque ligne
voir dans le debug
capture

[Part30]

Salut,



Pas problème.
Je crois même que tu peux retirer ce mode.



Si l'espace entre tableaux mensuels est constant (Ce doit être le cas pour une certaine rigueur), alors il suffirait d'argumenter la colonne de départ.
Autrement dit, le nombre 10 utilisé dans la procédure pour désigner la 1ère colonne pourrait être variable.

Attention! Je me répète. Tous les tableaux doivent avoir la même structure.

Nous aurions alors un code du style

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
Option Explicit
 
Public Sub dbl_mensuels()
 
Dim i As Integer
 
For i = 10 To 165 Step 15
        Call somh_dbl(i)
Next i
 
End Sub
 
Public Sub somh_dbl(lacolonne As Integer)
 
'Activer la Référence Scripting RunTime
Dim dico As Scripting.Dictionary
 
Dim dernl As Integer, i As Integer
Dim col As Byte
Dim d As Variant
Dim nb As Byte, somh As Byte
 
With ThisWorkbook.Worksheets("Suivi heures Centre")
        dernl = .Cells(.Rows.Count, lacolonne).End(xlUp).Row
        For i = 12 To dernl
                Set dico = CreateObject("Scripting.Dictionary")
                'Balayage des colonnes J à T
                'Dictionnaire des sites sur la ligne i, toutes les 2 colonnes
                For col = lacolonne To (lacolonne + 10) Step 2
                        With .Cells(i, col)
                                If Not dico.Exists(.Value) Then dico.Add .Value, .Value
                        End With
                Next col
                'Pour chaque clé du dictionnaire, nombre dde présences sur la ligne et somme des heures
                For Each d In dico.Keys
                        nb = 0
                        somh = 0
                        'Sommation toutes les 2 colonnes de J à T
                        For col = lacolonne To (lacolonne + 10) Step 2
                                If .Cells(i, col) = d Then
                                    nb = nb + 1
                                    somh = somh + .Cells(i, col + 1)
                                 End If
                          Next col
                          'Information des colonnes V (site en doublon) et W (somme des heures)
                          If nb > 1 Then
                                .Cells(i, lacolonne + 12).Value = d
                                .Cells(i, lacolonne + 13).Value = somh
                                Exit For
                          End If
                Next d
                Set dico = Nothing
                With .Cells(i, lacolonne + 13)
                        If .Value = 0 Then .Value = ""
                End With
        Next i
End With
 
End Sub

Tu reviens si nécessaire.

Bonjour,

Ta macro fonctionne mais ne va pas jusqu'au bout car mon tableau va de Janvier 2019 à Juin 2020 donc il y a 18 tableaux identique pour les 18 mois.
J'ai essayer de modifier ta macro pour que le résultat se fasse jusqu'au bout sur tout les mois mais je n'y arrive pas.

Peux tu me donner la macro qui ferra le travail sur tout les tableaux s'il te plait ?

[MarcelG]

Bonjour Part, Bonjour le Forum,

Si tu as bien intégré cette ligne de code

Code :

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

For i = 10 To 165 Step 15

alors l'adaptation ne sera pas difficile.

Regarde et reviens si nécessaire.

[Part30]

Bonjour Part, Bonjour le Forum,

Si tu as bien intégré cette ligne de code

Code :

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

For i = 10 To 165 Step 15

alors l'adaptation ne sera pas difficile.

Regarde et reviens si nécessaire.

Bonjour,

Justement j'ai modifié cette ligne en remplaçant 165 par 240 mais la macro s'arrête au mois d'avril 2020 il me manque 2 mois, mai 2020 et juin 2020.
Quand je mets 255 à la place de 165 sa me met : message d'erreur d'exécution 6 - Dépassement de capacité.
Quand je clique sur Débogage sa me surligne en jaune ce code

Code :

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

For col = lacolonne To (lacolonne + 10) Step 2

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
Option Explicit
 
Public Sub dbl_mensuels()
 
Dim i As Integer
 
For i = 10 To 255 Step 15
        Call somh_dbl(i)
Next i
 
End Sub
 
Public Sub somh_dbl(lacolonne As Integer)
 
'Activer la Référence Scripting RunTime
Dim dico As Scripting.Dictionary
 
Dim dernl As Integer, i As Integer
Dim col As Byte
Dim d As Variant
Dim nb As Byte, somh As Byte
 
With ThisWorkbook.Worksheets("Suivi heures Centre")
        dernl = .Cells(.Rows.Count, lacolonne).End(xlUp).Row
        For i = 12 To dernl
                Set dico = CreateObject("Scripting.Dictionary")
                'Balayage des colonnes J à T
                'Dictionnaire des sites sur la ligne i, toutes les 2 colonnes
                For col = lacolonne To (lacolonne + 10) Step 2
                        With .Cells(i, col)
                                If Not dico.Exists(.Value) Then dico.Add .Value, .Value
                        End With
                Next col
                'Pour chaque clé du dictionnaire, nombre dde présences sur la ligne et somme des heures
                For Each d In dico.Keys
                        nb = 0
                        somh = 0
                        'Sommation toutes les 2 colonnes de J à T
                        For col = lacolonne To (lacolonne + 10) Step 2
                                If .Cells(i, col) = d Then
                                    nb = nb + 1
                                    somh = somh + .Cells(i, col + 1)
                                 End If
                          Next col
                          'Information des colonnes V (site en doublon) et W (somme des heures)
                          If nb > 1 Then
                                .Cells(i, lacolonne + 12).Value = d
                                .Cells(i, lacolonne + 13).Value = somh
                                Exit For
                          End If
                Next d
                Set dico = Nothing
                With .Cells(i, lacolonne + 13)
                        If .Value = 0 Then .Value = ""
                End With
        Next i
End With
 
End Sub

Je ne comprend pas pourquoi le résultat s'arrête au mois d'avril 2020, c'est dommage il me manque que 2 mois.

[MarcelG]

Salut Part,

Vu.
Col est déclaré en Byte.
Modifie par

Code :

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

1
2
 
Dim col as Integer

[Part30]

Ah enfin ça fonctionne t'es le meilleur.

Merci beaucoup pour ton aide car c'était un vrai casse tête pour moi.

[MarcelG]

Salut,

S'il te plaît, Part.
Regarde le code et essaie, par tous les moyens (Forum, Aides Microsoft...), d'en comprendre les méthodes et propriétés.
N'hésite pas, après recherche, à poser les questions pour les points restés obscurs.
As-tu compris pourquoi le changement de type de variable était nécessaire?

Dans l'affirmative, je te souhaite une bonne continuation.

[Part30]

Oui, j'ai compris,

Byte Numérique Nombre entier de 0 à 255.
Integer Numérique Nombre entier de -32'768 à 32'767.

C'est pour cela que je ne pouvais pas aller au delà de 255.

Encore merci pour ton aide.
Je suis novice en macro mais j'essaie de comprendre pour avoir un meilleur niveau car pour mon travail j'en ai besoin pour mettre en place des outils qui me facilite les tâches.