Macro MFC, Appliquer puis eliminer en gardant formats

Bonjour à tous,

Ceci étant mon premier post, je me présente rapidement. Je suis contrôleur de gestion, chargé du Reporting pour une grande boîte d'assurance au Mexique. Je bosse sous Excel 2010 et bien évidemment j'utilise des formules en espagnol (normalement cela ne devrai pas avoir d'influence sur mon problème).

Je vous donne le contexte du problème:

Je suis propriétaire d'un modèle d'informations financières que je dois retravailler tous les deux mois pour présenter les chiffres actualisés. Le classeur comporte 63 de feuilles environ (1 titre, 1 input, 1 paramétrage, et 60 outputs).

J'ai depuis plusieurs mois "automatiser" le fichier pour qu'en fonction des paramètres que je sélectionne, celui-ci structure correctement les feuilles d'outputs. Concrètement j'ai des colonnes qui présentent soit des périodes, soit des comparatifs. En fonction d'une valeur sur une plage de la feuille de paramètres, des formats conditionnels s'appliquent a chaque colonnes.

La MFC que j'utilise est donc du type :

  • SI(!Parametre!A1="Période",1,0) ==> format numérique
  • SI(!Parametre!A1="Comparatifs",1,0) ==> format %
  • SI(!Parametre!A1="Complétion",1,0) ==> format %

Le format à appliquer n'est pas toujours exactement le même d'où les trois conditions, de plus les plages sur lesquels il s'applique sont discontinuent. (ex: si "Période", s'applique en numérique pour A3:A8 et A10:A15 et s'applique en % en A9,A16:A25)

Chaque feuilles de sorties compte environ 25 colonnes.

Jusque-là pas de problème et j'étais même plutôt content de moi vu que l'automatisation du modèle permet de ne plus jamais avoir à changer les références des formules.

Le problème est que plusieurs de mes "clients" ont besoin soit de copier/coller les informations présentes sur ces feuilles de sorties (outputs) soit de les sélectionner pour voir les sommes automatiques dans le coin en bas droite d’Excel. Dans le premier cas le copié/collé fonctionne très mal et les formats pourrissent la présentation de l’information. Dans le second cas, malgré le fait de sélectionner deux pourcentages la somme s’affichent en numérique et inversement.

J’ai donc deux solutions qui s’offrent a moi :

1) Revenir à l’ancienne version qui demande une attention particulière pour les chaque colonne de chaque feuilles (je refuse pour le moment de céder à cette possibilité)

2) A la création d’une nouvelle version du fichier :

A. Lancer une macro qui me permette d’appliquer le format conditionnels sur toutes les feuilles en base à l’archive que j’ai créé (le nom des feuilles ne changent jamais)

B. Paramétrer mes feuilles comme j’en ai l’habitude

C. Lancer une macro qui élimine les règles de formats conditionnels mais qui garde le format tel qu’il était appliqué avec les conditions.

D. Sauvegarder la nouvelle version de l’archive sans formats conditionnels

Dans ce dernier cas, je garde évidement mon fichier « maitre » ou les conditions sont présentes pour que la première macro puisse réappliquer ces format sur la nouvelle version de l’archive ou les conditions ne sont plus présentes.

Comme vous l’aurez sans doute devinez, j’aimerai votre aide pour la solution N°2 !!

J’espère ne pas avoir été trop confus dans mes explications et je sais que j’aurais pu être plus bref mais ne pouvant pas vous passer un exemple de 300ko, j’ai préféré vous donner une explication la plus détaillée possible.

Je vous remercie d’avance pour vos comentaires ainsi que vos idées ou solutions que vous pourrez me fournir.

Saludos !

Je me permet de faire remonter le sujet pour voir si quelqu'un peut m'aider a trouver une solution...

Merci d'avance

Bonjour,

Récupérer le format conditionnel n'est pas une très bonne idée, c'est assez complexe.

Le mieux est sans doute d'appliquer le format sur les cellules après analyse des conditions.

Exemple pour récupérer la couleur appliquée (adapter pour récupérer le format) : http://www.cpearson.com/excel/CFColors.htm

Tu ne dis pas si tu pratiques vba et si tu veux des pistes ou au contraire un soutien plus profond.

Si c'est le 2nd cas il faudrait un fichier avec l'essentiel pour le problème. Mise en page respectée, les conditions, plages et formats (exhaustif), comment reconnaitre les onglets concernés etc si tu veux que qq'un se penche dessus.

eric

Salut Eric,

Merci pour ta réponse.

Pour te répondre, je ne suis pas du tout familier avec VBA. Mes seuls capacités sont de lire le code et pouvoir le modifier pour adapter celui-ci à mes besoins quand ce n'est pas trop compliqué (genre changer le nom des onglets, rajouter des lignes similaires....)

Pour revenir a mon probleme, je crois que j'ai besoin d'un support assez profond de votre part

Je crois comprendre que rapatrier les MFC depuis un fichier maitre n'est pas la meilleure idée qui soit mais je peine a trouver d'autres solutions...

J'ai créer un exemple de mon modèle avec trois feuilles de sorties ou j'ai recopié les format pour que vous puissiez voir les conditions que j'utilise. J'ai aussi mis quelques chiffres pour que vous puissiez vous rendre compte des soucis qu'expériencent les utilisateurs finaux de mon fichier. C'est-à-dire les somme automatique de pourcentage qui sortent en valeur numerique ou inversement et le format qui se perd lors d'un copié collé.

J'essaye de joindre le fichier mais cela n'a pas l'air de fonctionner (je pense que cela vient du reseau du boulot qui m'empeche d'Upload donc si quelqu'un veut m'aider a le publier ce sera avec plaisir... je sais que j'abuse )

Pour résumer j'ai maintenant deux idée:

l'orignale:

  • Avoir une macro pour rapatrier les MFC sur mon fichier avant de le parametrer
  • Faire mon parametrage
  • Lancer une macro pour eliminer les MFC

la nouvelle:

- Avoir une macro qui me permette de figer les format determinée par les MFC. C'est-à-dire qu'il n'y est plus de format sous-jacent (les format d'origine sous la MFC sont ceux qui posent probleme pour le copié collé ou les sommes automatiques). Puis lancer cette même macro pour défiger les format et retrouver les conditions ce qui me permetraient de paramêtrer de nouveau mon fichier avant de figé une noouvelle fois les format.

J'espère que vous pourrez m'aider. En tout je vous remercie de nouveau !

EDIT: J'ai reussi a joindre le fichier finalement donc voila...

Saludos,

Pas trop le temps en ce moment, il faudra patienter un peu si personne d'autre ne s'y colle.

En H2, J2, etc c'est "Periodo" ou une formule qui retourne cette valeur ?

Si c'est une formule il la faudrait ainsi que tous ses antécédents.

Les onglets concernés commencent tous par "TOTAL" ?

eric

Salut Eric,

Merci pour le suivi. Je te mets le fichier actualisé avec toutes les formules que j'utilise.

J'explique:

  • Onglet Referencias = Onglet utilisé pour le paramétrage des outputs (liste déroulante des périodes à présenter & comparer, formules de références)
  • Onglets Inputs = Périodes avec les chiffres de la boites, Colonne EG sert de référence pour le rapatriement dans les outputs.
  • Onglets Total P&C= Output avec exemples des formules que j'utilise: lignes 1 à 4, références de paramétrage rapatriées de l'onglet Référencias. Ligne 12 et 16 deux formules différentes pour afficher soit le chiffre (de la période x) soit le comparatif (x vs y).

Les formules sont en espagnols mais je pense qu'en ouvrant le fichier sur une machine francophone, les formules devraient être avec une nomenclature française.

J'ai une 50 d'onglets "Outputs" avec des noms tous différents et non il ne commence pas tous par total.

Je ne suis pas si pressé que ça car mon boss vient de me dire qu'on fera avec le problème pour cette fois mais qu'on doit le corriger pour la prochaine période de Reporting d'ici un mois environ...

Je pars en vacances (2jours) ce soir mais j'essayerai de suivre le fil jusqu'au weekend si il y a besoin de précisions.

Un grand merci

Bonjour,

Ca n'a pas l'air monstrueux, juste un format nombre à appliquer sur une seule condition de déclenchement.

Par contre tu disais

Le format à appliquer n'est pas toujours exactement le même d'où les trois conditions, de plus les plages sur lesquels il s'applique sont discontinuent. (ex: si "Période", s'applique en numérique pour A3:A8 et A10:A15 et s'applique en % en A9,A16:A25)

Peux-tu donner les lignes réelles qui ont l'air d'être différentes (débuts en 11 et 19 ?).

Et la feuille 'Total Salud' a l'air d'être une exception. Lister toutes les exception et les plages concernées.

Chaque feuilles de sorties compte environ 25 colonnes.

Peut-on se baser sur la dernière cellule remplie de Referencias!2:2 et considérer toutes les colonnes paires de H à celle-ci ?

eric

suite...

Bon, j'ai fait avec ce que j'avais.

Le principe et les grandes lignes sont là.

Je n'ai pas l'impression que toutes les plages soient identiques sur toutes les feuilles, ni sur toutes les colonnes comme tu disais.

Il faudra sûrement adapter et compléter à la réalité.

Clic-droit sur le nom d'onglet 'Referencias' et choisir 'visualiser le code'.

Le code est exécuté sur changement d'une cellule (et une seule, pas de collé sur plusieurs à la fois) H2, J2, etc de cette feuille et met à jour cette colonne sur les autres feuilles concernées.

Je pense que tu comprendras la logique en lisant le code, je l'ai commenté.

Il faut supprimer les formats conditionnels (qui prennent le dessus) pour voir le résultat.

Conserver l'extension .xlsm pour les macros.

eric

Salut Eric,

Merci encore pour ta réponse et le suivi. J'ai malheuresement beaucoup beaucoup de boulot et je n'avais même pas vu que tu m'avais si bien répondu.

Je vais regarder le code et faire de mon mieux pour le comprendre et si possible l'adapter. On finis la cloture du premier trimestre le 15 avril donc si je n'ai pas répondu avant, excuse moi.

Quand j'aurai pu étudier un peu ton code, je te mets mes commentaires ainsi que les réponses a tes questions sur les onglets et plages concernées.

Merci pour tout ! Je suis impressioné par les gens comme toi et comme beaucoup du fofo qui donne de leur temps pour aider des inconus !

Salut Eric,

D'esol'e de revenir vers toi aussi tard.

J'ai pris une grosse semaine de retard au boulot donc je me met a peine sur le code. Merci pour les indications incluses, je trouve ca très utile et cela me permet d'élever un peu mon niveau VBA.

Je crois comprendre que la macro me permet d'appliquer des formats sans utiliser les formats conditionel. Je n'aurais donc pas a lancer la macro pour appliquer des formats conditionel puis les "desactiver". Cette macro formatera automatiquement mes colonnes en fonctions de la valeur cible. Cest beaucoup plus sensé et simple que ce que j'avais imaginer...(merci!).

Je comprends pourquoi tu disais qu'il te manquait certaines données et que les plages pouvaient différer. C'est bien le cas et le fichier est un peu plus complexe que ce que j'ai bien voulu te laisser entendre (je voulais pas non plus abuser en complexité). Je suis en train de modifier le code en ajoutant toutes les plages en fonction des bloques de feuilles qui different en structures.

J'ai defini:

  • 4 plages pour le bloc1 - 1 onglet
  • 2 plages pour le bloc2 - 31 onglets
  • 1 plage pour le bloc3 - 31 onglets
  • 2 plages pour le bloc4 - 3 onglets

J'ai aussi rajouter deux formats en plus des "entiers" et des "pourcentages". Je crois que j'en ai bientot fini avec les "definitions". Demain je m'occupe de la partie "fonctionelle" du code et je suis sur que je vais galerer un peu plus mais je te tiens au courant rapidemment sur mon evolution.

Pendant que j'y suis, j'ai defini toutes plages de la meme manière que ce que tu as fais, c'est à dire en prenant comme référence la colomme H : Par contre je ne vois pas trop quelle fonction me permetra d'appliquer la condition sur ces meme lignes mais sur des colonnes differentes. Cela vient surement dans la dernière partie du code mais c'est ce qui m'a frappé en regardant les differentes fonctions que tu utilises (bien que je comprenne pas tout, evidemment...).

Merci pour tout, je m'y remet

Edit:

Pour repondre au mieux a tes questions précédentes, je te passe le noms des ongles ainsi que les pagles exactes par bloc d'onglets:

Bloc0 = Ne doit pas rentrer en compte dans la macro

  • Instrucciones
  • Referencias
  • Input

Bloc1 = 1 onglet, 4 plages

- STIC

Bloc2 = 31 onglets, 2 plages

  • Total P&C
  • P&C Perso
  • P&C Com
  • Total Autos
  • Autos Ind
  • Autos Com
  • Autos Colectiv
  • Autos Flot
  • Total Salud + AP
  • Total Salud + AP Ind
  • Total Salud + AP Com
  • Total Salud
  • Salud Ind
  • Salud Com
  • Total Acc Pers
  • Acc Pers Ind
  • Acc Pers Com
  • Total Daños
  • Property
  • Liability
  • Marine
  • Others
  • FREAS
  • HSBC Total
  • HSBC Autos
  • HSBC Colectivas
  • HSBC Flotillas
  • HSBC Daños
  • HSBC Property
  • HSBC Liability
  • HSBC Others

Bloc3 = 31 onglets, 1 plages

  • Total P&C ($)
  • P&C Perso ($)
  • P&C Com ($)
  • Total Autos ($)
  • Autos Ind ($)
  • Autos Com ($)
  • Autos Colectiv ($)
  • Autos Flot ($)
  • Total Salud + AP ($)
  • Total Salud + AP Ind ($)
  • Total Salud + AP Com ($)
  • Total Salud ($)
  • Salud Ind ($)
  • Salud Com ($)
  • Total Acc Pers ($)
  • Acc Pers Ind ($)
  • Acc Pers Com ($)
  • Total Daños ($)
  • Property ($)
  • Liability ($)
  • Marine ($)
  • Others ($)
  • FREAS ($)
  • HSBC Total ($)
  • HSBC Autos ($)
  • HSBC Colectivas ($)
  • HSBC Flotillas ($)
  • HSBC Daños ($)
  • HSBC Property ($)
  • HSBC Liability ($)
  • HSBC Others ($)
  • HSBC Others($)

Bloc4 = 3 onglets, 2 plages

  • Total L&S ($)
  • L&S Ind ($)
  • L&S Com ($)

Plages Bloc1:

"$H$16,$H$20:$H$30,$H$45:$H$47,$H$56:$H$58,$H$62:$H$64,$H$70:$H$82,$H$86:$H$88,$H$92:$H$94"

"$H$99:$H$107"

"$H$109:$H$115"

"$H$18,$H$49:$H$54,$H$60,$H$66:$H$68,$H$84,$H$90"

Plages Bloc2:

"$H$72:$H$90,$H$12:$H$16"

"$H$20:$H$71,$H$95,$H$97,$H$99,$H$101"

Plage Bloc3:

"$H$11:$H$17,$H$19:$H$91"

Plages Bloc4:

"$H$12:$H$16,$H$20,$H$25:$H$29,$H$32:$H$51,$H$54:$H$58"

"$H$18,$H$30,$H$52"

Bonjour,

Oulaaa, c'est un peu vieux, j'ai tout oublié...

Pendant que j'y suis, j'ai defini toutes plages de la meme manière que ce que tu as fais, c'est à dire en prenant comme référence la colomme H : Par contre je ne vois pas trop quelle fonction me permetra d'appliquer la condition sur ces meme lignes mais sur des colonnes differentes. Cela vient surement dans la dernière partie du code mais c'est ce qui m'a frappé en regardant les differentes fonctions que tu utilises (bien que je comprenne pas tout, evidemment...).

Si jamais tu as des questions détaille-là comme si c'était la première et met un fichier nouvelle version.

eric

Rechercher des sujets similaires à "macro mfc appliquer puis eliminer gardant formats"