Macro trop lente

Le 30/12/2017 à 22:26

bonsoir a tous,

je suis nouveau sur ce forum et j'aurais besoins de votre aide car j'ai une macro qui mets environ 1 minutes et je sais pas pourquoi peut etre a cause de plusieurs conditions ?

Merci

Sub testdddd()
 Dim i As Integer
 c = Range("A1:A255").End(xlToRight).Column
    For i = [A65000].End(xlUp).Row To 1 Step -1
        For col = 1 To c
            If Cells(i, col) = "CD5279" Then
                Cells(i, col).Value = "XXX"

                If Cells(i, col) = "FX0006" Then
                Cells(i - 1, col).Value = "CGNR"

      End If

      ElseIf Cells(2, i).Value = "FX5041" Then
      Cells(i).Value = "MXP"

      ElseIf Cells(2, i).Value = "FX0008" Then
      Cells(i).Value = "FJR"

      ElseIf Cells(2, i).Value = "CD5279" Then
      Cells(i).Value = "MEM"

      ElseIf Cells(2, i).Value = "CD0003" Then
      Cells(i).Value = "MUCH"

      ElseIf Cells(2, i).Value = "ST009" Then
      Cells(i).Value = "STN"

     ElseIf Cells(2, i).Value = "CD0027" Then
      Cells(i).Value = "MEM"

     ElseIf Cells(2, i).Value = "ST0009" Then
      Cells(i).Value = "STN"

      ElseIf Cells(2, i).Value = "FX5124" Then
      Cells(i).Value = "FJR"

      ElseIf Cells(2, i).Value = "FX0039" Then
      Cells(i).Value = "EWR"

      ElseIf Cells(2, i).Value = "FX5028" Then
      Cells(i).Value = "CGNR"

      ElseIf Cells(2, i).Value = "FX5245" Then
      Cells(i).Value = "MAD"

      ElseIf Cells(2, i).Value = "FX5184" Then
      Cells(i).Value = "STO"

      ElseIf Cells(2, i).Value = "CD8038" Then
      Cells(i).Value = "OSL"

      ElseIf Cells(2, i).Value = "FX5182" Then
      Cells(i).Value = "CPH"

      ElseIf Cells(2, i).Value = "FX0004" Then
      Cells(i).Value = "FJR"

      ElseIf Cells(2, i).Value = "FX5212" Then
      Cells(i).Value = "VDD"

      ElseIf Cells(2, i).Value = "FX0038" Then
      Cells(i).Value = "DELH"

      ElseIf Cells(2, i).Value = "FL5188" Then
      Cells(i).Value = "AMS"

      ElseIf Cells(2, i).Value = "FX5173" Then
      Cells(i).Value = "BCN"

      ElseIf Cells(2, i).Value = "XX008" Then
      Cells(i).Value = "CANH"

      ElseIf Cells(2, i).Value = "ST0039" Then
      Cells(i).Value = "STN"

      ElseIf Cells(2, i).Value = "CD0001" Then
      Cells(i).Value = "MEM"

      ElseIf Cells(2, i).Value = "CD5035" Then
      Cells(i).Value = "MEM"

      ElseIf Cells(2, i).Value = "CD0003" Then
      Cells(i).Value = "MEM"

      ElseIf Cells(2, i).Value = "CD0027" Then
      Cells(i).Value = "MEM"

      ElseIf Cells(2, i).Value = "FX0010" Then
      Cells(i).Value = "NRTR"

      ElseIf Cells(2, i).Value = "EU050" Then
      Cells(i).Value = "STN"

      ElseIf Cells(2, i).Value = "FX5042" Then
      Cells(i).Value = "FJR"

      ElseIf Cells(2, i).Value = "IL5065" Then
      Cells(i).Value = "TLV"

      ElseIf Cells(2, i).Value = "FX5184" Then
      Cells(i).Value = "OSL"

       ElseIf Cells(2, i).Value = "FM026" Then
      Cells(i).Value = "MXP"

      ElseIf Cells(2, i).Value = "FX8094" Then
      Cells(i).Value = "STN"

      ' FLIGHT FLIGHT FLIGHT FLIGHT FLIGHT FLIGHT FLIGHT FLIGHT FLIGHT

        ElseIf Cells(2, i).Value = "TR3267" Then
      Cells(i).Value = "LEY"

         ElseIf Cells(2, i).Value = "TR3236" Then
      Cells(i).Value = "LHR"

       ElseIf Cells(2, i).Value = "TR3539" Then
      Cells(i).Value = "MAD"

       ElseIf Cells(2, i).Value = "TR3258" Then
      Cells(i).Value = "AMS"

       ElseIf Cells(2, i).Value = "TR3219" Then
      Cells(i).Value = "EIN"

       ElseIf Cells(2, i).Value = "TR3260" Then
      Cells(i).Value = "QYM"

       ElseIf Cells(2, i).Value = "TR3246" Then
      Cells(i).Value = "RTM"

       ElseIf Cells(2, i).Value = "TR3524" Then
      Cells(i).Value = "CGNR"

       ElseIf Cells(2, i).Value = "TR3375" Then
      Cells(i).Value = "CGNR"

       ElseIf Cells(2, i).Value = "TR3234" Then
      Cells(i).Value = "STN"

       ElseIf Cells(2, i).Value = "TR3235" Then
      Cells(i).Value = "STN"

       ElseIf Cells(2, i).Value = "TR3254" Then
      Cells(i).Value = "STN"

       ElseIf Cells(2, i).Value = "TR3217" Then
      Cells(i).Value = "EIN"

       ElseIf Cells(2, i).Value = "TR3218" Then
      Cells(i).Value = "EIN"

       ElseIf Cells(2, i).Value = "TR3866" Then
      Cells(i).Value = "LGGR"

       ElseIf Cells(2, i).Value = "LGGR" Then
      Cells(i).Value = "LGGR"

       ElseIf Cells(2, i).Value = "TR3688" Then
      Cells(i).Value = "LGGR"

       ElseIf Cells(2, i).Value = "TR3244" Then
      Cells(i).Value = "STN"

       ElseIf Cells(2, i).Value = "TR3280" Then
      Cells(i).Value = "AMS"

       ElseIf Cells(2, i).Value = "TR3245" Then
      Cells(i).Value = "STN"

       ElseIf Cells(2, i).Value = "TR3246" Then
      Cells(i).Value = "RTM"

       ElseIf Cells(2, i).Value = "TR3282" Then
      Cells(i).Value = "RTM"

       ElseIf Cells(2, i).Value = "TR3289" Then
      Cells(i).Value = "CGNR"

       ElseIf Cells(2, i).Value = "TR3535" Then
      Cells(i).Value = "CGNR"

       ElseIf Cells(2, i).Value = "TR3219" Then
      Cells(i).Value = "EIN"

       ElseIf Cells(2, i).Value = "TR3221" Then
      Cells(i).Value = "EIN"

       ElseIf Cells(2, i).Value = "TR3231" Then
      Cells(i).Value = "EIN"

       ElseIf Cells(2, i).Value = "LGGR" Then
      Cells(i).Value = "LGGR"

       ElseIf Cells(2, i).Value = "TR3684" Then
      Cells(i).Value = "LGGR"

       ElseIf Cells(2, i).Value = "TR3250" Then
      Cells(i).Value = "STN"

       ElseIf Cells(2, i).Value = "TR3262" Then
      Cells(i).Value = "STN"

       ElseIf Cells(2, i).Value = "TR3251" Then
      Cells(i).Value = "STN"

       ElseIf Cells(2, i).Value = "TR3255" Then
      Cells(i).Value = "STN"

       ElseIf Cells(2, i).Value = "TR3256" Then
      Cells(i).Value = "STN"

       ElseIf Cells(2, i).Value = "TR3240" Then
      Cells(i).Value = "MAD"

       ElseIf Cells(2, i).Value = "TR3241" Then
      Cells(i).Value = "MAD"

       ElseIf Cells(2, i).Value = "TR3242" Then
      Cells(i).Value = "MAD"

       ElseIf Cells(2, i).Value = "TR3203" Then
      Cells(i).Value = "STO"

       ElseIf Cells(2, i).Value = "TR3201" Then
      Cells(i).Value = "AMS"

       ElseIf Cells(2, i).Value = "TR3259" Then
      Cells(i).Value = "QYM"

       ElseIf Cells(2, i).Value = "TR3530" Then
      Cells(i).Value = "CGNR"

       ElseIf Cells(2, i).Value = "TR3533" Then
      Cells(i).Value = "CGNR"

       ElseIf Cells(2, i).Value = "CGNR" Then
      Cells(i).Value = "TR3538"

       ElseIf Cells(2, i).Value = "TR3662" Then
      Cells(i).Value = "LGGR"

       ElseIf Cells(2, i).Value = "TR3290" Then
      Cells(i).Value = "CGNR"

       ElseIf Cells(2, i).Value = "TR3532" Then
      Cells(i).Value = "TR3532"

       ElseIf Cells(2, i).Value = "TR3531" Then
      Cells(i).Value = "CGNR"

       ElseIf Cells(2, i).Value = "TR3536" Then
      Cells(i).Value = "CGNR"

       ElseIf Cells(2, i).Value = "TR3228" Then
      Cells(i).Value = "CGNR"

       ElseIf Cells(2, i).Value = "TR3229" Then
      Cells(i).Value = "CGNR"

       ElseIf Cells(2, i).Value = "TR3292" Then
      Cells(i).Value = "CGNR"

       ElseIf Cells(2, i).Value = "TR3214" Then
      Cells(i).Value = "CGNR"

       ElseIf Cells(2, i).Value = "TR3215" Then
      Cells(i).Value = "CGNR"

       ElseIf Cells(2, i).Value = "TR3233" Then
      Cells(i).Value = "STN"

       ElseIf Cells(2, i).Value = "TR3232" Then
      Cells(i).Value = "STN"

       ElseIf Cells(2, i).Value = "TR3205" Then
      Cells(i).Value = "BCN"

       ElseIf Cells(2, i).Value = "TR3206" Then
      Cells(i).Value = "BCN"

       ElseIf Cells(2, i).Value = "TR3547" Then
      Cells(i).Value = "BCN"

       ElseIf Cells(2, i).Value = "TR3226" Then
      Cells(i).Value = "CPH"

       ElseIf Cells(2, i).Value = "TR3388" Then
      Cells(i).Value = "CPH"

       ElseIf Cells(2, i).Value = "TR3213" Then
      Cells(i).Value = "BUD"

       ElseIf Cells(2, i).Value = "TR3234" Then
      Cells(i).Value = "PRG"

       ElseIf Cells(2, i).Value = "TR3443" Then
      Cells(i).Value = "MAD"

       ElseIf Cells(2, i).Value = "TR3200" Then
      Cells(i).Value = "AMS"

       ElseIf Cells(2, i).Value = "TR3266" Then
      Cells(i).Value = "AMS"

       ElseIf Cells(2, i).Value = "AMS" Then
      Cells(i).Value = "TR3283"

       ElseIf Cells(2, i).Value = "TR3263" Then
      Cells(i).Value = "EIN"

       ElseIf Cells(2, i).Value = "TR3264" Then
      Cells(i).Value = "EIN"

       ElseIf Cells(2, i).Value = "TR3220" Then
      Cells(i).Value = "EIN"

       ElseIf Cells(2, i).Value = "TR3222" Then
      Cells(i).Value = "EIN"

       ElseIf Cells(2, i).Value = "TR3284" Then
      Cells(i).Value = "EIN"

       ElseIf Cells(2, i).Value = "TR3296" Then
      Cells(i).Value = "EIN"

       ElseIf Cells(2, i).Value = "TR3265" Then
      Cells(i).Value = "RTM"

       ElseIf Cells(2, i).Value = "TR3247" Then
      Cells(i).Value = "RTM"

       ElseIf Cells(2, i).Value = "TR3285" Then
      Cells(i).Value = "RTM"

       ElseIf Cells(2, i).Value = "TR3286" Then
      Cells(i).Value = "QYM"

       ElseIf Cells(2, i).Value = "TR3293" Then
      Cells(i).Value = "LEY"

       ElseIf Cells(2, i).Value = "TR3297" Then
      Cells(i).Value = "LEY"

       ElseIf Cells(2, i).Value = "TR3248" Then
      Cells(i).Value = "VDD"

       ElseIf Cells(2, i).Value = "TR3457" Then
      Cells(i).Value = "VDD"

       ElseIf Cells(2, i).Value = "TR3663" Then
      Cells(i).Value = "LGGR"

       ElseIf Cells(2, i).Value = "TR3683" Then
      Cells(i).Value = "LGGR"

       ElseIf Cells(2, i).Value = "TR3681" Then
      Cells(i).Value = "LGGR"

       ElseIf Cells(2, i).Value = "TR3682" Then
      Cells(i).Value = "LGGR"

       ElseIf Cells(2, i).Value = "TR3685" Then
      Cells(i).Value = "LGGR"

      ElseIf Cells(2, i).Value = "TR3538" Then
      Cells(i).Value = "CGNR"

      'FEEDERs***********************************************

      ElseIf Cells(2, i).Value = "FX8045" Then
      Cells(i).Value = "PSA"

     ElseIf Cells(2, i).Value = "AU8082" Then
      Cells(i).Value = "GLA"

      ElseIf Cells(2, i).Value = "FX8019" Then
      Cells(i).Value = "WAW"

      ElseIf Cells(2, i).Value = "FX8043" Then
      Cells(i).Value = "NCL"

      ElseIf Cells(2, i).Value = "FX8014" Then
      Cells(i).Value = "STRH"

      ElseIf Cells(2, i).Value = "FX8006" Then
      Cells(i).Value = "BFS"

      ElseIf Cells(2, i).Value = "FX8020" Then
      Cells(i).Value = "PRG"

      ElseIf Cells(2, i).Value = "FX8030" Then
      Cells(i).Value = "SNN"

      ElseIf Cells(2, i).Value = "FX8035" Then
      Cells(i).Value = "FCO"

       ElseIf Cells(2, i).Value = "FX5204" Then
      Cells(i).Value = "ZMP"

       ElseIf Cells(2, i).Value = "AU5204" Then
      Cells(i).Value = "MHZ"

            End If
        Next col
    Next i
End Sub
Le 30/12/2017 à 23:15

Bonsoir,

Tu répètes toute ta liste de tests autant de fois qu'il y a de colonnes à chaque ligne... On peut considérer en gros que si tu as 10 colonnes, ta macro travaille à 90% pour rien, mais cela occupe du temps.

De plus la méthode n'est pas spécialement performante et ce n'est certainement pas la plus rapide...

Je suis par ailleurs dubitatif sur tes renvois de valeurs en ligne 1, mais c'est ton affaire.

Cordialement.

Le 30/12/2017 à 23:18

Bonsoir,

est-ce volontairement que tu ne mets pas de n° de colonne dans les instructions

Cells(i).Value = "MXP"

si tu ne mets pas de n° de colonne, pas besoin de mettre ces instructions dans la boucle for col=1 to c, sinon il faut revoir la logique. un fichier exemple de ce que tu as, de ce que tu veux avoir et des règles/instructions pour passer de l'un à l'autre seraient bienvenus.

Le 30/12/2017 à 23:19

Bonsoir,

vous pouvez toujours essayer de rajouter ceci :

Sub testdddd()
     Application.ScreenUpDating = False
     Dim i As Integer
     c = Range("A1:A255").End(xlToRight).Column

Juste pour voir.

@ bientôt

LouReeD

Le 30/12/2017 à 23:31

Super,

je viens de faire la modification est tout fonctionne tres bien

Merci encore et tous mes voeux

Le 30/12/2017 à 23:35

Bonsoir, c'est de la poussières aux yeux ! Soit cela fonctionne, mais les remarque de MFerrand et de h2so4 sont pertinentes

vbMBHB

Le 30/12/2017 à 23:41

Bonsoir,

Il est vrai que lorsque je bricole, je mets des lunettes pour éviter les poussières...

Mais comme l'a dit le Maréchal (bonsoir Maréchal) et "Acide" (bonsoir h2so4), il est vrai que votre code mérite une RAZ (remise à zéro).

Après, tout dépend de votre niveau, du temps que vous voulez y consacrer, de votre envie de progresser...

@ bientôt

LouReeD

Le 31/12/2017 à 18:30

Hello la compagnie

et il y a du monde au balcon ... de ce sujet !

alors je mets mon grain de sel (de Noirmoutier)

pourquoi une macro, alors qu'un tableau et une RECHERCHEV auraient suffi ! ?

Rechercher des sujets similaires à "macro trop lente"