Boucle For sur 100 000 lignes

Bonjour à tous,

J'ai un problème au niveau du temps de réponse de ma macro qui boucle sur environ 100 000 lignes.

Pourriez-vous me conseiller afin d'optimiser une macro qui parcoure des milliers de lignes, s'il vous plaît?

Merci par avance pour votre aide.

Bon après-midi à tous.

Ca depend que souhaites tu faire ?

Bonjour @whereisbryan ,

Dans un classeur excel, j'ai dans ma colonne A des milliers de lignes.

Les lignes ont pour valeur un nom de fichier.

Ma macro parcoure un dossier contenant des milliers de fichier pdf.

Elle évalue si le nom du fichier pdf fait partie des valeurs de la colonne A. Si oui, la cellule est colorée en vert, sinon en rouge.

Pourriez-vous me dire comme je peux faire pour optimiser la lecture svp?

En effet, lorsque je lance ma macro pour 10 , 100 lignes elles tournent bien mais lorsque je boucle sur le nombre total de ligne du classeur, la macro plante.

Merci par avance pour votre aide.

Bonjour,

Tu peux déjà utiliser des propriétés comme Application.ScreenUpdating = False -> Application.ScreenUpdating = true pour empêcher le rafraichissement de l'écran.

Sinon en passant par un tableau, à la place de directement récupérer les infos dans le fichier à chaque itérations. Stocker dès le début toutes les infos dans un tableau puis les traiter.

Cordialement

re,

Pour moi la solution la plus rapide est de passer par un tableau

Tu stock toutes les valeurs de ton dossier dans un premier tableau puis tu stock toute ta colonne dans un autre puis tu compares avec des boucles tes 2 tableaux ca ira beaucoup plus vite normalement

Merci à bvous deux pour votre aide.

Je vais tester les deux solutions.

J'ai une question concernant la propriété "Application.ScreenUpdating = true".

Je place cette propriété à quel niveau dans ma macro (au tout début, juste avant la boucle For ..)

Merci par avance pour votre aide.

Application.ScreenUpdating = False au tout debut de ta macro juste apres le sub par exemple

Application.ScreenUpdating = true tout à la fin

Cela va juste figer l image entre les 2

Bonjour,

C'est le travail sur les cellules qui ralentit.

Il faut utiliser des Arrays(), éventuellement indexés par des dictionnaires (ou collection si MAC)

Boisgontier

@Boisgontierjacques

vous auriez pu ajouter la référence avec toutes les infos pour comprendre comment ca marche

http://boisgontierjacques.free.fr/

Re,

J'ai ajouté la propritété "Application.ScreenUpdating = true"

Mon code est plus rapide lorsque la boucle tourne sur 100 records mais au-delà la macro ne répond plus.

J'ai joint le classeur avec ma macro. (j'ai supprimé environ 80 000 lignes car le fichier était trop volumineux pour le joindre)

Pourriez-vous avoir un regard sur ma macro et m'aider à l'optimiser s'il vous plaît?

Mille mercis pour votre aide.

11for-loop.xlsm (831.35 Ko)

re,

Sub GetFilesInFolder()
Dim WS As Worksheet
Dim FSO As Scripting.FileSystemObject
Dim SourceFolderName As String, DestinationFolderName As String
Dim SourceFolder As Scripting.folder, SubFolder As Scripting.folder
Dim FileItem As Scripting.file
Dim FileNamesArray() As String
Dim i As Long, iLig As Long, NbLig As Long, FileNameValue As Long

    Set WS = Worksheets("Sheet1")

    SourceFolderName = "C:\Users\Desktop\Test\"

    Set FSO = New Scripting.FileSystemObject
    Set SourceFolder = FSO.GetFolder(SourceFolderName)

    ReDim FileNamesArray(1 To SourceFolder.Files.Count)

    NbLig = WS.Cells(WS.Rows.Count, "A").End(xlUp).Row

    Application.ScreenUpdating = False

    dern = Sheets(1).Range("A1").End(xlDown).Row
    Dim tableau()
    ReDim tableau(dern + 1, 2)

    For x = 2 To dern
        tableau(x - 2, 0) = Sheets(1).Cells(x, 1)
        tableau(x - 2, 1) = Sheets(1).Cells(x, 2)
    Next

    For iLig = 0 To dern
        i = 0
        For Each FileItem In SourceFolder.Files
            i = i + 1
            FileNamesArray(i) = FileItem.Name
            FileNameValue = Application.Trim(VBA.Split(FileNamesArray(i))(2))

                If tableau(iLig, 0) = FileNameValue Then
                   tableau(iLig, 2) = "TRUE" '.Cells(iLig, 2).Interior.Color = xlNone
                    '.Cells(iLig, 3).Value = "TRUE"
                    Exit For
                Else
                    '.Cells(iLig, 2).Interior.Color = RGB(255, 64, 0)
                    tableau(iLig, 2) = "FALSE"
                End If

        Next FileItem
    Next iLig

    Application.ScreenUpdating = True
For y = 2 To dern
    Sheets(1).Cells(y, 3) = tableau(y - 2, 2)
Next
    Set SourceFolder = Nothing
    Set FSO = Nothing

End Sub

essaies de cette facon

il y a peut etre des erreurs , j ai pas tres bien verifié mais potentiellement ca peut aller plus vite dans l idée

Re @ whereisbryan,

J'ai testé votre macro. Le résultat renvoyé est correcte.

Malheuresement, je suis de nouveau face à une boucle qui ne cesse pas de tourner à partir du moment où le nombre de lignes est supérieur à 1000.

Je ne sais pas comment faire pour permettre à ma macro d'aller plus rapidement?

hello @Black_Hole

Je ne vois pas ce qui fait ralentir autant, cela peut tout de meme prendre du temps sur autant d infos donc peut etre attendre meme une 10aine de minutes...

Sinon j ai une autre suggestion: tu devrais passer par la fenêtre des commandes afin d'extraire l intégralité de tes noms de fichiers

mettre tous ces noms sur ta feuille excel dans une nouvelle colonne puis utiliser ta macro pour comparer les 2 colonnes.

Cela va permettre d’éviter d'aller chercher dans un dossier ce qui peut prendre du temps

un lien qui explique comment obtenir le nom de tous tes fichiers:

Rechercher des sujets similaires à "boucle 100 000 lignes"