Limites de la boucle "For...Next" VBA

[Paski]

Bonjour à tous,

Avant de commencer, merci de l'aide que vous apportez sur différents sujets, qui permettent chaque jour de résoudre pas mal de problèmes...

Mais là, j'en ai un qui me pose soucis, et j'en me remet à vous...

J'ai une macro fonctionnelle, mais si mes données sont supérieures à environ 10000 lignes, alors là, c'est le drame, ça mouline dans le vent...

Voici le code qui va bien

Sub seqp1()
Dim i As Long
Dim k As Long


For i = 1 To Range("B65536").End(xlUp).Row
    If Cells(i, 2).Value = "?" Then
        Cells(i + 1, 2).Value = Cells(i, 3).Value 
    End If
    
For k = 1 To Range("B65536").End(xlUp).Row
    If Cells(i + k, 2).Value <> "?" And Cells(i + k, 2).Value <> "" Then
        Cells(i + k, 2).Value = Cells(i + 1, 2).Value 
    Else
Exit For
End If
Next k
Next i
End Sub

Précisions :
- j'avais déclaré en "Integer" à la base, ce que j'ai corrigé en "Long" après erreur du dépassement de capacité, pour les séries de plus de 30000 lignes
- Pour environ 6000 lignes, la macro met 30 secondes à tourner
- J'ai attendu 10 bonnes minutes pour 15000 lignes, sans qu'elle ne finisse...
- Je fais tourner ça sur un DELL récent, avec un bon core 2 duo, de la RAM itou itou
- Je fais tourner ça depuis Excel 2003

Ma question est donc de savoir si j'atteins les limites de la boucle For...Next, ou si la façon dont j'ai écris ma boucle fout tout par terre si j'ai un trop grand nombre de lignes...

L'autre problème est qu'il faut absolument que je garde mes grandes listes... Pas moyen de scinder en plus petits fichiers (ce qui règlerais mon problème en plus...)

Merci d'avance de votre aide à tous !

 

[phlaurent55]

Re : Limites de la boucle "For...Next" VBA

Bonjour Paski, et bienvenue sur le forum

essaie en mettant en début de code:
Application.ScreenUpdating = False

et n'oublie pas de le remettre à True avant la fin

à+
Philippe

Edit: es-tu obligé d'avoir 2 boucles imbriquées ???, pourrais-tu joindre un fichier explicatif avec 50 lignes pour pouvoir comprendre et tester

EDIT 2: Bonjour PierreJean, Haco

 

[pierrejean]

Re : Limites de la boucle "For...Next" VBA

Bonjour Paski

Et bienvenue sur XLD

2 questions
1) Y a-t-il un inconvenient a classer tes listes
2) Peux-tu poster un fichier avec quelques dizaines de lignes ( données non confidentielles)

Edit: Salut Philippe

 

[Guest]

Re : Limites de la boucle "For...Next" VBA

bonjour et bienvenue sur le forum.

Comme tu as Deux boucles imbriquées, pour 10000 Lignes, tu as 100 000 000 de cellules à tester? C'est beaucoup. Non?

Pour pouvoir t'aider plus efficacement, il faudrait voir concrètement de quoi il retourne.

Essaie de déposer un fichier <50ko ici avec des données fictives et explications de ce que tu cherches à faire.

A+

[Edit] salut Pierre-Jean et Philippe

 

[Paski]

Re : Limites de la boucle "For...Next" VBA

Merci des premiers retours rapides !
Je post un fichier d'exemple dès demain matin (tout est sur l'ordi du boulot !)

Peut être qu'il y a possibilité de faire autrement que ma macro...

Bonne soirée messieurs

 

[Paski]

Re : Limites de la boucle "For...Next" VBA

Comme promis le petit fichier en pièce jointe...

J'ai détaillé ce que je voulais faire, avec un "avant/après"

Je ne peux, par contre, vous envoyer un fichier d'exemple avec 5o.ooo lignes, pour tester...

Je suis reparti d'un fichier vierge sinon c'était trop volumineux... donc la macro n'est pas dedans....

Encore merci !

 

[Guest]

Re : Limites de la boucle "For...Next" VBA

Bonjour Paski,

J'ai regardé ton fichier et dois avouer que je ne comprends rien.

A+

 

[youky(BJ)]

Re : Limites de la boucle "For...Next" VBA

Bonjour à tous,
Voici une version
annulation de calculation si formules et l'affichage.
Ou encore tester avec la methode Find cela fait refaire toute la macro et pas sur du résultat.

Bruno

Sub seqp1()
Dim i As Long
Dim k As Long
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
For i = 1 To Range("B65536").End(xlUp).Row
    If Cells(i, 2).Value = "?" Then
        Cells(i + 1, 2).Value = Cells(i, 3).Value
    End If
    
For k = 1 To Range("B65536").End(xlUp).Row
    If Cells(i + k, 2).Value <> "?" And Cells(i + k, 2).Value <> "" Then
        Cells(i + k, 2).Value = Cells(i + 1, 2).Value
    Else
Exit For
End If
Next k
Next i
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

 

[Guest]

Re : Limites de la boucle "For...Next" VBA

Re,

Il doit sûrement y avoir un moyen d'éviter la double boucle mais il faudrait que notre ami donne des explications supplémentaires concernant son séquençage afin que nous trouvions une algorythme plus efficace.

A+

 

[tbft]

Re : Limites de la boucle "For...Next" VBA

Bonjour à tout le monde

Paski, je te propose une autre méthode:
1: charger la colonne B en mémoire
2: travailler sur la mémoire
3: recharger la mémoire en lieu et place de la colonne B

 

[laetitia90]

Re : Limites de la boucle "For...Next" VBA

bonjour tous ;
comme je comprends???

Option Explicit

Dim t As Variant, t2() As Variant, x As Long, i As Long, z As Integer, k As Long
Sub es()
On Error Resume Next
Application.ScreenUpdating = False
t = Range("a1:f" & Range("a65536").End(xlUp).Row)
x = 1: z = 1
For i = 1 To UBound(t)
If t(i, 2) <> "?" Then t(i, 2) = t(i - z, 3): z = z + 1 Else z = 1
ReDim Preserve t2(1 To 6, 1 To x)
For k = 1 To 6
t2(k, x) = t(i, k): Next k: x = x + 1: Next i
Range("A1:f65536").ClearContents
Range("a1").Resize(UBound(t2, 2), UBound(t2, 1)) = Application.Transpose(t2)
Erase t, t2
End Sub

 

[laetitia90]

Re : Limites de la boucle "For...Next" VBA

re;
si c'est bon le code plus haut on peut améliorer la vitesse d'execution en "travaillant" seulement 2 colonnes au lieu de 6

Sub es()
On Error Resume Next
Application.ScreenUpdating = False
t = Range("b1:c" & Range("b65536").End(xlUp).Row)
x = 1: z = 1
For i = 1 To UBound(t)
If t(i, 1) <> "?" Then t(i, 1) = t(i - z, 2): z = z + 1 Else z = 1
ReDim Preserve t2(1 To 2, 1 To x)
For k = 1 To 2
t2(k, x) = t(i, k): Next k: x = x + 1: Next i
Range("b1:c65536").ClearContents
Range("b1").Resize(UBound(t2, 2), UBound(t2, 1)) = Application.Transpose(t2)
Erase t, t2
End Sub

oubli pas les variables

 

[Papou-net]

Re : Limites de la boucle "For...Next" VBA

Bonjour à tous,

RE Paski

Je te propose de modifier ta macro ainsi :

Sub seqp1()
Dim Seq

Application.ScreenUpdating = False
For Each cel In Range("B1:B" & Range("B65536").End(xlUp).Row)
  If cel.Value = "?" Then
    Seq = Cells(cel.Row, 3)
    Else
    cel.Value = Seq
  End If
Next
Application.ScreenUpdating = True
End Sub

Espérant avoir contribué.

Cordialement.

 

[Minick]

Re : Limites de la boucle "For...Next" VBA

Salut a tous,

En passant par 2 colonnes intermediaires et des formules toutes simples
ca doit etre quasi instantanne (enfin si j'ai bien compris...)

Sub seq1()
    Application.ScreenUpdating = False
        Range("IU1").Value = "?"
        Range("IV1").Value = 10
        Range("IU2").FormulaR1C1 = "=IF(RC2=""?"",""?"",IF(R[-1]C=""?"",R[-1]C[1],R[-1]C))"
        Range("IV2").FormulaR1C1 = "=R[-1]C+10"
        
        With Range("IU2:IV" & Range("A65536").End(xlUp).Row)
            .FillDown
            If Application.Calculation <> xlCalculationAutomatic Then .Calculate
        End With
        
        Range("IU1:IV" & Range("A65536").End(xlUp).Row).Copy
        Range("B1").PasteSpecial xlPasteValues
        
        Columns("IU:IV").ClearContents
    Application.ScreenUpdating = True
End Sub

Teste sur 60000 lignes, le temps de cligner des yeux c'etait fini.

 

[laetitia90]

Re : Limites de la boucle "For...Next" VBA

rebonjour tous ;

sur une colonne peut être pas aussi rapide que l'ami Minick avec utilisation de colonnes inter... a tester ??

Sub es()
Dim t As Variant, t2() As Variant, x As Long, i As Long, z As Long, k As Long
On Error Resume Next
Application.ScreenUpdating = False
t = Range("b1:b" & Cells(Rows.Count, 2).End(xlUp).Row)
x = 1: z = 1
For i = 1 To UBound(t)
If t(i, 1) <> "?" Then t(i, 1) = Cells(i - z, 3): z = z + 1 Else z = 1
ReDim Preserve t2(1 To 1, 1 To x)
For k = 1 To 1
t2(k, x) = t(i, k): Next k: x = x + 1: Next i
Columns("b:b").ClearContents
Range("b1").Resize(UBound(t2, 2), UBound(t2, 1)) = Application.Transpose(t2)
Erase t, t2
End Sub