Discussion :

[Carlitoto]

Bonjour à toutes et toutes,

Tout d'abord, je remercie la communauté pour la qualité des échanges que j'ai pu parcourir pour la création de ma macro.

Etant novice en la matière le forum est d'une aide précieuse.

Mais à ce stade de mon projet, je rencontre un souci sur une partie de mon code. je vais essayer d'être clair. Cette Macro a pour but de ventiler les codes BB selon 6 questions. Chaque question se réfère un à Niveau en particulier. A chaque niveau, une liste de pays lui est associée.

Le résultat de la Macro doit se présenter sous la forme d'un tableau à 5 colonnes (fichier en pj "Import.xls") :

• En A 'id' est la concaténation de B, C et D
• en B le "millésime"
• en C le code "BB"
• En D le" nombre" de pays répondant à la question (les doublons sont comptabilisés)
• En E la liste des pays (valeur unique) en "commentaire"

Mon problème réside dans la partie de la macro qui traite la liste des pays. En effet, ma boucle ne renvoie pas toutes les valeurs attendues.

Par exemple, pour le code BB098, présent sur les lignes 716 et 717 du fichier "repartition_des_BB_niveau", j'ai deux pays : France et Emirats Arabes Unis qui répondent chacun à une question. Lorsque je lance ma macro, je devrais donc dans mon fichier "Import.xls" retrouver pour le Code BB098, ces pays dans deux cellules commentaires pays. Or le résultat n'est pas escompté, je retrouve uniquement Emirats Arabes Unis pour ce code.

Malheureusement, ce souci répète à la marge (environ 12 cas), mais cela pose problème car cette macro est vouée à une "industrialisation".

Voici le bout de code concerné :

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
Dim Entity As String, Question_res As String, pays_prec As String
Dim Lastline_import As Integer, Lastline_src As Integer
 
Lastline_import = ActiveWorkbook.Sheets("Import").Cells(Application.Rows.Count, 2).End(xlUp).Row
pays_prec = ""
Lastline_src = Workbooks("Repartition_des_BB_niveau.xlsm").Sheets("Correspondance").Cells(Application.Rows.Count, 2).End(xlUp).Row
 
For I = 2 To Lastline
    Entity = ActiveWorkbook.Sheets("Import").Cells(I, 3).Value
    Question_res = ActiveWorkbook.Sheets("Import").Cells(I, 4).Value
    For l = 2 To Lastline_src
        If Entity = Workbooks("Repartition_des_BB_niveau.xlsm").Sheets("Correspondance").Cells(l, 2).Value _
            And Question_res = Workbooks("Repartition_des_BB_niveau.xlsm").Sheets("Correspondance").Cells(l, 11).Value Then
                Pays = Workbooks("Repartition_des_BB_niveau.xlsm").Sheets("Correspondance").Cells(l, 8).Value
                If pays_prec <> Pays Then
                    'que faire si pas doublons
                    ActiveWorkbook.Sheets("Import").Cells(I, 6).Value = ActiveWorkbook.Sheets("Import").Cells(I, 6).Value & " - " & Pays
                    pays_prec = Pays
                End If
            'End If
        End If
    Next
 ActiveWorkbook.Sheets("Import").Cells(I, 6).Value = Mid(ActiveWorkbook.Sheets("Import").Cells(I, 6).Value, 4)

Auriez-vous une piste pour comprendre pourquoi ce souci et comment l'éviter ?

J'en profite aussi pour vous demander de l'aide sur un autre problème, je souhaiterais également calculer pour chaque BB et en fonction des questions, le nombre de fois où BB est présent dans chaque pays dans une variable pour les ajouter à ma liste de pays en commentaire:

Ex : 2020 - BB001 - Q52 // 2020 // BB001 // Q52 // 2 // EGYPTE : 2
Ex : 2020 - BB055 - Q52 // 2020 // BB055 // Q52 // 2 // ESPAGNE: 1 - PORTUGAL : 1

Je vous joins les deux fichiers pour être plus explicite.

En tout cas, je vous remercie d'avance pour votre aide.

Carlitoto

[Ben_L]

Bonjour,

Le code que vous avez posté n'est pas fonctionnel. Il manque la fermeture d'une des instructions For...Next.
Il vous manque également des déclarations de variables, et vous utilisez une variable Lastline qui n'est pas initialisée (c'est Lastline_import qui a été initialisée).
Ci-dessous une proposition de correction :

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
Public Sub CeciEstUnTest()
    Dim Entity As String, Question_res As String, pays_prec As String, Pays As String
    Dim Lastline_import As Long, Lastline_src As Long, iRow As Long, cRow As Long
    Dim ShtCor As Worksheet, ShtImp As Worksheet
    Set ShtCor = Workbooks("Repartition_des_BB_niveau.xlsm").Sheets("Correspondance")
    Set ShtImp = ActiveWorkbook.Sheets("Import")    'Plutôt <ThisWorkbook> ?!??!??
    Lastline_import = ShtImp.Cells(ShtImp.Rows.Count, 2).End(xlUp).Row
    Lastline_src = ShtCor.Cells(ShtCor.Rows.Count, 2).End(xlUp).Row
    pays_prec = ""
 
    For iRow = 2 To Lastline_import
        Entity = ShtImp.Cells(iRow, 3).Value
        Question_res = ShtImp.Cells(iRow, 4).Value
        For cRow = 2 To Lastline_src
            If Entity = ShtCor.Cells(cRow, 2).Value And Question_res = ShtCor.Cells(cRow, 11).Value Then
                Pays = ShtCor.Cells(cRow, 8).Value
                If pays_prec <> Pays Then
                    ShtImp.Cells(iRow, 6).Value = ShtImp.Cells(iRow, 6).Value & " - " & Pays
                    pays_prec = Pays
                End If
            End If
        Next cRow
        ShtImp.Cells(iRow, 6).Value = Mid(ShtImp.Cells(iRow, 6).Value, 4)
    Next iRow
 End Sub

Si le code proposé correspond bien à ce que vous réalisez, alors le problème provient de ce que la ligne d'initialisation de pays_prec devrait se trouver à l'intérieur d'une boucle For.

Cdt

[Carlitoto]

Bonjour Ben,

Merci beacoup pour la correction,

Dans mon précédent message, j'avais effectivement, oublié de fermer la boucle (erreur de copier/coller)

En utilisant votre code, le résultat reste inchangé. Comme vous l'avez dit, il semble donc que ce soit l'initialisation de "pays_prec".

Je l'ai donc placé dans la boucle et miracle, cela fonction correctement, un immense merci pour votre aide.

Concernant mon second problème, auriez-vous une piste vers laquelle m'orienter ?

Bien à vous,

Carlitoto

[Ben_L]

Pour votre second problème, j'aurais tendance à utiliser un dictionnaire (en activant la référence Microsoft Scripting Runtime puis en utilisant le type d'objet Scripting.Dictionary).
Ca se comporte comme une collection, mais est généralement plus efficace et, surtout, dispose d'une méthode Exists pour déterminer si un élément est déjà présent dans le dictionnaire sans avoir à faire de la gestion d'erreur.

En somme, je définirais un identifiant (une chaîne de caractère) unique pour chaque cas ; par exemple, la concaténation du code BB, de l'année, du pays et du numéro de question (avec un caractère de séparation entre chaque propriété).
A chaque fois qu'un pays est identifié, définissez l'identifiant qui va correspondre au cas courant.
Vérifiez si l'identifiant existe dans votre dictionnaire :

• Si oui, récupérez la valeur correspondante et incrémentez la,
• Si non, définissez un nouvel élément dans votre dictionnaire et associez lui la valeur 1.

Un exemple simpliste et totalement arbitraire, juste pour vous donner une idée des syntaxes :

Code :

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

1
2
3
4
5
6
7
8
9
10
Dim SynthDct As New Scripting.Dictionary, iRow As Long, Sht As Worksheet, sKey As String
Set Sht = ThisWorkbook.Sheets(1)
For iRow = 2 to 10
    sKey = Sht.Cells(iRow, 1).Text & "\" & Sht.Cells(iRow, 2).Text & "\" & Sht.Cells(iRow, 3).Text
    If SynthDct.Exists(sKey) Then
        SynthDct(sKey) = SynthDct(sKey) + 1
    Else
        SynthDct.Add sKey, 1
    End If
Next i

Selon l'usage que vous souhaitez faire de cette synthèse, il peut être préférable d'utiliser des dictionnaires imbriqués.
Un premier dictionnaire aura alors pour identifiants (on les appelle des clés) les BB code seuls, et chaque élément associé à l'une de ces clés sera lui-même un dictionnaire (appelons le "de rang 2").
Chaque dictionnaire de rang 2 aura pour clés les numéros de question, et pour éléments des dictionnaires de rang 3.
Chaque dictionnaire de rang 3 aura [... je ne vais pas tous les faire ...]
Au rang final, les dictionnaires contiendront les nombres de correspondances auxquels vous vous intéressez.

Cette méthode de dictionnaires imbriqués est potentiellement plus lourde à mettre en œuvre, mais facilite largement la récupération des informations si vous avez besoin de faire des synthèses BB code par BB code par exemple.

Cdt

[Carlitoto]

Bonsoir Ben,

Encore merci pour vos explications, il est vrai que j'ai pensé à partir sur un dictionnaire mais j'avais des doutes sur la manière d'aborder cette problématique,

Je vais me pencher sur la question avec les éléments que vous m'avez partagé.

J'espère revenir vers vous avec un résultat positif,

Excellente soirée,

Carlitoto

[Carlitoto]

Bonjour Ben,

J'espère que le week-end fut bon,

j'ai travaillé à partir de l'exemple et j'arrive à récupérer et afficher les données du dictionnaire :

• Colonne 1 : BB/PAYS
• Colonne 2 : Nombre de pays

Je souhaiterais pouvoir utiliser les valeurs du dictionnaire pour les ajouter à chaque pays identifié avec cette partie de la macro, qui vérifie entre le fichier source et le fichier cible la correspondance entre Code BB et Question pour afficher le nom du/des pays qui s'y réfèrent.

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
Dim Entity As String, Question_res As String, pays_prec As String
Dim Lastline_import As Integer, Lastline_src As Integer
 
Lastline_import = ActiveWorkbook.Sheets("Import").Cells(Application.Rows.Count, 2).End(xlUp).Row
Lastline_src = Workbooks("Repartition_des_BB_niveau.xlsm").Sheets("Correspondance").Cells(Application.Rows.Count, 2).End(xlUp).Row
 
For I = 2 To Lastline_import
    Entity = ActiveWorkbook.Sheets("Import").Cells(I, 3).Value
    Question_res = ActiveWorkbook.Sheets("Import").Cells(I, 4).Value
    pays_prec = ""
    For l = 2 To Lastline_src
        If Entity = Workbooks("Repartition_des_BB_niveau.xlsm").Sheets("Correspondance").Cells(l, 2).Value _
        And Question_res = Workbooks("Repartition_des_BB_niveau.xlsm").Sheets("Correspondance").Cells(l, 11).Value Then
            Pays = Workbooks("Repartition_des_BB_niveau.xlsm").Sheets("Correspondance").Cells(l, 8).Value & " : "
            If pays_prec <> Pays Then
                'que faire si pas doublons
                ActiveWorkbook.Sheets("Import").Cells(I, 6).Value = ActiveWorkbook.Sheets("Import").Cells(I, 6).Value & " - " & Pays
                pays_prec = Pays
            End If
        End If
    Next
    ActiveWorkbook.Sheets("Import").Cells(I, 6).Value = Mid(ActiveWorkbook.Sheets("Import").Cells(I, 6).Value, 4)
Nex

Mais après de moults essais, je ne trouve pas la syntaxe appropriée.

Mon idée initiale était d'ajouter une troisième condition, au 1er if du code ci dessus, du type

Code :

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

1
2
3
If Entity = Workbooks("Repartition_des_BB_niveau.xlsm").Sheets("Correspondance").Cells(l, 2).Value _
        And Question_res = Workbooks("Repartition_des_BB_niveau.xlsm").Sheets("Correspondance").Cells(l, 11).Value  _
        and Dct(keys) = Workbooks("Repartition_des_BB_niveau.xlsm").Sheets("Correspondance").Cells(l, 2).Value  &"\"& Workbooks("Repartition_des_BB_niveau.xlsm").Sheets("Correspondance").Cells(l, 8).Value Then

Mais bien entendu cela ne fonctionne pas du tout.

Est-ce uniquement un souci de syntaxe ou mon approche n'est pas la bonne et le passage à des dictionnaires imbriqués est la meilleure voie ?

Belle après midi,

Charles

[Ben_L]

Bonjour Charles,

Sans rapport direct avec votre question, je me permets une remarque relative aux performances et à la lisibilité de votre code : le fait de trouver un classeur parmi ceux ouverts et d'y appeler une feuille particulière consomme (certes pas énormément) des ressources et nuit à la lisibilité générale de votre code, chaque ligne étant considérablement rallongée.
L'utilisation de variables pour faire références aux différentes feuilles que vous utilisez pourrait améliorer cela.
Plus particulièrement, l'identificateur ActiveWorkbook est susceptible de pointer vers un classeur qui n'est pas celui auquel vous pensez. Il est, pour ce cas, vraiment préférable de n'utilisez l'identificateur qu'une seule fois, puis de stocker l'objet d'intérêt (classeur, feuille ou autre selon le cas d'usage) dans une variable objet.

Autre point, l'utilisation d'une variable dont le nom est un "L" minuscule est une pratique comparable à une obfuscation : dans l'éditeur VBA, distinguer un L minuscule du chiffre 1 relève du miracle...
Procéder ainsi dans votre code final peut être acceptable, selon l'usage du code. Mais pour poser des questions sur un forum, il paraît préférable d'améliorer autant que possible la lisibilité du code que vous postez.

Concernant votre besoin, j'avoue ne pas comprendre ce que vous souhaitez faire exactement : quelles sont les infos que vous souhaitez récupérer et comment souhaitez vous les synthétiser ?
Désolé de vous demander plus d'explication, mais malgré plusieurs relectures, je ne comprends pas votre besoin.

Dans le code que vous avez posté, je ne vois pas le remplissage du dictionnaire.
Je ne vois pas non plus où vous fixez une valeur pour la variable keys.
Et j'ai l'impression que vous essayez d'extraire de l'info de votre dictionnaire dans la même boucle que celle où vous le remplissez. Cela n'est pas possible : il est nécessaire de passer en revue toutes les données d'entrée. Seulement une fois le dictionnaire entièrement rempli pourrez-vous accéder à une information complète.

Cdt

[Carlitoto]

Ben,

Merci pour ces retours,

Il est vrai que je n'ai pas ajouté le bout de code concernant le dictionnaire dans le fichier initial.

L'idée initiale était de placer dans le champ commentaire la liste des pays pour le couple Code BB/Question. Cette demande à quelque peu évoluée.

En effet, on me demande maintenant dans le champ commentaire en plus de la liste de pays, le nombre de Code BB par Pays. C'est pour cela que je souhaitais via de dictionnaire récupérer le cumul par Code BB et Pays ( extrait du dico BB001/EGYPTE // 2).

Mon idée était donc de calculer le présence d'un Code BB dans un pays (d'où le dictionnaire) puis à partir de la récupération du Pays via le couple Code BB/Question associer nom du pays et la valeur du dictionnaire correspondant.

Ex :
ID // Millesime // BB // Question // Nombre // Commentaire
2020 - BB055 - Q52 // 2020 // BB055 // Q52 // 2 // ESPAGNE : 1 - PORTUGAL : 1

Concernant la "propreté" de mon code, étant très débutant, j'ai bien conscience de ne pas avoir encore les bons réflex de syntaxe, je ferais ce travail une fois que l'ensemble de ma macro sera finalisée, car effectivement, il y a beaucoup de reprises des mêmes expressions pour des variables différents et donc sans doutes des délais de traitement longs.

J'espère avoir été plus clair dans mon explication,

Bien à vous,

[Ben_L]

J'ai bien peur que l'on ne se comprenne toujours pas...

L'idée initiale était de placer dans le champ commentaire la liste des pays pour le couple Code BB/Question.

Ici je comprends qu'il existe potentiellement plusieurs pays pour lesquels on va trouver un même couple [Code BB, Question].

[...] puis à partir de la récupération du Pays via le couple Code BB/Question associer nom du pays [...]

Mais là, il semble que chaque couple [Code BB, Question] ne puisse correspondre qu'à un seul pays.

Dans le doute, voici une proposition, à adapter pour coller à vos besoins :

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
Dim SynthDct As New Scripting.Dictionary, OccurDct As Scripting.Dictionary
Dim iRow As Long, ShtSrc As Worksheet, ShtImp As Worksheet, sKey As String, sSynth As String, nSynth As Long
Dim Lastline_import As Long, Lastline_src As Long, sBBc As String, sQst As String, sPays As String
Set ShtSrc = Workbooks("Repartition_des_BB_niveau.xlsm").Sheets("Correspondance")
Set ShtImp = ActiveWorkbook.Sheets("Import")
Lastline_src = ShtSrc.Cells(ShtSrc.Rows.Count, 2).End(xlUp).Row
Lastline_import = ShtImp.Cells(ShtImp.Rows.Count, 2).End(xlUp).Row
'Récupération des données source
For iRow = 2 to Lastline_src
    sBBc = ShtSrc.Cells(iRow, 1).Text      'Numéro de colonne à adapter ! (BB code, dans Source)
    sQst = ShtSrc.Cells(iRow, 2).Text      'Numéro de colonne à adapter ! (Question, dans Source)
    sPays = ShtSrc.Cells(iRow, 3).Text     'Numéro de colonne à adapter ! (Pays, dans Source)
    sKey = sBBc & "\\" & sQst
    If SynthDct.Exists(sKey) Then
        Set OccurDct = SynthDct(sKey)
        If OccurDct.Exists(sPays) Then
            OccurDct(sPays) = OccurDct(sPays) + 1
        Else
            OccurDct.Add sPays, 1
        End If
    Else
        Set OccurDct = New Scripting.Dictionary
        OccurDct.Add sPays, 1
        SynthDct.Add sKey, OccurDct
    End If
Next iRow
 
'Passage en revue des lignes d'import
For iRow = 2 to Lastline_import
    sBBc = ShtImp.Cells(iRow, 1).Text      'Numéro de colonne à adapter ! (BB code, dans Import)
    sQst = ShtImp.Cells(iRow, 2).Text      'Numéro de colonne à adapter ! (Question, dans Import)
    sPays = ShtImp.Cells(iRow, 3).Text     'Numéro de colonne à adapter ! (Pays, dans Import)
    sKey = sBBc & "\\" & sQst
    If SynthDct.Exists(sKey) Then
        Set OccurDct = SynthDct(sKey)
        sSynth = ""
        nSynth = 0
        For Each Itm In OccurDct
            sSynth = sSynth & Itm & " : " & OccurDct(Itm) & " | "
            nSynth = nSynth + OccurDct(Itm)
        Next Itm
        ShtImp.Cells(iRow, 4).Value = nSynth                          'Numéro de colonne à adapter ! (Nb d'occurrences total, dans Import)
        ShtImp.Cells(iRow, 5).Value = Left(sSynth, Len(sSynth) - 3)   'Numéro de colonne à adapter ! (Liste Pays, dans Import)
    Else
        ShtImp.Cells(iRow, 4).Value = 0                               'Numéro de colonne à adapter ! (Nb d'occurrences total, dans Import)
    End If
Next iRow

Code non testé !

[Carlitoto]

Ben,

Ce que j'essaye d'expliquer c'est dans le fichier source "Correspondances" pour un Code BB, il peut y avoir la présence de X fois le même Pays et chaque pays est lié à une Question.

Donc, je peux avoir 100 lignes pour le Code BBY avec 10 lignes avec France, 50 avec Italie, 30 avec Pologne, 10 avec Egypte. Ces 4 pays sont liés à la question Q52.

Ce que je souhaite, dans la colonne F du fichier "import", c'est afficher "France : 10 - Italie : 50 - Pologne : 30 - Egypte : 10"

J'arrive à récupérer la liste (en valeur unique) des pays qui sont liés à un code BB et une question (Cf code suivant). J'obtiens donc : France - Italie - Pologne - Egypte.

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
Dim Entity As String, Question_res As String, pays_prec As String
Dim Lastline_import As Integer, Lastline_src As Integer
 
Lastline_import = ActiveWorkbook.Sheets("Import").Cells(Application.Rows.Count, 2).End(xlUp).Row
Lastline_src = Workbooks("Repartition_des_BB_niveau.xlsm").Sheets("Correspondance").Cells(Application.Rows.Count, 2).End(xlUp).Row
 
For I = 2 To Lastline_import
    Entity = ActiveWorkbook.Sheets("Import").Cells(I, 3).Value
    Question_res = ActiveWorkbook.Sheets("Import").Cells(I, 4).Value
    pays_prec = ""
    For l = 2 To Lastline_src
        If Entity = Workbooks("Repartition_des_BB_niveau.xlsm").Sheets("Correspondance").Cells(l, 2).Value _
        And Question_res = Workbooks("Repartition_des_BB_niveau.xlsm").Sheets("Correspondance").Cells(l, 11).Value Then
            Pays = Workbooks("Repartition_des_BB_niveau.xlsm").Sheets("Correspondance").Cells(l, 8).Value & " : "
            If pays_prec <> Pays Then
                'que faire si pas doublons
                ActiveWorkbook.Sheets("Import").Cells(I, 6).Value = ActiveWorkbook.Sheets("Import").Cells(I, 6).Value & " - " & Pays
                pays_prec = Pays
            End If
        End If
    Next
    ActiveWorkbook.Sheets("Import").Cells(I, 6).Value = Mid(ActiveWorkbook.Sheets("Import").Cells(I, 6).Value, 4)
Next

En partant de cette base, Je pensais donc avec un compteur calculer le nombre de fois qu'un code BB est présent dans un pays. (Valeur en gras dans mon exemple ci-dessus) J'ai donc placé le dictionnaire avant le code précédent en me disant qu'en lutilisant à l'intérieure de la boucle citée, je pourrais obtenir "France : 10 - Italie : 50 - Pologne : 30 - Egypte : 10" en commentaire pour le couple "Code BBX" et "Q5Y"


En tout cas, je vais regarder le code que vous m'avez transmis,

Espérons que j'ai été plus clair, précis

[Carlitoto]

Bonjour Ben,

Un très grand merci pour votre aide malgré mes explications parfois "tirées par les cheveux",

J'ai fais quelques mini modifications du dernier bout de code pour arriver au résultat attendu,

Vous avez été une très grande aide.

J'espère maintenant me perfectionner rapidement pour devenir plus autonome en VBA.

Je vous souhaite une belle journée,

[Ben_L]

Merci à vous d'avoir continuer à fournir des explications.
La compréhension mutuelle n'est malheureusement pas toujours immédiate lorsque l'on échange sur des sujets techniques.

Je vous souhaite de rapidement atteindre les objectifs que vous vous êtes fixés dans l'utilisation du VBA.
Bonne soirée !