Enregistrement d'une macro

Le 09/02/2020 à 10:06

Bonjour à tous,

J’ai enregistré une macro, je sollicite votre aide car celui-ci est lourd n’y a-t-il pas un moyen de l’alléger ?

Voici la macro

Sub Etat_De_Parc()

    Range("M5:M988").FormulaR1C1 = _
        "=COUNTIFS(RQT!C2,'Etat de parc 2020'!RC[-11],RQT!C21,R4C13,RQT!C12,""DN"")"

    Range("N5:N988").FormulaR1C1 = _
        "=COUNTIFS(RQT!C2,'Etat de parc 2020'!RC[-12],RQT!C21,R4C14,RQT!C12,""DN"")"

    Range("O5:O988").FormulaR1C1 = _
        "=COUNTIFS(RQT!C2,'Etat de parc 2020'!RC[-13],RQT!C21,R4C15,RQT!C12,""DN"")"

    Range("P5:P988").FormulaR1C1 = _
        "=COUNTIFS(RQT!C2,RC2,RQT!C21,R4C16,RQT!C12,""EX"",RQT!C15,""*EP9*"")"

    Range("Q5:Q988").FormulaR1C1 = _
        "=COUNTIFS(RQT!C2,RC2,RQT!C21,R4C17,RQT!C12,""EX"",RQT!C15,""*EP9*"")"

    Range("R5:R988").FormulaR1C1 = _
        "=COUNTIFS(RQT!C2,RC2,RQT!C21,R4C18,RQT!C12,""EX"",RQT!C15,""*EP9*"")"

    Range("S5:S988").FormulaR1C1 = _
        "=COUNTIFS(RQT!C2,RC2,RQT!C21,R4C19,RQT!C12,""EX"",RQT!C15,""*EP9*"")"

    Range("T5:T988").FormulaR1C1 = _
        "=COUNTIFS(RQT!C2,RC2,RQT!C21,R4C20,RQT!C12,""EX"",RQT!C15,""*EP9*"")"

    Range("U5:U988").FormulaR1C1 = _
        "=COUNTIFS(RQT!C2,RC2,RQT!C21,R4C21,RQT!C12,""EX"",RQT!C15,""*EP9*"")"

    Range("V5:V988").FormulaR1C1 = _
        "=COUNTIFS(RQT!C2,RC2,RQT!C21,R4C22,RQT!C12,""EX"",RQT!C15,""*PP6*"")"

    Range("W5:W988").FormulaR1C1 = _
        "=COUNTIFS(RQT!C2,RC2,RQT!C21,R4C23,RQT!C12,""EX"",RQT!C15,""*PP6*"")"

    Range("X5:X988").FormulaR1C1 = _
        "=COUNTIFS(RQT!C2,RC2,RQT!C21,R4C24,RQT!C12,""EX"",RQT!C15,""*PP6*"")"

    Range("Y5:Y988").FormulaR1C1 = _
        "=COUNTIFS(RQT!C2,RC2,RQT!C21,R4C25,RQT!C12,""EX"",RQT!C15,""*PP6*"")"

    Range("Z5:Z988").FormulaR1C1 = _
        "=COUNTIFS(RQT!C2,RC2,RQT!C21,R4C26,RQT!C12,""EX"",RQT!C15,""*PP6*"")"

    Range("AA5:AA988").FormulaR1C1 = _
        "=COUNTIFS(RQT!C2,RC2,RQT!C21,R4C27,RQT!C12,""EX"",RQT!C15,""*PP6*"")"

    Range("AB5:AB988").FormulaR1C1 = _
        "=COUNTIFS(RQT!C2,RC2,RQT!C21,R4C,RQT!C12,""EX"",RQT!C15,""*PP9*"")"

    Range("AC5:AC988").FormulaR1C1 = _
        "=COUNTIFS(RQT!C2,RC2,RQT!C21,R4C,RQT!C12,""EX"",RQT!C15,""*PP9*"")"

    Range("AD5:AD988").FormulaR1C1 = _
        "=COUNTIFS(RQT!C2,RC2,RQT!C21,R4C,RQT!C12,""EX"",RQT!C15,""*PP9*"")"

    Range("AE5:AE988").FormulaR1C1 = _
        "=COUNTIFS(RQT!C2,RC2,RQT!C21,R4C,RQT!C12,""EX"",RQT!C15,""*PP9*"")"

    Range("AF5:AF988").FormulaR1C1 = _
        "=COUNTIFS(RQT!C2,RC2,RQT!C21,R4C,RQT!C12,""EX"",RQT!C15,""*PP9*"")"

    Range("AG5:AG988").FormulaR1C1 = _
        "=COUNTIFS(RQT!C2,RC2,RQT!C21,R4C,RQT!C12,""EX"",RQT!C15,""*PP9*"")"

    Range("AH5:AH988").FormulaR1C1 = _
        "=COUNTIFS(RQT!C2,RC2,RQT!C21,R4C,RQT!C12,""EX"",RQT!C15,""*CO²*"")"

    Range("AI5:AI988").FormulaR1C1 = _
        "=COUNTIFS(RQT!C2,RC2,RQT!C21,R4C,RQT!C12,""EX"",RQT!C15,""*CO²*"")"

    Range("AJ5:AJ988").FormulaR1C1 = _
        "=COUNTIFS(RQT!C2,RC2,RQT!C21,R4C,RQT!C12,""EX"",RQT!C15,""*CO²*"")"

    Range("AK5:AK988").FormulaR1C1 = _
        "=COUNTIFS(RQT!C2,RC2,RQT!C21,R4C,RQT!C12,""EX"",RQT!C15,""*CO²*"")"

    Range("AL5:AL988").FormulaR1C1 = _
        "=COUNTIFS(RQT!C2,RC2,RQT!C21,R4C,RQT!C12,""EX"",RQT!C15,""*CO²*"")"

    Range("AM5:AM988").FormulaR1C1 = _
        "=COUNTIFS(RQT!C2,RC2,RQT!C21,R4C,RQT!C12,""BA"",RQT!C15,""*BAES*"")"

    Range("AN5:AN988").FormulaR1C1 = _
        "=COUNTIFS(RQT!C2,RC2,RQT!C21,R4C,RQT!C12,""BA"",RQT!C15,""*BAES*"")"

    Range("AO5:AO988").FormulaR1C1 = _
        "=COUNTIFS(RQT!C2,RC2,RQT!C21,R4C,RQT!C12,""BA"",RQT!C15,""*BAES*"")"

    Range("AP5:AP988").FormulaR1C1 = _
        "=COUNTIFS(RQT!C2,RC2,RQT!C21,R4C,RQT!C12,""BA"",RQT!C15,""*BAEH*"")"

    Range("AQ5:AQ988").FormulaR1C1 = _
        "=COUNTIFS(RQT!C2,RC2,RQT!C21,R4C,RQT!C12,""BA"",RQT!C15,""*BAEH*"")"

    Range("AR5:AR988").FormulaR1C1 = _
        "=COUNTIFS(RQT!C2,RC2,RQT!C21,R4C,RQT!C12,""BA"",RQT!C15,""*BAEH*"")"

    Range("AS5:AS988").FormulaR1C1 = _
        "=COUNTIFS(RQT!C2,RC2,RQT!C21,R4C,RQT!C12,""SI"",RQT!C15,""*CONSIGNE*"")"

    Range("AT5:AT988").FormulaR1C1 = _
        "=COUNTIFS(RQT!C2,RC2,RQT!C21,R4C,RQT!C12,""SI"",RQT!C15,""*CONSIGNE*"")"

    Range("AU5:AU988").FormulaR1C1 = _
        "=COUNTIFS(RQT!C2,RC2,RQT!C21,R4C,RQT!C12,""SI"",RQT!C15,""*CONSIGNE*"")+COUNTIFS(RQT!C2,RC2,RQT!C21,""MQT"",RQT!C12,""SI"",RQT!C15,""*CONSIGNE*"")"

    Range("AV5:AV988").FormulaR1C1 = _
        "=COUNTIFS(RQT!C2,RC2,RQT!C21,R4C,RQT!C12,""SI"",RQT!C15,""*BOITE*"")"

    Range("AW5:AW988").FormulaR1C1 = _
        "=COUNTIFS(RQT!C2,RC2,RQT!C21,R4C,RQT!C12,""SI"",RQT!C15,""*BOITE*"")"

    Range("AX5:AX988").FormulaR1C1 = _
        "=COUNTIFS(RQT!C2,RC2,RQT!C21,R4C,RQT!C12,""SI"",RQT!C15,""*BOITE*"")+COUNTIFS(RQT!C2,RC2,RQT!C21,""MQT"",RQT!C12,""SI"",RQT!C15,""*BOITE*"")"

    Range("AY5:AY988").FormulaR1C1 = _
        "=COUNTIFS(RQT!C2,RC2,RQT!C21,R4C,RQT!C12,""SI"",RQT!C15,""*CLE*"")"

    Range("AZ5:AZ988").FormulaR1C1 = _
        "=COUNTIFS(RQT!C2,RC2,RQT!C21,R4C,RQT!C12,""SI"",RQT!C15,""*CLE*"")"

    Range("BA5:BA988").FormulaR1C1 = _
        "=COUNTIFS(RQT!C2,RC2,RQT!C21,R4C,RQT!C12,""SI"",RQT!C15,""*CLE*"")+COUNTIFS(RQT!C2,RC2,RQT!C21,""MQT"",RQT!C12,""SI"",RQT!C15,""*CLE*"")"

    Range("BB5:BB988").FormulaR1C1 = _
        "=COUNTIFS(RQT!C2,RC2,RQT!C21,R4C,RQT!C12,""PL"",RQT!C15,""*PLAN*"")"

    Range("BC5:BC988").FormulaR1C1 = _
        "=COUNTIFS(RQT!C2,RC2,RQT!C21,R4C,RQT!C12,""PL"",RQT!C15,""*PLAN*"")"

    Range("BD5:BD988").FormulaR1C1 = _
        "=COUNTIFS(RQT!C2,RC2,RQT!C21,R4C,RQT!C12,""PL"",RQT!C15,""*PLAN*"")+COUNTIFS(RQT!C2,RC2,RQT!C21,""MQT"",RQT!C12,""PL"",RQT!C15,""*PLAN*"")"

    Range("BE5:BE988").FormulaR1C1 = _
        "=COUNTIFS(RQT!C2,RC2,RQT!C21,R4C,RQT!C12,""SI"",RQT!C15,""*SIGNALETIQUE*"")"

    Range("BF5:BF988").FormulaR1C1 = _
        "=COUNTIFS(RQT!C2,RC2,RQT!C21,R4C,RQT!C12,""SI"",RQT!C15,""*SIGNALETIQUE*"")"

    Range("BG5:BG988").FormulaR1C1 = _
        "=COUNTIFS(RQT!C2,RC2,RQT!C21,R4C,RQT!C12,""SI"",RQT!C15,""*SIGNALETIQUE*"")+COUNTIFS(RQT!C2,RC2,RQT!C21,""MQT"",RQT!C12,""SI"",RQT!C15,""*SIGNALETIQUE*"")"

    Range("BH5:BH988").FormulaR1C1 = _
        "=COUNTIFS(RQT!C2,RC2,RQT!C21,R4C,RQT!C12,""PK"",RQT!C15,""*BAC*"")"

    Range("BI5:BI988").FormulaR1C1 = _
        "=COUNTIFS(RQT!C2,RC2,RQT!C21,R4C,RQT!C12,""PK"",RQT!C15,""*BAC*"")"

    Range("BJ5:BJ988").FormulaR1C1 = _
        "=COUNTIFS(RQT!C2,RC2,RQT!C21,R4C,RQT!C12,""PK"",RQT!C15,""*BAC*"")+COUNTIFS(RQT!C2,RC2,RQT!C21,""MQT"",RQT!C12,""PK"",RQT!C15,""*BAC*"")"

    Range("BK5:BK988").FormulaR1C1 = _
        "=COUNTIFS(RQT!C2,RC2,RQT!C21,R4C,RQT!C12,""PC"",RQT!C15,""*PCF*"")"

    Range("BL5:BL988").FormulaR1C1 = _
        "=COUNTIFS(RQT!C2,RC2,RQT!C21,R4C,RQT!C12,""PC"",RQT!C15,""*PCF*"")"

    Range("BM5:BM988").FormulaR1C1 = _
        "=COUNTIFS(RQT!C2,RC2,RQT!C21,R4C,RQT!C12,""PC"",RQT!C15,""*PCF*"")+COUNTIFS(RQT!C2,RC2,RQT!C21,""MQT"",RQT!C12,""PC"",RQT!C15,""*PCF*"")"

    Range("BN5:BN988").FormulaR1C1 = _
        "=COUNTIFS(RQT!C2,RC2,RQT!C21,""NV"",RQT!C12,""CS"",RQT!C15,""*COLONNE*"")"

    Range("BO5:BO988").FormulaR1C1 = _
        "=COUNTIFS(RQT!C2,RC2,RQT!C21,R4C,RQT!C12,""CS"",RQT!C15,""*COLONNE*"")"

    Range("BP5:BP988").FormulaR1C1 = _
        "=COUNTIFS(RQT!C2,RC2,RQT!C21,R4C,RQT!C12,""CS"",RQT!C15,""*COLONNE*"")"

End Sub

Merci de l’aide que vous pouvez m’apporter

Le 09/02/2020 à 10:26

Bonjour,

Bravo pour cet excellent travail. Mais à quoi ça sert ?

Si c'est inutile, toute amélioration même géniale sera aussi inutile.

Le 09/02/2020 à 10:31

Bonjour

Il faudrait d'abord que tu améliores tes formules excel en utilisant au mieux adressage absolu et adressage relatif.

Si tu es capable de limiter tes formules et de les dupliquer par copier coller, tu pourras simplifier le vba

Le 09/02/2020 à 10:32

Bonjour,

Comme je me suis également intéressé à ce problème, j'ajoute mon grain de sel.

safwaty, joins un fichier et indique ce que tu souhaites réaliser.

Amicalement.

Le 09/02/2020 à 11:16

merci de l’intérêt que vous portez à mon problème,

j'ai un logiciel j'ai fais une requête dessus il me sort un fichier excel je l'ai copié sur feuille RQT ensuite quand j'appuie sur le bouton formule dans la feuille etat de parc il me complète par nb.si.ens

je vous joint le fichier

merci de votre aide

13etat-de-parc.xlsm (402.90 Ko)
Le 09/02/2020 à 16:14

Ton fichier m'étonne un peu

J'ai cherché une macro afin de remplacer la mise en place de formules par ta macro enregistrée - tu inscrits au total 56 * 983 formules, ça représente 55'048 formules, ce qui bouffe passablement de mémoire - mais très étonnamment ma macro mouline également terriblement. Je n'arrive pas à comprendre pourquoi elle avance aussi lentement !!

Je m'explique. J'ai tout d'abord préparé le traitement de la colonne M de ta feuille ''Etat de parc 2020''. Bizarrement il fallait environ 12 secondes sur ma machine afin de traiter cette colonne, pratiquement une éternité.

J'ai continué à vouloir traiter la colonne P en plus et ça durait environ 24 secondes, alors que je pensais que le boucle For i/Next était la même et que ça ne durerait rien de plus !!!!

Puis j'ai tenté de traiter les colonnes P à U dans une ''sous-boucle'' For j/Next et la durée de traitement est montée à 90 secondes environ. A n'y rien comprendre.

N'ayant pas trouvé le bouton indiqué de ta part, j'en ai ajouté un vers la cellule F1 de la feuille ''Etat de parc 2020''.

Il faudrait alors tenté de passer par des tableaux afin de réduire la durée de traitement, mais je ne désire pas le faire avant de savoir si je suis parti sur la bonne piste. Peux-tu alors me confirmer que les résultats sont bien ceux-attendus, éventuellement sur la base d'autres données dans la feuille ''RQT'' ?

Et éventuellement qu'un autre membre a une explication à cette extrême lenteur. Afin de l'éviter, j'ai déjà essayé de supprimer les formules de la colonne L (la seule en contenant) et de supprimer les couleurs de fond de toute la page, mais sans succès

Cordialement.

8etat-de-parc-v1.xlsm (202.64 Ko)
Le 09/02/2020 à 16:46

Je pense que la lenteur provient peut être de la colonne L celle qui ramène la date

Qu'en pensez-vous ?

Le 09/02/2020 à 16:51

Selon moi, non

Yvouille a écrit :

Afin de l'éviter, j'ai déjà essayé de supprimer les formules de la colonne L (la seule en contenant) ........, mais sans succès

Le 09/02/2020 à 16:54

Excuse moi je n'ai pas fais attention que tu l'as vu

Le 09/02/2020 à 17:06
Yvouille a écrit :

Peux-tu alors me confirmer que les résultats sont bien ceux-attendus, éventuellement sur la base d'autres données dans la feuille ''RQT'' ?

Le 09/02/2020 à 17:17

oui c'est exactement ça

Le 09/02/2020 à 18:17

Bonjour,

Moi je dirai qu'il ne faut pas travailler sur la totalité de chaque colonne mais borner exactement à la zone contenant les données. Il y a deux moyens pour y arriver : soit travailler avec les adresses exactes (dans l'exemple lignes 2 à 69 pour toutes colonnes) ou travailler avec des plages nommées dynamiques (DECALER) . Ensuite moi je ne travaillerai pas avec des FormulaR1C1 imbuvable mais avec des Formula. Pour la première ligne ça donne :

Sub Etat_De_Parc()
    Range("M5:M988").Formula = _
        "=COUNTIFS(RQT!$B$2:$B$69,'Etat de parc 2020'!B5,RQT!$U$2:$U$69,$M$4,RQT!$L$2:$L$69,""DN"")"

... et c'est instantané !

Troisième possibilité : Travailler avec les plages nommées d'un tableau structuré mais là je m'avance peut-être un peu. Je n'ai pas essayé car je maitrise assez peu...

Quatrième possibilité : Ma préférée... Nommer chaque formule dans le gestionnaire de nom. Pour M5 par exemple on copie la formule que je viens de citer en la modifiant au besoin avec des formules dynamiques puis on remplace la formule par le nom dédié. cf le classeur joint.

Je n'ai travaillé qu'avec la colonne M mais c'est pareil pour les autres On crées toutes les plages nécessaires ZONEB, ZONEU, ZONEL et autant de noms de formules que nécessaire PAPILLON est joli mais POMME, POIRE, BANANE, ou ...GALOPIN feraient aussi bien l'affaire et basta...

Dans ce cas le VBA devient :

Sub Etat_De_Parc()
    Range("M5:M988").Formula = "=PAPILLON"

Plus simple : YAPA !

A+

7etat-de-parc.xlsm (408.12 Ko)
Le 09/02/2020 à 18:29

Bonjour galopin01,

merci de votre intéressement est ce qu'en appliquant ta solution: elle prend toute la colonne (M5:M988) ?

Le 09/02/2020 à 18:38

Tu as essayé ? Relis ma réponse j'ai modifié, rajouté d'autres pistes et ajouté le classeur.

Hum c'est comme pour Port Salut : C'est écrit dessus 

Range("M5:M988").Formula =

Sorry erreur de classeur, c'est modifié...

A+

Le 10/02/2020 à 09:47

J'ai été un peu plus loin je t'ai modifié avec ce système les colonnes jusqu'à U, Juste pour que tu apprécies toute la puissance du système...

Je ne peux pas aller plus loin car il n'y a aucune donnée qui remonte pour les autres colonnes donc je ne peux pas vérifier au fur et à mesure mébon le principe est le même...

A noter que tes formules ne sont pas très optimisées : Ainsi pour quelques $ en plus (ou en moins...) la même formule peut traiter les colonnes MNO et une autre PQRSTU. "PAPILLON" et "CERISE" y remédient...

La même observation s'applique aux autres groupes de colonnes.

Il n'en demeure pas moins que toutes ces NB.SI.ENS comme toutes les matricielles sont assez chronophage. Plus le tableau est grand et plus le temps de recalcul pourrait s'allonger. Dans ce cas il y aurait peut-être avantage à figer les résultats après chaque série.

Pour aller plus loin, toujours selon la taille des tableaux et le temps de recalcul on pourrait aussi évoquer la possibilité de calculer directement sur des Array.

Enfin quelques fumeurs invétérés de trucs inavouables pourrait peut-être proposer des solutions à base de segments et/ou Power.Machin méla je suis un peu à la ramasse...

A+

5etat-de-parc-vg2.xlsm (406.55 Ko)
Le 15/02/2020 à 08:03

bonjour Galopin 01

mille merci pour votre aide précieuse désolé de répondre si tardivement mais cela fonctionne de fou ton calcul presque rien en temps super efficace, par contre tu dis que l'on peut figer les résultats après chaque calcul, avez vous une idée de comment procéder? c'est seulement pour info mais cela m'intéresse

encore un . grand merci

Le 16/02/2020 à 07:03

C'est un peu tard ! Figer les résultats c'est faire un Copier/Collage spécial valeur.

Une autre manière est de commenter chaque ligne de la macro au fur et à mesure que tu as fignolé une formule. (Pas la peine de recalculer à chaque étape.) et une fois que toutes les formules sont OK YAPUKA "décommenter" TOUSSA.

A+

Rechercher des sujets similaires à "enregistrement macro"