Exécution code trop lent

Le 15/08/2019 à 16:02

bonjour,

j'ai adapté ce code sur mon fichier et il est assez précis mais horriblement lent dans l'exécution, qlq aurait il une idée pour l'améliorer?

le fichier "test" est mon fichier que je souhaite compléter avec 2 infos qui se trouvent sur un autre fichier. "stm", les 2 sont sur le réseau dans le même répertoire.

donc en appuyant sur le bouton, le code ouvre le fichier "stm" et va chercher en colonnes 12 (lot) et 14 (rack), les infos en fonction de commande en colonne 3 et de l'article en colonne 8, pour les copier en colonnes 6 et 7 de la feuille "test".

merci d'avance pour votre aide.

10stm.xlsx (285.01 Ko)
11test.xlsm (877.81 Ko)
Le 15/08/2019 à 16:05

Bonjour,

Tu pourrais tester

Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
chemin = "C:\Users\Moutcheba_c\Desktop"
Workbooks.Open (chemin & "\" & "stm.xls")
Set dict = CreateObject("scripting.dictionary")
    With ThisWorkbook.Sheets("Sorties")
For o = 2 To .Range("C" & Rows.Count).End(xlUp).Row
           commande = CStr(.Range("C" & o))
            article = CStr(.Range("H" & o))
For p = 2 To Workbooks("stm").Sheets("stm").Range("C" & Rows.Count).End(xlUp).Row
        If commande = CStr(Workbooks("stm").Sheets("stm").Range("C" & p)) And _
            article = CStr(Workbooks("stm").Sheets("stm").Range("H" & p)) Then
                lot = Workbooks("stm").Sheets("stm").Range("L" & p)
               rack = Workbooks("stm").Sheets("stm").Range("N" & p)
               clé1 = commande & lot
               clé2 = commande & rack
                        If Not dict.exists(clé1) Then
                        If Not repetelot Then .Range("F" & o) = lot
                        dict.Add clé1, 1
                    End If
                        If Not dict.exists(clé2) Then
                        If Not repeterack Then .Range("G" & o) = rack
                        dict.Add clé2, 1
                        Exit For
                    End If
                End If
             Next p
          End If
        Next o
     End With
  Workbooks("stm").Close
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

En espèrant que cela t'aide

Le 15/08/2019 à 16:49

bonjour, non ça n'améliore pas la situation. merci bcp.

Le 15/08/2019 à 16:52

bonjour

resalut James au passage

quand il s'agit de mettre en relation 2 tables de données, il faut RECHERCHEV

as-tu essayé ?

amitiés

Le 15/08/2019 à 16:53

autre solution Power BI

tout dépend de l'usage du fichier final

Le 15/08/2019 à 17:50

Bonjour à tous,

Un essai ...

Pour le test, je n'ai pas mis les tries dans le code.

Trier le fichier "test.xlsm" sur la colonne "C" (Commande).

Trier le fichier "stm.xlsx" sur la colonne "C" (N° de commande) > attention que toutes les données soient sélectionnées au moment du trie, le fichier exemple n'ayant pas d'en-têtes, le trie manuel ne sélectionne que la colonne C.

Puis, tester ce code ...

Si les colonnes sont triées, dès que numéro de commande du fichier test.xlsm est plus petit que le numéro de commande du fichier stm.xlsx, l'on peut sortir de la 2e boucle For p = 2 To ....... > il y a donc un gain considérable de temps en ne comparant pas les données inutilement.

Private Sub CommandButton1_Click()

    Application.ScreenUpdating = False

    Debut = Time

    chemin = "G:\Document en Commun\Stocks"

    Workbooks.Open (chemin & "\" & "stm.xlsx")

    Set dict = CreateObject("scripting.dictionary")

    With ThisWorkbook.Sheets("Sorties")
        For o = 2 To .Range("C" & Rows.Count).End(xlUp).Row
            commande = CStr(.Range("C" & o))
            article = CStr(.Range("H" & o))
            For p = 2 To Workbooks("stm.xlsx").Sheets("stm").Range("C" & Rows.Count).End(xlUp).Row

                If commande < CStr(Workbooks("stm.xlsx").Sheets("stm").Range("C" & p)) Then Exit For

                If commande = CStr(Workbooks("stm.xlsx").Sheets("stm").Range("C" & p)) And _
                   article = CStr(Workbooks("stm.xlsx").Sheets("stm").Range("H" & p)) Then

                    lot = Workbooks("stm.xlsx").Sheets("stm").Range("L" & p)
                    rack = Workbooks("stm.xlsx").Sheets("stm").Range("N" & p)
                    clé1 = commande & lot
                    clé2 = commande & rack

                    If Not dict.exists(clé1) Then
                        If Not repetelot Then .Range("F" & o) = lot
                        dict.Add clé1, 1
                    End If

                    If Not dict.exists(clé2) Then
                        If Not repeterack Then .Range("G" & o) = rack
                        dict.Add clé2, 1
                        Exit For
                    End If
                End If
            Next p
    Next o
End With
Workbooks("stm.xlsx").Close
Application.ScreenUpdating = True

fin = Time
MsgBox "Le temps d'exécution est de : " & fin - début
End Sub

Ici > 2, 35 secondes

ric

Le 16/08/2019 à 12:21

Bonjour, la modif apporte en effet de la rapidité mais le résultat n'est pas fiable. c'est néanmoins une très bonne approche de ne pas tester tous les numéros de commande. ce qui m'a conduit à limiter la zone de recherche en fonction de la date. étant donné que les données sont ajoutéés jour après jour et qu'il n'y a pas de modif sur le passé, j'ai essayé depuis hier de choisir une date de début et une date de fin, ainsi la ligne de la date de début (x) et la ligne de la date de fin (y) sont recuperées pour :

remplacer : For o = 2 To .Range("C" & Rows.Count).End(xlUp).Row

par for o = x to y

ce qui donne :

x = .Range("B:B").Find(CDate(TD), lookat:=xlWhole).Row

y = .Range("B:B").Find(CDate(TF), lookat:=xlWhole).Row

for o = x to y

j'ai juste un souci sur la "y" et je n'arrive pas à trouver de solution.

il me revoie la ligne de la première cellule qui a la date de fin, je ne sais pas comment faire pour qu'il revoie plutôt la dernière ligne qui a la date de fin.

merci pour votre aide svp.

Pedro.

Le 16/08/2019 à 12:35

Bonjour,

... la modif apporte en effet de la rapidité mais le résultat n'est pas fiable ...

J'ai aussi constaté ce phénomène et je suis à tenter d'en trouver la source.

Pour ton autre question, peut-être un "FindNext" et comparaison des dates.

ric

Le 16/08/2019 à 12:49

merci à vous pour votre temps, je test.....

Le 16/08/2019 à 15:44

Bonjour à tous,

Dis-moi ... les numéros de commandes et les numéros d'articles sont-ils supposés de toujours être seulement des chiffres ?

ric

Le 16/08/2019 à 16:32

bonjour, oui uniquement des chiffres, toujours 6 chiffres pour les articles et de 4 à 9 chiffres pour les commandes. merci.

Le 16/08/2019 à 16:57

Bonjour à tous,

Dans ce cas ...

Dans ce fichier, c'est le "Tableau2", il faudra en tenir compte dans la macro Sub TriEtConversion() du module1 ... si tu adaptais le code dans un autre fichier.

Le fichier de lecture de données est le "stm.xlsx" dans le même dossier .

Sur la base que j'ai soumise plus avant ... mais, corrigée ...

Les données du fichier test.xlsm > feuille "Sorties" seront triées colonne C et H.

Idem pour les données du fichier "stm.xlsx" > les données seront aussi triées sur les colonnes C et H.

Pour vérifier qu'il fonctionne bien > j'ai lancé le fichier d'origine avec le code d'origine en ajoutant que les cellules écrites auraient un fond jaune.

J'ai effacé les données des cellules jaunes et lancé le nouveau code avec le même résultat ... sauf que le temps est passé de 9 minutes et quelques à 2.31 secondes d'exécution.

Un essai...

ric

9test.xlsm (865.41 Ko)
Le 16/08/2019 à 22:27

bonjour @Ric,

bonjour le forum,

là c'est vraiment nickel, le résultat est excellent et en 2 petites secondes.

merci bcp.

Pedro.

Le 17/08/2019 à 09:35

ric

Rechercher des sujets similaires à "execution code trop lent"