Comparaison de 2 feuilles Excel et correspondance de cellules

[emi06]

Bonjour à tous !

Alors voilà mon problème:

Sur un fichier excel, j'ai 2 feuilles
- la 1ère contient un tableau recensant une liste de procédures en fonction de risques; càd qu'à chaque risque correspond une ou plusieurs procédures (cf les croix dan sle tableau via une macro double clic)
- la 2nde contient quasiment le même tableau sauf qu'au lieu d'avoir les procédures en fonction des risques, il s'agit de nom de sociétés; càd que chaque société est concernée par un ou plusieurs risques (cf les croix dan sle tableau via une macro double clic)

Grâce à une macro ou un TCD (sur plusieurs plages notamment, déjà testé mais étant novice que je n'ai pas réussi à le mener au bout ...) ou autre méthode, ce que je souhaiterai dans ma 3ème feuille, c'est que celle-ci ressorte pour chaque société et en fonction du risque considéré, les procédures associées.

Je donne un exemple pour plus de compréhension:
- cas de la société 1 (feuille 2)
- concerné en 1er lieu par le risque "piéton, véhicules" (feuille 2)
- or ce risque est mentionné dans les procédures n° 3, 23, 24, 25, 26, 27 (feuille 1)
- donc la feuille 3 ressort en A2 le nom de la société (par exemple via une liste déroulante - déjà mise en place) et en B2:Bi et C2:Ci respectivement le titre et la référence des procédures.

J'espère avoir été assez claire, n'hésitez pas pour plus de précision.

Merci d'avance pour votre aide !

Bonne journée !

 

[Modeste]

Re : Comparaison de 2 feuilles Excel et correspondance de cellules

Bonsoir emi06,

Il me semble avoir fini par comprendre ce que tu voudrais faire ... MAIS, pour qu'on puisse y arriver, ne faudrait-il pas que les "intitulés" des risques soient écrits à l'identique dans les deux premières feuilles?

• "Déchets" <> "Déchets - Amiante"
• "Energie électrique, pneumatique, hydraulique, etc." splitté en deux colonnes distinctes, dans la seconde feuille
• même un "Alt+Enter" présent d'un côté et pas de l'autre fera une différence!

Si tu repasses par ici et que tu cherches toujours une solution, peux-tu faire "un peu de ménage"

 

[emi06]

Re : Comparaison de 2 feuilles Excel et correspondance de cellules

Bonjour Modeste !

Tu as bien fait de me le signaler, je n'avais même pas vu ces erreurs !
Normalement, tout est bon sur ce nouveau fichier

Merci de prendre un peu de temps pour moi

Bonne journée !

 

[Modeste]

Re : Comparaison de 2 feuilles Excel et correspondance de cellules

Bonjour,

Il y avait encore une petite différence (Alt+Enter à un emplacement différent pour le risque "Présence de gaz/poussières/vapeurs inflammables) ... mais je n'ai pas vérifié les autres!

Teste le fichier joint, pour vérifier d'abord si j'ai bien compris ce que tu voulais.
La macro ne s'exécute pas quand on clique sur ton bouton, mais à chaque changement de valeur dans la cellule A2 de la feuille Attribution

 

[emi06]

Re : Comparaison de 2 feuilles Excel et correspondance de cellules

Re-bonjour Modeste !

Tu as tout à fait compris ce que je souhaitais réaliser
C'est donc quasiment parfait Je dis "quasiment" car j'aurai qqes questions en plus.
Déjà la base pour moi qui suis novice en VBA, ext ce que tu pourrais un peu m'expliquer le code, je ne connais pas du tout cet événement "Change". Je comprends à peu près le cheminement mais certaines synthaxes me sont completement inconnues ^^
Une autre chose, je n'ai aps compris pourquoi tu avais aussi fait une fonction "EffaceTout", elle sert à quoi au juste ?
Et enfin, ce que tu as fait en onglet 3 est juste génial ! J'aurai juste une petite modification à te demander (comme je ne comprends pas bien le code, je n'arrive pas à le faire tte seule ...) Je t'explique:
- Comme tu pourras le voir dans le fichier joint, j'ai ajouté en onglet 1 des numéros dans la colonne "Référence" car c'est important d'avoir ces références en sortie dans l'onglet 3. En fait dans l'onglet 3, je ne souhaite pas spécialement avoir les risques (comme c'est le cas actuellement en colonne B) mais ce qu'il me faut c'est le nom en entier des procédures (par exemple "Procédure 1", onglet 1, colonne A) et leur référence (par exemple "0.1", onglet 1, colonne B)
- en gros quand je choisi la société en A2 de l'onglet 3, il ressort en colonne B le nom entier des procédures et en colonne C leur référence

J'espère une fois de plus avoir été assez clair et je te remercie d'avance pour ton aide si précieuse

Bon après-midi

 

[Modeste]

Re : Comparaison de 2 feuilles Excel et correspondance de cellules

Re,

Pour ne pas changer, il va falloir vérifier si j'ai compris ... le plus simple est de remplacer le code précédent par celui-ci ... et de tester

J'ai modifié le séparateur dans les listes de procédures et de références: comme ces dernières contenaient des virgules, j'ai utilisé le '#' ... ça au moins, ne devrait pas être difficile à changer.
Si c'est correct, il sera toujours temps de se lancer dans les explications à ce moment

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Target.Address <> "$A$2" Then Exit Sub
    effaceTout
    If Target = "" Then Exit Sub
    Set liste = CreateObject("Scripting.dictionary")
    Set soc = Sheets("Sociétés-Risques")
    Set proc = Sheets("Procédures-Risque")
    Dim monTablo(1) As String
    numLigne = Application.Match(Target, soc.[A1:A200], 0)
    nbCol = Application.CountA(soc.[1:1])
    For Each c In soc.Cells(numLigne, 2).Resize(1, nbCol)
        If UCase(c) = "X" Then liste(soc.Cells(1, c.Column).Value) = "": trouve = True
    Next c
    If trouve Then
        cpt = 2
        For Each risk In liste.keys
            colProc = Application.Match(risk, proc.[1:1], 0)
            For Each x In proc.Cells(2, colProc).Resize(Application.CountA(proc.[A:A]), 1)
                If UCase(x) = "X" Then
                    monTablo(0) = monTablo(0) & proc.Cells(x.Row, 1) & " # "
                    monTablo(1) = monTablo(1) & proc.Cells(x.Row, 2) & " # "
                End If
            Next x
            If monTablo(0) <> "" Then monTablo(0) = Mid(monTablo(0), 1, Len(monTablo(0)) - 3)
            If monTablo(1) <> "" Then monTablo(1) = Mid(monTablo(1), 1, Len(monTablo(1)) - 3)
            Cells(cpt, 2).Resize(1, 2).Value = monTablo
            monTablo(0) = ""
            monTablo(1) = ""
            cpt = cpt + 1
        Next risk
    End If
End Sub
Sub effaceTout()
    With Sheets("Attribution")
        If .[B200].End(xlUp).Row = 1 Then Exit Sub
        .[B2].Resize(Application.CountA(.[B:B]) - 1, 2).Clear
    End With
End Sub

 

[emi06]

Re : Comparaison de 2 feuilles Excel et correspondance de cellules

Ne t'inquiète pas, tu comprends toujours bien

Bon par contre je t'avoue que je ne comprends toujours pas tout le code ^^
Je suis donc toute ouie pour des explications et/ou commentaires

Juste une dernière petite chose, est ce que ça serait possible d'avoir à la fin une procédure par ligne et non toutes les procédures sur la même ligne ? Càd comment faire pour pouvoir ajouter cette nouvelle ligne automatiquement et que les procédures s'y inscrivent directement ?

Encore merci pour ton aide

Bonne fin de journée

 

[Modeste]

Re : Comparaison de 2 feuilles Excel et correspondance de cellules

Bonsoir,

Juste une dernière petite chose, est ce que ça serait possible d'avoir à la fin une procédure par ligne et non toutes les procédures sur la même ligne ? Càd comment faire pour pouvoir ajouter cette nouvelle ligne automatiquement et que les procédures s'y inscrivent directement ?

Ben là, il y a au moins une chose dont je suis certain, c'est que, cette fois, ... je n'ai rien compris
... Ou alors, si c'est ce que je crois comprendre, on peut le qualifier de tout , sauf de "une dernière petite chose"

Et si on se posait un instant et qu'on réfléchissait? Tu m'as dit que les intitulés des risques ne t'intéressaient pas plus que ça. Or, je m'aperçois qu'il y a 3 colonnes (alors que je n'en complétais que 2 !?) Es-tu certaine que si tu listes toutes les procédures les unes en-dessous des autres, tu n'auras pas besoin de conserver le nom du risque auquel elles sont liées? D'autre part, dans tes procédures (et sans doute aussi dans les références!?) lorsqu'elles seront affichées en colonne, il risque d'y avoir des doublons, non? Ce ne sera pas génant?

Chaque "petite chose" que tu ajoutes ou modifies implique une modification du code. Si les changements se succèdent et sont plus importants que tu ne l'imagines, ça peut vite devenir une "usine à gaz". Il est donc important de bien prendre le temps avant de se lancer.

 

[emi06]

Re : Comparaison de 2 feuilles Excel et correspondance de cellules

Bonjour Modeste !

Si si tu comprends bien ce que je souhaite faire ne tkt pas

En faisant simple, ce que je veux à la toute fin, c'est d'avoir en onglet 3 et pour chaque société l'ensemble des procédures qui les concernent et leur référence. Donc en effet, l'affichage des risques ne m'est pas très utile et si on pouvait éviter les doublons ça serait génial
Voici un exemple tout simple:



Ceci dit, tu es mieux placé que moi pour savoir ce qu'est une "dernière petite chose" en VBA, du coup, je suis dsl pour toutes ces modifications de dernière minutes dont je n'avait pas énoncé la couleur avant.

Donc si pour toi, il est plus simple de laisser les risques et bah ce n'est pas bien grave, du moment que j'ai quand même l'ensemble des procédures (nom entier) et de leur référence, ligne par ligne, ça me convient très bien .
Je ne vais aps faire la difficile ^^

Encore merci à toi
Bonne journée !

 

[Modeste]

Re : Comparaison de 2 feuilles Excel et correspondance de cellules

Bonjour emi06, le fil et le forum,

Nouvelle version du code, donc (puisque j'ai forcément bien compris, cette fois ).

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Target.Address <> "$A$2" Then Exit Sub
    effaceTout
    If Target = "" Then Exit Sub
    
    Set listeRsk = CreateObject("Scripting.dictionary")
    Set listeProc = CreateObject("Scripting.dictionary")
    Set soc = Sheets("Sociétés-Risques")
    Set proc = Sheets("Procédures-Risque")
    
    numLigne = Application.Match(Target, soc.[A1:A200], 0)
    nbCol = Application.CountA(soc.[1:1])
    For Each c In soc.Cells(numLigne, 2).Resize(1, nbCol)
        If UCase(c) = "X" Then listeRsk(soc.Cells(1, c.Column).Value) = "": trouve = True
    Next c
    If trouve Then
        cpt = 2
        For Each risk In listeRsk.keys
            colProc = Application.Match(risk, proc.[1:1], 0)
            For Each x In proc.Cells(2, colProc).Resize(Application.CountA(proc.[A:A]), 1)
                If UCase(x) = "X" Then
                    listeProc(proc.Cells(x.Row, 1).Value) = proc.Cells(x.Row, 2)
                End If
            Next x
        Next risk
        [B2].Resize(listeProc.Count, 1) = Application.Transpose(listeProc.keys)
        [C2].Resize(listeProc.Count, 1) = Application.Transpose(listeProc.items)
    End If
End Sub
Sub effaceTout()
    With Sheets("Attribution")
        If .[B200].End(xlUp).Row = 1 Then Exit Sub
        .[B2].Resize(Application.CountA(.[B:B]) - 1, 2).Clear
    End With
End Sub

... à tester dans tous les sens

PS: pas ajouté de tri alphabétique ... je présume que dans le "vrai fichier" les noms des procédures seront sensiblement différents!?

 

[emi06]

Re : Comparaison de 2 feuilles Excel et correspondance de cellules

Coucou !

Effectivement le tri des procédures n'a pas de grande importance car dans le "vrai" fichier, elle sont aussi dans le désordre

Tu vois que tu as bien compris ce que je voulais, c'est juste GE-NI-AL !!!! Exactement ce que je souhaitais faire, ça fonctionne du tonnerre de Dieu ! ^^
Un travail de pro =D

En tout cas, un énorme merci à toi, pour ta patience, ta disponibilité et ta compréhension

 

[emi06]

Re : Comparaison de 2 feuilles Excel et correspondance de cellules

Bonjour !

Modeste, je reviens vers toi car après être retournée sur mon fichier que j'ai dû laisser de côté un moment, j'ai relancé la macro et le message d'erreur n°13 "Incompatibilité de type" s'affiche pour la ligne 21:

For Each x In proc.Cells(2, colProc).Resize(Application.CountA(proc.[A:A]), 1)

Il me semble que pour cette erreur, c'est le format de nombre qui est en cause, j'ai mis toutes mes feuilles en mode Standard, mais rien n'y fait !

Aurais-tu une idée du problème ?

Merci d'avance pour ton aide

Bonne journée !

 

[Modeste]

Re : Comparaison de 2 feuilles Excel et correspondance de cellules

Salut emi06,

On obtient le même message d'erreur lorsqu'un "risque" n'est pas retrouvé à l'identique dans une de tes deux feuilles!
Pour vérifier, ouvre l'Editeur VBA (clic droit sur l'onglet de la feuille "Attribution", puis visualiser le code). Positionne-toi sur la ligne colProc = Application.Match(risk, proc.[1:1], 0) et appuye sur F9: un point d'arrêt va apparaître à cet endroit, dans le code.
Bascule dans la fenêtre de ton classeur et teste avec la société qui a provoqué une erreur. La macro va s'arrêter, là où tu as placé le point d'arrêt. Dans la fenêtre de l'Editeur, appuye une fois sur F8, puis regarde dans la fenêtre des variables locales (en bas) la valeur de colProc. Si tu vois une valeur d'erreur, compare la façon dont le risque concerné est écrit, dans tes 2 feuilles.
Pour rappel, une espace à la fin du texte, un Atl+Enter, etc. sont à prendre en considération!

 

[emi06]

Re : Comparaison de 2 feuilles Excel et correspondance de cellules

Bonjour Modeste !

Effectivement c'est super important que tout soit à l'identique. Après une vérification de l'ensemble du fichier et grâce à tes conseils, le problème a été réglé

Encore merci de ton aide !

Bonne journée !

 

[emi06]

Re : Comparaison de 2 feuilles Excel et correspondance de cellules

Bonjour Modeste !

Je souhaiterai ajouter une fonctionnalité à mon fichier (qui fonctionne à merveilles au passage ). Je m'explique:
- dans l'onglet 2 "Procédure-risque", on a en colonne B les références des procédures. Pour chaque référence, j'y ai lié un lien hypertexte (adresse web) me donnant directement accès au document.
- dans l'onglet 3, grâce à ta macro, je récupère en colonneC l'ensemble des référence mais sans le lien hypertexte ajouté

Comment conserver ce lien hypertexte de l'onglet 2 à l'onglet 3 ?

Merci d'avance pour ton aide

Bon après-midi