Macro Relation entre deux tableaux Excel

Bonjour à tous les membres,

J'ai parcouru tous les sujets mais je n’ai pas trouvé des solutions à mon problème donc je vais vous le soumettre.

Je dois travailler sur deux fichiers : un planning d'audits qui place les établissements et les plages d'audits de ces établissements; un tableau récapitulatif montrant les premiers jours d'audits ASNO et AST des différents établissements. Je dois trouver une macro qui met en relation ces deux fichiers.

L'objectif de cette macro est de : pouvoir placer les dates des premiers jours d'audits de ces établissements que ce soit de l'ASNO et de l'AST sous les conditions suivantes : il ne faut retenir que les entités colorés en Gras Rouge (ASNO), en Gras Noir (AST), en normal noir (AST) et en normal rouge (ASNO). Ces dates doivent être placées dans le tableau du deuxième feuillet dans ces colonnes : Prévision du prochain Audit ASNO et Prévision du prochain Audit AST.

En gros cette macro doit être capable de capable de parcourir le planning, prendre les établissements corresponds aux conditions établis plus hauts et placer les premier jours d'audits dans les colonnes correspondantes du tableau du deuxième feuillet.

Le fichier est trop gros pour que je vous puisse vous envoyer là donc je vous envoie par mail pour ceux qui peuvent m’aider.

Merci à ceux qui peuvent m'aider et à bientôt.

Salut et bienvenue sur le Forum,

Si ton fichier est trop gros, essaie de la comprimer ou de supprimer une partie des données, des feuilles, des MFC inutiles, etc. Sinon essaie de passer par le site http://cjoint.com/

Cordialement.

Bonjour ezt Merci Yvouille,

Je n'étais pas revenu ici depuis un mois parce que j'avais trop une solution qui au final avait pas réussi.

Donc comme m'a suggeré Yvouille, je vous envoie le lien internet ou est joint mon dossier.

Merci d'avance pour vos réponses et voici le lien :

Salut,

J’aurais trouvé sympa que tu reviennes sur ce fil dire merci pour mon aide gratuite avant d’en être obligé

Ta demande est quand même assez brouillon ; tu ne fais pas les noms des feuilles, les références de tes plages de cellules ou de tes cellules individuelles, etc. Par exemple lorsque tu parles du ‘deuxième feuillet’, je ne sais pas de quoi tu parles.

Peux-tu alors me fournir un ou deux exemples précis de ce que tu voudrais voir reporter ? Et autre question : à quel moment doivent se faire ces reports ? Lors de la sélection d’une feuille ? Lorsque tu le décides en cliquant sur un bouton ? Autres évènement ?

Tu as également placé un mot de passe sur ton explorateur VBA : ainsi je ne peux rien faire

A te relire.

Ok Bonjour,

Pour faire plus simple, dans mon fichier, j'ai deux feuillets : un feuillet qui montre le planning des audits et l'autre qui montre la liste des etablissements audités avec les dates des derniers audits/les dates des prochaines prévisions audits et les dates des programmations des audits. Les etablissements qui doivent etre pris en compte sont les établissements en rouge gras, rouge normal, bleu normal, bleu gras.

Je dois remplir le deuxieme feuillet automatiquement en remplissant la colonne 16 pour les Rouge et la colonne 19 pour les bleuw

- Par exemple

( sur le planning ) l'ECT de Toulouse est programmé le 13 janv 2014, je dois mettre cette date sur la bonne ligne à la colonne 16 ( tableau du 2e feuillet)

(sur le planning ) l'EIC Paris Champagne Ardenne est programmé le 2 sept 2013, je dois retrouver cette date à la colonne 16 sur la ligne correspondante du tableau du 2e feuillet.

Pareil pour TM Chambery en bleu normal qui doit avoir sa date à la colonne 19 sur la ligne correspondante et ESBE Grand Est Lyon en bleu gras qui doit avoir sa sate à la colonne 19 sur la ligne correspondante.

J'espère que c'est plus clair là et si c'est pas plus clair, voila mon mail rud_ngongus@yahoo.fr

Merci d'avance.

Salut,

Tes excuses pour m’avoir laissé tomber un mois sont toutes acceptées.

Dans ton message du 23 janvier tu parlais de cellules rouges et noires alors que tu parles maintenant de cellules rouges et bleues !!! Comme il n’y a pas de cellules bleues dans ton fichier, je n’ai traité pour l’instant que les cellules rouges.

De plus je suis au chalet et j’ai dû transformer ton fichier Excel >=2007 en fichier Excel 2003. Je n’ai donc que 256 colonnes à disposition et il semble que tu en as plus. Mais comme la cellule L3C256 de la feuille ‘Planning’ est le 6 août 2014, je pense que ça devrait jouer.

Mais encore, tu joues toujours à cache-cache avec tes macros en ne me fournissant pas le mot de passe et – afin de pouvoir placer mes macros à moi, j’ai dû dupliquer les deux feuilles nécessaires dans un nouveau fichier.

Donc si dans la plage L8C5:L107XC256 de la feuille ‘Planning’ tu as un texte en rouge (sans importance s’il est en gras ou non), la date correspondante dans la ligne 3 est reprise dans la colonne 16 de la feuille ‘Ets audités par DAS Projet 2014’. Ma macro est lancée par le nouveau bouton mis en place vers la cellule A1 de la feuille ‘Planning’.

La colonne 16 de la feuille ‘Ets audités par DAS Projet 2014’ est à chaque fois vidée avant la réactualisation.

Tu as encore un peu de peine avec les références précises aux objets Excel, mais je ne désespère pas.

Si tu me fournis un fichier avec des textes en bleu, on pourra voir la suite.

Amicalement.

Merci beaucoup pour ton aide et le temps que tu y consacre, je te remercie beaucoup et encore désolé pour mon silence.

Ça m'aide vraiment beaucoup là mais je pense que je n’étais pas ete très clair et je m'en excuse.

Pour faire encore beaucoup plus simple, lorsque la macro parcourt le tableau, il doit prendre en compte trois types d'établissements : les établissements NOTES en rouge normal et gras et les établissements NOTES en bleu.

Les dates des débuts d'audits des établissements NOTES en Rouge doivent être placées sur la colonne 16 du deuxième feuillet ( c'est ce que tu as bien résolus ) mais il manque le placement des dates des débuts des établissements NOTES en Bleu qui doivent être placées sur la colonne 19 du deuxieme feuillet.

PS : Les établissements NOTES en bleu sont sur la ligne 37 à 40 du planning.

Pour que je puisse comprendre le code, j'aimerais que tu mettes les commentaires sur les lignes de codes de la macro, je suis assez moyen en Macro donc j'ai compris ta macro à moitié. Merci d'avance pour ton aide.

Salut,

Tu as deux sortes de cellules sur ta feuille ‘Planning’.

A) Des cellules qui ont comme couleur de base de la police une couleur bien définie et reconnaissable par macro par l’instruction ‘ColorIndex’.

Ainsi par exemple ta cellule L16C84 de la feuille ‘Planning’ a une couleur de base rouge qui correspond à la ‘ColorIndex’ numéro 3. Je peux donc ressortir cette cellule par macro.

Mais B) tu as d’autres cellules sur cette même feuille qui ont une couleur de base « Automatique » et pour lesquelles tu as placé des MFC (Mise en forme conditionnelle) qui transforment cette couleur de base en couleur ‘conditionnelles. Par exemple pour la cellule L37C19, tu as une couleur de base ‘Automatique’ et une mise en forme conditionnelle qui transforme la couleur de la police dans ce bleu bizarre si la valeur de la cellule est plus grande que zéro. Ainsi, lorsque je recherche par macro – avec l’instruction ‘ColorIndex’ la couleur de la police de cette cellule, elle trouve qu’il s’agit de la couleur 0 (‘Couleur automatique’ = neutre = rien).

Je ne sais pas comment rechercher par macro la couleur d’une cellule découlant d’une MFC. Alors soit je ne peux pas t’aider plus, soit tu es d’accord de modifier ton fichier et d’affecter aux cellules qui ont une MFC une couleur de base ‘ColorIndex’ bleue. Le cas échéant, je t’indiquerai comment.

Tes MFC sont d’ailleurs selon moi aberrantes puisque la formule de la cellule L37C19 est par exemple ‘La valeur de la cellule > 0’ ; donc soit la cellule est vide et l’écriture est noire (mais ça nous fait une belle jambe), soit il y a un texte écrit et la couleur de la police est ce bleu indéfinissable !!!!! Ou est-ce que tu as parfois des nombres négatifs dans cette cellule qui justifieraient cette MFC ???

A te relire.

Bonjour, merci pour ton temps précieux,

Je pense comme toi qu'il faut modifier ton fichier et d’affecter aux cellules qui ont une MFC une couleur de base ‘ColorIndex’ bleue pour pouvoir faire comme pour la couleur rouge.

Je t'ai encore dis une connerie mais les établissements en bleu sont de la ligne 33 à 40 du Planning et non sur la 37 à 40.

Merci d'avance et bon début de semaine.

Salut,

rudylandry a écrit :

Je pense comme toi qu'il faut modifier ton fichier

rudylandry a écrit :

Je t'ai encore dis une connerie mais les établissements en bleu sont de la ligne 33 à 40 du Planning et non sur la 37 à 40.

Concernant cette écriture bleue, j’ai fait quelques essais et je me suis rendu compte que si le rouge sélectionnable dans la palette de ‘Couleurs standard’ visible ci-dessous est une couleur correspondant au code VBA ‘ColorIndex’ numéro 3, par contre aucune des couleurs bleues présentes au même endroit n’ont de correspondance ‘ColorIndex’ exacte. Apparemment elles ont des correspondances approximatives, mais je trouve trop risqué de partir sur cette base.

Je te prierais donc – si tu désires remplacer tes MFC par des couleurs fixes – d’utiliser le bleu à disposition dans la palette visible ci-dessous (dans l’hexagone blanc, après avoir cliqué sur 'Autres couleurs' dans l'image du dessus) ; ce bleu correspond effectivement exactement à la ‘ColorIndex’ numéro 5.

A réception de ton fichier corrigé, je peux voir pour terminer ce code.

A moins que – puisque tu me le demandes – tu t’en sortes avec les commentaires placés dans le code du fichier ci-joint. Mais ce n’est pas facile de savoir si l’on en dit trop ou pas assez. Si jamais reviens avec des questions plus précises sur les passages que tu ne comprends pas. Dans mon dernier code, j’avais laissé des instructions inscrites en cours de préparation et qui étaient finalement inutiles ; je les ai effacées ici.

Amicalement.

Merci pour tout

J'ai enleve les MFC et remplacer par la couleur bleue comme tu m'as dis de faire et voilà le lien :

J'aimerais comprendre comment tu fais pour savoir que la "ColorIndex" du Rouge est 3 et celle du Bleu est 5.

Merci encore pour tout et bonne journée.

Re-bonjour,

Voici un code modifié qui tient également compte des textes en bleu.

Attention, tu as parfois des textes sur la feuille ‘Planning’ (par exemple ‘ILOG Travaux Ile de France’) qui n’ont pas de correspondance exacte sur la feuille ‘Ets …….’ (sur laquelle il est indiqué ‘ILOG Travaux Ile-de-France’ avec des traits d’union !!) ; dans un tel cas, Excel n’arrivera jamais à faire le lien entre ces deux textes. Attention également aux espaces inutiles d'un côté ou de l'autre qui peuvent empêcher la correspondance.

Comme tu as toujours tes mots de passe sur tes macros – et que je ne retrouve pas le message dans lequel tu me le fournissais – j’ai copié ta nouvelle feuille ‘Planning’ dans le fichier ‘essai couleur’.

Pour trouver les numéros des ColorIndex, tu peux te bricoler un petit fichier comme celui-ci-joint. Tu remarqueras alors qu’il y a 56 ColorIndex et si tu cliques sur une couleur nouvellement créée, tu peux aller voir dans la palette des couleurs à quoi elle correspond.

D'autres systèmes de traiter les couleurs en VBA existent, certains permettant d'obtenir des couleurs beaucoup plus précises. Mais pour le genre de macros dont tu as besoin, l'utilisation de ColorIndex facilite les choses.

Cordialement.

Bonjour,

Mon boss m'a donné un autre projet, la j'ai essayé de le readapter avec ce que tu as fais avant, ca marchait un moment mais la ca ne marche plus, ca me sort autre chose carrement.

Comme avant, les établissements en rouge, leurs dates doivent etre mis sur la colonne 17 du feuillet " Ets audités par DAS Projet 2014" et pour les etablissements en bleu, les dates doivent etre mis sur la colonne 22 du feuillet "Ets audités par DAS Projet 2014"

Ps : Il faut voir le cas ou en balayant le planning, il ne prend pas en compte les établissements mal orthographiés sur le planning, il faut mettre une alerte je pense.

Pour la relation entre le feuillet "planning" et "campagne AST 2015", il faut tenir compte que des établissements en noirs et en se basant sur la colonne 3 de "Campagne AST 2015", il faut pouvoir mettre des dates sur la colonne 7 et des périodes sur la colonne 9.

Voila le lien du fichier :

et je te rappelle le code 090461.

Merci d'avance.

Salut,

J'ai d'abord vu ton texte ci-dessous :

rudylandry a écrit :

Comme avant, les établissements en rouge, leurs dates doivent etre mis sur la colonne 17 du feuillet " Ets audités par DAS Projet 2014" et pour les etablissements en bleu, les dates doivent etre mis sur la colonne 22 du feuillet "Ets audités par DAS Projet 2014"

Puis au moment de poster, j'ai encore vu le texte complémentaire

rudylandry a écrit :

Ps : Il faut voir le cas ou en balayant le planning, il ne prend pas en compte les établissements mal orthographiés sur le planning, il faut mettre une alerte je pense.

Pour la relation entre le feuillet "planning" et "campagne AST 2015", il faut tenir compte que des établissements en noirs et en se basant sur la colonne 3 de "Campagne AST 2015", il faut pouvoir mettre des dates sur la colonne 7 et des périodes sur la colonne 9.

Cordialement.

Bonjour,

Merci pour ta réponse, avant de commencer à reexpliquer ce que je t'ai dis avant, j'ai mis en haut .Range("Q5:Q" & Rows.Count).ClearContents, .Range("V5:V" & Rows.Count).ClearContents parce que dans le tableau "Ets audités par DAS Projet 2014", les écrits commencent à la 5eme ligne et non à la 4eme ligne ou j'ai mal compris.

Je te reexplique en te disant que quand la macro parcourt le planning, il se peut qu'il ne recupere pas tous les dates d'audits parce que certains etablissements sont mal notés ou pas inscrits sur le feuillet "Ets audités par DAS Projet 2014", comment faire pour qu'ils puissent récuperer tous ces établissements là.

Puis je t'envoie un deuxieme lien avec un fichier joint ou il est encore question de reporter les débuts des audits mais là en fait c'est plus détaillé.

• Il faudrait prendre les dates des établissements écrits en rouge avec un fond bleu et les mettre à la 16eme colonne du feuillet "Tableau_Audits (EX).
• Il faudrait prendre les dates des établissements écrits en rouge avec un fond violet et les mettre à la 16eme colonne du feuillet "Tableau_Audits (T).
• Il faudrait prendre les dates des établissements écrits en rouge avec un fond vert et les mettre à la 16eme colonne du feuillet "Tableau_Audits (M).
• Il faudrait prendre les dates des établissements écrits en rouge avec un fond marron et les mettre à la 16eme colonne du feuillet "Tableau_Audits (V).
• Il faudrait prendre les dates des établissements écrits en bleu et les mettre à la 16eme colonne du feuillet "Tableau_Audits (ASP).

Je t'envoie le lien avec le fichier ci joint :

Merci d'avance et bonne après midi.

Salut,

Ton fichier est assez mal foutu, ta feuille ‘Planning’ est quand même assez fouillis et il faut vraiment s’accrocher afin d’avoir envie de continuer.

Ensuite tu as environ 10 modules inutiles et j’ai beau les supprimer à chaque fois que je peux, ils réapparaissent avec tes nouvelles versions.

Puis tu m’as posé des questions au sujet de l’utilisation des couleurs et je me suis fendu d’un fichier explicatif. L’as-tu consulté ? Dans ton dernier fichier, tu as semble-t-il introduit des couleurs de fonds, mais sans te soucier de mes remarques au sujet de l’utilisation des ColorIndex, non ? Alors merci soit de m’indiquer quelles ColorIndex tu as utilisées, soit de modifier tes couleurs de fond afin de pouvoir m'en indiquer le numéro ColorIndex.

Concernant tes noms mal orthographiés, je ne vois qu’une solution : tous les repasser en revue manuellement.

Pour les espaces inutiles devant ou derrière les noms, tu pourrais une fois nettoyer ton fichier. Tu sélectionnes les feuilles à nettoyer les unes après les autres et tu lances le code ci-après sur chacune d’elle. Ca prend quand même de 1 à 5 minutes par feuille. Mais pense quand même qu’un espace de trop et ta référence ne sera jamais trouvée.

Sub ddd()
Dim Cellule As Range

For Each Cellule In ActiveSheet.UsedRange
    Cellule = Trim(Cellule)
Next

End Sub

A te relire.

Hello,

merci pour tes réponses, justement je n'ai pas mis ces couleurs de fonds sur le planning, on m'a juste demandé de faire macro pour les différents cas que je t'ai cité precedemment. Ils sont mis de facon automatique ou pas ? ET comme pour les textes écrits en couleur, il faut aussi utiliser la palette de couleur pour mettre la couleur au fond des cellules ? pour le cas du fond des cellules, comment on fait ?

Merci d'avance.

Salut,

Je suis aujourd’hui sur un PC germanophone et ça ne va pas faciliter les choses pour mes explications.

Commençons par le début : plus on avance, plus je me rends compte que tu ne connais pas ton fichier ! Je présume donc que ce n’est pas toi qui l’as fait !

Selon ce que j’ai pu constater, il n’y a pas (ou plus) de mise en forme conditionnelle (MFC) pour les fonds des cellules que tu me demandes de traiter. Par contre il y a trois systèmes différents de coloriage.

Système 1

Pour la cellule L28C29 (Z28S29 en allemand), on a par exemple apparemment utilisé la palette de couleur nouvellement créée avec les versions Excel 2007 et suivante (en allemand, Designfarben).

Système 2

Pour la cellule voisine L28C26 (Z28S26 en allemand) on a apparemment utilisé la palette de couleur « Standard » (Dans l’image ci-dessus tu cliques en plus sur « Weitere Farben », probablement « Autres couleurs » en français).

Système 3

Pour d’autres cellules, comme la Z37S22 (L37C22 en français) on a encore utilisé un autre système et il faudrait utiliser le système RGB (Red Green Blue) pour retrouver cette couleur par un code VBA.

Ceci est beaucoup trop compliqué selon moi.

En ce qui concerne les couleurs des écritures, je t’avais expliqué que l’on allait travailler avec les ColorIndex uniquement, ce qui facilite grandement l’écriture des macros.

Alors je te prierais maintenant de choisir parmi les couleurs que je t’ai fournies le 25 février (mon fichier fourni nommé 'Essai ColorIndex.xls') pour tes fonds de cellule et sur cette base je verrai si je peux transformer mon code pour répondre à tes nouveaux besoin.

Pour utiliser une couleur ColorIndex dans une cellule, tu sélectionnes la couleur dans mon fichier indiqué ci-dessus, tu vas vois dans la palette de couleurs à quelle couleur ça correspond (revoir à ce sujet mon explication du 24 février pour le bleu ColorIndex numéro 5) puis tu utilises cette couleur comme couleur de fond de ton fichier ‘Planning ……’.

Si tout ceci te semble bien compliqué, tu peux éventuellement indiquer ce fil comme résolu et ouvrir un autre fil afin de voir si un autre membre à une meilleure idée.

Cordialement.

Bonjour,

J'ai remplacé les couleurs au fond des cellules dans le planning et pour les etablissements écrits en bleu, jai remplacé les couleurs par la tablette de couleurs mais le report des dates pour le feuillet 'Ets audités par DAS Projet 2014' sur la colonne 22 ne se fait pas.

Pour le report des autres dates dans les autres feuillets (Tableau_Audits (EX)/Tableau_Audits (T)/Tableau_Audits (M)/Tableau_Audits (V)), voila les conditions ci - dessous, il faut tenir compte des établissements écrits en gras pour chaque cas et faire attention au niveau des colonnes de ses feuillets il y'a les dates qui doivent correspondre. Voici les differents cas :

• Il faudrait prendre les dates des établissements écrits en rouge avec un fond bleu et les mettre à la 16eme colonne du feuillet "Tableau_Audits (EX).
• Il faudrait prendre les dates des établissements écrits en rouge avec un fond violet et les mettre à la 16eme colonne du feuillet "Tableau_Audits (T).
• Il faudrait prendre les dates des établissements écrits en rouge avec un fond vert et les mettre à la 16eme colonne du feuillet "Tableau_Audits (M).
• Il faudrait prendre les dates des établissements écrits en rouge avec un fond marron et les mettre à la 16eme colonne du feuillet "Tableau_Audits (V).

Et voici le lien :

Merci d'avance.

Salut,

Merci plutôt à toi pour ta réponse

Je t’ai fait remarquer que les modules VBA inutiles que je nettoyais dans mes fichiers réapparaissait la fois d’après. Tu sembles ne pas avoir tenu compte de ma remarque et si ces modules réapparaissent encore une fois, je vais probablement cesser de t’aider.

Le bouton que je mets à chaque fois en place sur la feuille ‘Planning’, tout en haut à gauche, disparait également. C’est rageant.

Il y a toujours ton maudit mot de passe en place pour l’explorateur VBA et je dois à chaque fois le rechercher dans tes messages ; ne pourrais-tu pas le supprimer ? D’ailleurs à quoi sert-il ? Qui pourrait bien aller modifier tes codes ?

Je t’avais demandé de me nommer les numéros des couleurs que tu utilisais nouvellement. Comme tu ne l’a pas fait, j’ai dû les rechercher moi-même.

Il n’était pas question de tenir compte des écritures en gras ou non. Je présume donc que c’est uniquement pour que je remarque ces cellules plus facilement pour préparer mon code que tu indiques cela et non pas pour que je tienne compte de cette particularité en plus, ce que je n’ai d’ailleurs pas fait.

Si tu changes à tout bout de champs mes macros, je ne sais plus où j’en suis. Ma macro ‘aa’ a été remplacée par ta Macro9, c’est énervant.

rudylandry a écrit :

..... et pour les etablissements écrits en bleu, jai remplacé les couleurs par la tablette de couleurs mais le report des dates pour le feuillet 'Ets audités par DAS Projet 2014' sur la colonne 22 ne se fait pas.

rudylandry a écrit :

Pour le report des autres dates dans les autres feuillets (Tableau_Audits (EX)/Tableau_Audits (T)/Tableau_Audits (M)/Tableau_Audits (V)), voila les conditions ci - dessous,

Comme pour mon autre macro, les colonnes P des 4 feuilles ‘Tableau_Audits’ traitées sont vidées à chaque fois en début de macro. Le code actuel dure environ 10 secondes un mon PC professionnel et environ 30 secondes sur mon PC privé.

Je te rappelle que plus tu attends entre deux messages, plus je dois me replonger dans ton travail pour m’en souvenir.

A te relire.