XL 2016 Ralentissement du code vba

[JORDAN]

bonjour le Forum, tout le monde,

je n'arrive pas à comprendre pourquoi mon code ralentit au fur et à mesure
si je teste avec 10 lignes de recherches tout se passe bien mais
si il y a 100 lignes, la recherche est de plus en plus longue
avez vous une idée svp ? par avance merci

voici mon code :

Option Explicit
Public RefProd As String
Public PxCession As Integer
Public Plage As Range

Private Sub CommandButton1_Click()

' Controle ZFAP

Dim I As Integer
Dim DernierZFAP As Integer

Application.ScreenUpdating = False

DernierZFAP = Range("C" & Rows.Count).End(xlUp).Row

For I = 2 To DernierZFAP
    RefProd = Cells(I, 2)

    Call cherche(RefProd, Plage)

    Worksheets("Contrôle ZFAP").Select
    Cells(I, 4) = Cells(I, 3) - PxCession
    Cells(I, 5) = ((Cells(I, 3) - PxCession) / Cells(I, 3))
Next I

Application.ScreenUpdating = True

End Sub

Sub cherche(RefProd, Plage)
Dim C As Range
Dim LigneRes As Integer
Dim ColonneRes As Integer
Set C = Worksheets("ZPRT").Range("M1:M5000").Find(RefProd, LookIn:=xlValues, lookat:=xlWhole)

Worksheets("ZPRT").Select
If Not C Is Nothing Then
  LigneRes = C.Row
  ColonneRes = C.Column
  PxCession = Worksheets("ZPRT").Cells(LigneRes, ColonneRes + 3).Value / Worksheets("ZPRT").Cells(LigneRes, ColonneRes + 4).Value
Else
  MsgBox ("Référence non trouvée : " & RefProd)
  PxCession = 1000
    End If

Set C = Nothing

End Sub

 

[Calvus]

Bonsoir,

Alors je ne suis pas sûr pour ton code, mais il me semble déjà que tu pourrais gagner en efficacité en remplaçant tes worksheets.select par worksheet.Activate.

Ensuite, si tu écris sur 100 lignes, il semble normal que ce soit plus long que 10 non ?

Tu pourrais passer en Calculating : Xlmanual, couper les événements également.

A+

 

[ChTi160]

Bonjour JORDAN
Bonjour le Fil (Calvus),le Forum
un petit fichier avec par exemple 10 Lignes de données Non confidentielles ;
peut aider a tester !
dans l'attente
Bonne journée
Amicalement
jean marie

 

[JORDAN]

Bonjour Calvus, ChTi160,

merci pour vos réponses

en 1er j'ai changé select par activate mais cela n'a rien changé
ensuite j'ai créé un fichier test et là je ne retrouve pas mon problème, il n'y a pas de talentissement
donc j'ai quelque chose qui fait ralentir la macro quelque part ailleurs dans mon fichier et mes autres macros

il ne me reste plus qu'à trouver d'où provient ce ralentissement 😕
je vous joins quand même mon fichier test

bonne journée

 

[Staple1600]

Bonjour le fil, le forum

Déjà, je changerai ceci
Dim LigneRes As Integer
Dim ColonneRes As Integer
en
Dim LigneRes As Long
Dim ColonneRes As Long
et cela
Dim I As Integer
Dim DernierZFAP As Integer
en ceci
Dim I As Long
Dim DernierZFAP As Long

 

[ChTi160]

Bonsoir JORDAN
Bonsoir Le Fil (JM) ,Le Forum
Une approche !
pas évident avec un fichier avec autant de colonnes Vides (j’espère qu'il n'y a pas que des Formules Lol)
Ne pas hésiter si besoin de Commentaires.
Bonne fin de Soirée
Amicalement
jean marie

 

[JORDAN]

bonsoir Jean-Marie, le Fil,
mdr, il n'y a aucune formule dans ce fichier mais toutes les colonnes sont remplies et il y en a encore d'autres que j'ai supprimées
je viens de faire quelques essais et c'est génial la vitesse à laquelle le tableau est complété, ca n'a rien à voir avec mon code
je traite des fichiers assez volumineux (70000 lignes maxi dans l'onglet "contrôle ZFAP" et 5000 lignes dans l'onglet "ZPRT") et lors d'un essai avec 36000 lignes il y a un message d'erreur "dépassement de capacité"
ce n'est pas gênant, je peux couper mes fichiers mais quelle est le nombre de lignes maxi que je puisse traiter en 1 seule fois ?
environ 1 seconde pour traiter 10000 lignes, que demander de mieux 🙂

merci beaucoup pour ton aide et ton superbe code
je vais essayer de le décortiquer pour bien tout comprendre et principalement les resize et redim ou je bloque un peu trop

Amitiés

 

[ChTi160]

Bonjour JORDAN
Bonjour le Fil,le forum
je pense que c est juste une variable declaree en integer qui devrait l etre en Long (JM)
Je modifie ca et mets des commentaires dans le code.
Bonne journee
Amicalement
Jean marie

 

[ChTi160]

Re
Le fichier Modifié et commenté !
Bonne Journée
Amicalement
Jean marie

 

[mapomme]

Bonsoir à tous,

Pour le FUN, un code assez rapide utilisant des tableaux et un dictionnaire. Le code est dans module1 sous une première version sans commentaire et avec une deuxième version qui est commentée.

edit : bonsoir @Staple1600

 

[Staple1600]

Bonsoir le fil, le forum

@JORDAN

bonsoir Jean-Marie, le Fil,
Amitiés

Suis-je invisible?
Car la déclaration en Long au lieu d'Integer évoqué par JM (ChTi160) dans le message#8, j'en parlais déjà dans le message#5😉
Mais il semblerait que tu as zappé ce message#5 non?

NB: Il se trouve que moi aussi je suis un JM mais JM (Staple1600) 😉

edit: bonsoir mapomme 😉 (alors ça y est 100% opérationnel au niveau respiratoire 😉 ?)

 

[mapomme]

Re,

edit: bonsoir mapomme 😉 (alors ça y est 100% opérationnel au niveau respiratoire 😉 ?)

Oui, le 100% est revenu. Premier noël et premier premier de l’an au fond du lit 😕.

 

[JORDAN]

bonsoir Staple1600, ChTi160, mapomme, le fil, le Forum,

sincèrement désolé Staple1600 qui est également un JM de t'avoir un peu zappé dans ma réponse
j'avis bien pris en compte tes remarques et avais modifié mon 1er code dans ce sens, cela n avait rien changé pour la lenteur de mon code par contre pour le nombre de lignes à traiter c'était parfait, merci pour tes conseils

Merci ChTi160, mapomme pour vos codes qui sont vraiment top et en plus avec toutes les explications c'est génial, 2 fichiers que je vais précieusement conserver et étudier, ils vont souvent me servir
concernant la vitesse d’exécution il n'y a pas beaucoup de différence, il faut monter haut dans le nombre de lignes à traiter pour avoir un infime écart (pour 50000 lignes traitées : 0,38 "et 0,48")

encore merci à vous tous pour votre aide 😎
Au plaisir