Boucle For lente

Bonjour,

Je souhaiterais accélérer une boucle For qui est très lente a l'exécution comme les calculs sont plutôt long (la boucle lit une feuille de classeur avec des données sur environ 20000 lignes) l'exécution de la macro prend une dizaine de minutes et parfois plus en fonction des données de la feuille et si les conditions sont réunies.

Pour résumé mes 2 conditions If, test si la valeur Var =0 dans la colonne 13 et que la valeur decalé de -10 et 1 sont inferieur ou superieur alors on insère une ligne

J'ai testé avec une boucle do while mais j'ai pas vu de changement, et j'avais vu que dans un code on peut lire puis écrire en une seule fois a la fin ce qui accélère grandement l'exécution et évite de devoir lire et écrire en boucle mais je ne sais pas comment faire

Si quelqu'un a une solution pour l'optimisé ça serait top

Merci d'avance

voici une partie du code avec la boucle For :

PS: la condition du If est bien sur une seule ligne dans mon code

Set FL1 = Worksheets("Feuil1")

    NoCol = 13

    derniereLigne = Cells(Rows.Count, 13).End(xlUp).Row

    'Utilisation du N° de ligne dans une boucle For ... Next
    For NoLig = 3 To derniereLigne
        Var = FL1.Cells(NoLig, NoCol)
        'si test cellule de gauche sup alors sauter lignes a gauche
        If Var = 0 And Cells(NoLig, NoCol).Offset(, -10).Value > Cells(NoLig, NoCol).Offset(, 1).Value And Cells(NoLig, NoCol).Offset(, 1).Value <> 0 Then
            ActiveSheet.Cells(NoLig, NoCol).Offset(0, -1).Select
            Set plage = Range(ActiveCell, ActiveCell.Offset(0, -9))
            plage.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
            'recole la formule
            Range("M3").AutoFill Destination:=Range(Cells(3, 13), Cells(derniereLigne, 13)), Type:=xlFillDefault 

        End If
        'si test cellule de gauche inf alors sauter lignes a droite
        If Var = 0 And Cells(NoLig, NoCol).Offset(, -10).Value < Cells(NoLig, NoCol).Offset(, 1).Value And Cells(NoLig, NoCol).Offset(, -5).Value <> 0 Then
            'Selection.Value = 0
            ActiveSheet.Cells(NoLig, NoCol).Offset(0, 1).Select
            Set plage = Range(ActiveCell, ActiveCell.Offset(0, 9))
            plage.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
            'recole la formule
            Range("M3").AutoFill Destination:=Range(Cells(3, 13), Cells(derniereLigne, 13)), Type:=xlFillDefault 

        End If
    Next

    Set FL1 = Nothing

Bonjour,

Sans voir le fichier....

Une première idée :

- en début de code, mettez Application.Calculation = xlCalculationManual
- en fin de code, mettez Application.Calculation=xlCalculationAutomatic
- Aussi évitez votre ligne avec SELECT

D'autres idées ou suggestions vont peut être vous être proposées

Cordialement

Bonjour à tous,

et que la valeur decalé de -10 et 1 sont inferieur ou superieur alors on insère une ligne

en clair ça veut dire quoi ?

Et même demande : déposer un fichier (anonymisé) de quelques lignes avec tous les cas de figure à traiter.
eric

Salut Aquaking,
Salut les as,

je suis peut-être à côté de la plaque mais, de ce que j'ai pu déchiffrer, la colonne "M" n'étant pas impactée par les insertions de ligne, la recopie des formules est inutile = gain de temps.
Après réécriture, à l'aveugle..

Dim sWk As Worksheet, iVar%
'
Application.ScreenUpdating = False
Set sWk = Worksheets("Feuil1")
'
For x = 3 To Range("M" & Rows.Count).End(xlUp).Row
    iVar = sWk.Cells(x, 13)
    'si test cellule de gauche sup alors sauter lignes a gauche
    If iVar = 0 And Cells(x, 13).Offset(0, -10).Value > Cells(x, 13).Offset(0, 1).Value And _
        Cells(x, 13).Offset(0, 1).Value <> 0 Then
            Range("M" & x).Offset(0, -10).Resize(1, 10).Insert shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    ElseIf iVar = 0 And Cells(x, 13).Offset(0, -10).Value < Cells(x, 13).Offset(0, 1).Value And _
        Cells(x, 13).Offset(0, -5).Value <> 0 Then
            Range("M" & x).Offset(0, 1).Resize(1, 10).Insert shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    End If
Next
'
Set sWk = Nothing
Application.ScreenUpdating = True

Á voir... Vu l'insertion de lignes, es-tu certain du sens de lecture de la boucle ?
Valait mieux avoir ce sujet aujourd'hui : durant un réveillon, dur, dur...

EDIT : concernant la recopie de la formule en [M:M], sans doute faut-il la placer hors de la boucle FOR...NEXT...

Range("M3").AutoFill Destination:=Range("M3:M" & Range("M" & Rows.Count).End(xlUp).Row), Type:=xlFillDefault

A+

Salut à tous,

Merci pour vos réponses,

Concernant le calcul manuel : Application.Calculation = xlCalculationManual

Application.Calculation=xlCalculationAutomatic

J'ai déjà essayé mais ça créer des problèmes une fois la macro exécutée donc je l'ai enlevé, quand je mettais la fonction il y avait des calculs manquants.

Pour le SELECT faudrait que j'essaye de trouver autre chose en effet

Je vous transmets le classeur entier

Non tu as bien compris curulis57 la colonne M n'est pas impacté mais la recopie des formules est obligé car en insérant une ligne la formule ne correspond plus les cellules visé dans la formule ne sont plus sur la même ligne

Ducoup comme j'ai plus de données dans la colonne H pour simplifier le fichier car il était trop lourd pour l'upload voici une modif a faire dans le code au niveau du 2e If

'si test cellule de gauche inf alors sauter lignes a droite
        If Var = 0 And Cells(NoLig, NoCol).Offset(, -10).Value < Cells(NoLig, NoCol).Offset(, 1).Value And Cells(NoLig, NoCol).Offset(, -4).Value <> 0 Then
           

Offset de -4 au lieu de -5 avant (du moment que l'offset est entre -1 et -10 a ce niveau la par rapport a la colonne M cela a peut d'importance comme on cherche juste si cette plage de la ligne est diffèrent de 0)

Bonjour

Concernant le calcul manuel : Application.Calculation = xlCalculationManual - Application.Calculation=xlCalculationAutomatic

J'ai déjà essayé mais ça créer des problèmes une fois la macro exécutée donc je l'ai enlevé, quand je mettais la fonction il y avait des calculs manquants.

Si vous avez des formules, cela peut poser souci (bien que je n'en vois aucune dans le fichier) Retenez que si vous avez des formules, excel recalcule à l'arriere plan dès que vous modifiez une cellule. Là cela alourdi l'exécution du code bien entendu.

Pour le SELECT faudrait que j'essaye de trouver autre chose en effet

Supprimez la ligne contenant le Select et remplacez là par ceci :

Set plage = Range(Cells(nolig, 12), Cells(nolig, 3))

A voir le code de Curculis qui sera, je pense, plus rapide

Le tout est de voir si votre fichier posté correspond exactement à ce que vous avez.

Une suggestion, pourquoi ne pas commencer par faire un tri pour mettre toutes les valeurs 0 en premier car si je lis bien dans le code, vous controlez toujours que VAR soit nulle. D'où dans votre fichier cela fait boucler inutilement jusque la ligne 8189 avant que la variable VAR soit vue à 0

Bonjour,

J'ai essayé votre code curulis mais par contre je suis obliger de recoller la formule dans la colonne M donc il faut que je la laisse dans la boucle car sinon elle n'est plus valide quand on insère une ligne.

Dan j'ai bien des formules dans mon fichier dans la colonne M :

=SI(OU(C3=N3;C3="";N3="");1;0)

oui en effet les calculs sont long avec la recopie des formules, une dizaine de minutes.

Le but de cette macro est d'aligner les tests en ayant toujours la colonne C égale à la N si c'est le cas on met '1' dans la colonnes M sinon '0' il faut insérer une ligne a gauche ou a droite.

Un 'tri' c'est a dire Dan ? avec des filtres? parce qu'il faut que je garde quand même les tests dans l'ordre, si j'ai bien compris ce que vous pensez c'est de mettre tous les lignes avec 0 en colonne M en premier sur la feuille

Bonjour

Désolé je n'avais pas regardé qu'il y avait cette formule

Un 'tri' c'est a dire Dan ? avec des filtres? parce qu'il faut que je garde quand même les tests dans l'ordre, si j'ai bien compris ce que vous pensez c'est de mettre tous les lignes avec 0 en colonne M en premier sur la feuille

Oui exactement. D'abord trier vos données. Ensuite dans la boucle mettre une condition que si VAR devient 1, vous sortez de la macro (exit sub). De cette sorte vous ne traitez que VAR = 0.

En plus de la modification du SELECT proposée précédemment, voici une idée pour le code de tri éventuel à ajouter dans votre fichier

    With Worksheets("Feuil1").Sort
        .SortFields.Clear
        .SortFields.Add2 Key:=Range("M3:M15746"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SetRange Range("C3:T15746")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

Aussi à essayer, enlevez cette ligne dans les 2 conditions IF et la placer juste en dessous de l'instruction NEXT

Range("M3").AutoFill Destination:=Range(Cells(3, 13), Cells(derniereLigne, 13)), Type:=xlFillDefault 

A voir si cela convient car je n'ai testé que sur une ou deux valeurs en ligne 2781 (après avoir appliqué le tri)

Bonjour,

J'ai essayé votre code mais j'ai une erreur sur cette ligne :

.SortFields.Add2 Key:=Range("M3:M15746"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

Je n'ai pas compris comment votre code dans with fait pour trier les 0 et 1 de la colonne M.

Voici mon code :

Application.ScreenUpdating = False

With Worksheets("Feuil1").Sort
        .SortFields.Clear
        .SortFields.Add2 Key:=Range("M3:M15746"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SetRange Range("C3:T15746")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

    Dim sWk As Worksheet, iVar%

   'Instance de la feuille qui permet d'utiliser FL1 partout dans
    'le code à la place du nom de la feuille
    Set sWk = Worksheets("Feuil1")

    'Utilisation du N° de ligne dans une boucle For ... Next
    For x = 3 To Range("M" & Rows.Count).End(xlUp).Row
        iVar = sWk.Cells(x, 13)
    If iVar = 1 Then Exit Sub

    'si test cellule de gauche sup alors sauter lignes a gauche
    If iVar = 0 And Cells(x, 13).Offset(0, -10).Value > Cells(x, 13).Offset(0, 1).Value And _
        Cells(x, 13).Offset(0, 1).Value <> 0 Then
            Range("M" & x).Offset(0, -10).Resize(1, 10).Insert shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
'            Range("M3").AutoFill Destination:=Range("M3:M" & Range("M" & Rows.Count).End(xlUp).Row), Type:=xlFillDefault
    ElseIf iVar = 0 And Cells(x, 13).Offset(0, -10).Value < Cells(x, 13).Offset(0, 1).Value And _
        Cells(x, 13).Offset(0, -1).Value <> 0 Then
            Range("M" & x).Offset(0, 1).Resize(1, 10).Insert shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
'            Range("M3").AutoFill Destination:=Range("M3:M" & Range("M" & Rows.Count).End(xlUp).Row), Type:=xlFillDefault
    End If
Next
Set sWk = Nothing
Range("M3").AutoFill Destination:=Range(Cells(3, 13), Cells(derniereLigne, 13)), Type:=xlFillDefault

Application.ScreenUpdating = True

Bonjour

J'ai essayé votre code mais j'ai une erreur sur cette ligne :

Essayez en enlevant le 2 juste derrière ADD

Je n'ai pas compris comment votre code dans with fait pour trier les 0 et 1 de la colonne M.

C'est l'instruction Order:= xlAscending qui organise le tri par ordre croissant

Crdlt

ok merci ca fonctionne mais c'est plus compliqué que ca, il faut une fois le tri et les sauts de ligne effectué, replacer les tests dans l'ordre croissant sachant que la synchro doit être à 1 partout

Bonjour,

Je comprends ce que tu veux faire Dan mais j'ai peur que se soit plus compliqué comme les tests ne sont plus dans l'ordre après les avoir classés par ordres croissants.

Avec une boucle For each ça serait peut être plus rapide ?

Bonjour

ok merci ca fonctionne mais c'est plus compliqué que ca, il faut une fois le tri et les sauts de ligne effectué, replacer les tests dans l'ordre croissant sachant que la synchro doit être à 1 partout

j'ai peur que se soit plus compliqué comme les tests ne sont plus dans l'ordre après les avoir classés par ordres croissants.

A quoi servent les colonnes N à T car le tri n'est effectué que sur les colonnes C à M

Une boucle for each est plus rapide mais vous n'allez pas gagner grand chose.

Vous dites replacer les tests croissants, on peut le faire sauf que lorsque vous ajoutez une ligne la colonne C n'est pas incrémentée

Bonjour,

Il y a du (beau même très beau !! Dan, Curulis, Eriiiic) monde, alors je m'invite au salon.

Une méthode d'accélération foudroyante est d'oublier de travailler avec les cells et range, et de ne travailler qu'avec des array en important les données dans un grand tableau

Bonjour,

Il faudrait que le tri soit effectué sur les données de chaque coté de la colonne M, le problème en effet c'est que les colonnes C et N ne sont pas incrémentées, je vais essayé de codé ça

Il faudrait que le tri soit effectué sur les données de chaque coté de la colonne M,

Bonjour,

c'est à dire ?? , quelles colonnes et le tri final en fonction de quoi ?

Essayez de préciser mieux votre résultat final avec un fichier qui reprend moins de données. Désolé, mais de mon coté je n'ai toujours pas compris le résultat final attendu.

Le but de mon programme c'est que je compare 2 classeurs, le premier se situe de la colonne C à L et l'autre de N à V, la colonne M sert a comparer si la synchro des numéros de tests colonne C et N est bonne, c'est a dire si les valeurs sont égales, par exemple ligne 8189 on voit qu' on a le test 9150 et 9153, la synchro n'est donc pas bonne donc '0' et pour aligner les tests j'insère des lignes soit dans le classeur de gauche soit dans celui de droite.

Ainsi à la fin j'ai tout mes tests qui sont alignés et donc comparable pour tracer un graphique.

Le problème c'est que la macro est lente a exécutée plus de dix minutes

J'espère que c'est plus clair maintenant

Y a-t-il d'autre formules que celle de la colonne M ? S'il n'y en a pas d'autres, la meilleure solution -que je voudrais tester- est de travailler en array en dehors de la feuille.

c'est a dire si les valeurs sont égales, par exemple ligne 8189 on voit qu' on a le test 9150 et 9153, la synchro n'est donc pas bonne donc '0' et pour aligner les tests j'insère des lignes soit dans le classeur de gauche soit dans celui de droite

Lorsque vous prenez cette ligne 8189, avec le code ci-dessous. Pourquoi mettez vous une condition avec OFFSET(0, -1) qui correspond à la colonne L qui est toujours vide dans le fichier.

ElseIf iVar = 0 And Cells(x, 13).Offset(0, -10).Value < Cells(x, 13).Offset(0, 1).Value And _
        Cells(x, 13).Offset(0, -1).Value <> 0 Then