Accélerer un traitement

[patoq]

Bonjour à tous,

Je cherche un moyen d'accélerer le traitement d'un fichier.

Dans l'exemple ci-joint, je n'ai mis que 300 lignes sur la feuille stj mais on peut aller jusqu'a 1500 lignes .

Il manque aussi quelques onglets pour la taille.

Quand je lance ma macro (sur le bouton vert) ça fonctionne mais j'aimerais réduire le temps d' éxécution un chouilla.

Aprés si je filtre sur les combo c'est encore pire.


Si vous trouvez le moyen d'accélerer le traitement ce serait terrible

merci d'avance

Cdt
Patrice

PS : Bien entendu l'onglet est rempli de MFC et de formules de recherche sur les autres onglets😱😎

 

[Jacou]

Re : Accélerer un traitement

Bonjour,

dans le code, je ne vois pas à quoi sert la boucle for

For i = 0 To 3
With Sheets("STJ")

.Cells(ligpstj, 1) = CStr(Sheets(ws).Cells(cel.Row, 1))
'.Cells(ligpstj, 2) = Sheets(ws).Cells(cel.Row, 28)
'.Cells(ligpstj, 3) = Sheets(ws).Cells(cel.Row, 15)
ligpstj = ligpstj + 1
End With
Next i

en effet la variable i ne semble pas utilisée. donc la macro exécute à priori 4 fois le même traitement pour rien
bonne journée

 

[patoq]

Re : Accélerer un traitement

Bonjour jacou et merci de t'intéresser à mon cas.

La boucle copie 3 fois chaque occurence trouvée.
Mais je pense que ce sont les formules dans chaque ligne qui ralentissent le tout.

En essayant avec bcp moins de lignes, c'est beaucoup mieux.

A +
Pat

 

[Softmama]

Re : Accélerer un traitement

Bonjour,

Je pense que tu pourrais modifier ce code ainsi afin de gagner en vitesse d'exécution, ça évitera de traiter les 1500 lignes une à une et de ne s'intéresser qu'à celles qui t'intéressent :

Sub Extrout()

Dim ws As String
Dim cc As Range
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
ws = " 100-101 TDC MMS080"



With Sheets(ws)
  .Range("BB500").End(xlUp).Offset(1) = "STJ"
  Dim cc As Range
  Set cc = .Range("BB6:BB500").Find(what:="STJ", LookIn:=xlValues, lookat:=xlWhole)
  Do While Not cc.Address(0, 0) <> .Range("BB500").End(xlUp).Address(0, 0)
     .Cells(cc.Row, 1) = CStr(.Cells(cc.Row, 1))
     .Cells(cc.Row, 2) = .Cells(cc.Row, 28)
     .Cells(cc.Row, 3) = .Cells(cc.Row, 15)
     Set cc = .Range("BB6:BB500").FindNext(cc)
  Loop
  .Range("BB500").End(xlUp) = ""
End With
Application.Calculation = xlCalculationAutomatic
End Sub

Cordialement

 

[patoq]

Re : Accélerer un traitement

bonjour Softmama,

Dans mon exemple je vais chercher tous les codes de la colonne A de l'onglet 100-101 correspondant au site STJ en colonne BB du même onglet et je les rapatrie dans la colonne A de l'onglet STJ.

Je suis pas sur que ta macro fasse ça mais merci de m'aider.

C'est surtout la macro sur les combo_change qui rament le plus (20 s sur 500 lignes avec un quadri-coeur).

Merci à tous pour votre aide future.

Cdt
Pat

 

[Softmama]

Re : Accélerer un traitement

Dans mon exemple je vais chercher tous les codes de la colonne A de l'onglet 100-101 correspondant au site STJ en colonne BB du même onglet et je les rapatrie dans la colonne A de l'onglet STJ.

Je suis pas sur que ta macro fasse ça mais merci de m'aider.

Oui en effet, j'avais pas tout capté :

Sub Extrout()

Dim ws As String
Dim cc As Range
Dim tpLig As Long
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
ws = " 100-101 TDC MMS080"

With Sheets(ws)
  .Range("BB500").End(xlUp).Offset(1) = "STJ"
  Set cc = .Range("BB6:BB500").Find(what:="STJ", LookIn:=xlValues, lookat:=xlWhole)
  Do While Not cc.Address(0, 0) <> .Range("BB500").End(xlUp).Address(0, 0)
    Sheets("STJ").Cells(tpLig, 1) = CStr(.Cells(cc.Row, 1))
    Sheets("STJ").Cells(tpLig, 2) = .Cells(cc.Row, 28)
    Sheets("STJ").Cells(tpLig, 3) = .Cells(cc.Row, 15)
    tpLig = tpLig + 1
    Set cc = .Range("BB6:BB500").FindNext(cc)
  Loop
  .Range("BB500").End(xlUp) = ""
End With
Application.Calculation = xlCalculationAutomatic
End Sub

 

[Herdet]

Re : Accélerer un traitement

Bonjour jacou et merci de t'intéresser à mon cas.

La boucle copie 3 fois chaque occurence trouvée.
Mais je pense que ce sont les formules dans chaque ligne qui ralentissent le tout.

En essayant avec bcp moins de lignes, c'est beaucoup mieux.

A +
Pat

Bonsoir Pat,
Le problème vient du fait que "Private Sub Worksheet_Change(ByVal R As Range)" est exécuté à chaque lecture de ligne.
Pour y remédier, il faut déclarer en Public un drapeau FLinit (ou autre), le mettre à True avant la boucle puis à False à la sortie.
Ci-joint ton fichier corrigé dont l'exécution est immédiate.
Je jeterai un coup d' oeuil plus approfondi demain.
Bon travail
Robert

 

[Herdet]

Re : Accélerer un traitement

Pat,
A noter que le nb de lignes de formules de la feuille STJ est insuffisant, il faut au moins 122 groupes de 4 lignes soit
=4*NB.SI($BB:$BB;"STJ") = 488 lignes de résultats

 

[patoq]

Re : Accélerer un traitement

Bonsoir,

Tout d'abord merci à vous deux ,c'est vraiment sympa de m' aider.

Herdet ,le problème de ton drapeau ,c'est que "Private Sub Worksheet_Change(ByVal R As Range)" n'est pas exécuté ,donc mes listes ne sont pas alimentés.

De plus dans les combo_change il n'y a pas d'actualisation vu que la procédure s'arrête avec le drapeau (enfin c'est ce que je pense mais bon je suis pas un expert).

Ssoftmama ton code ne marche encore pas ,ta variable tpLig est déclaré mais j'ai l'impression qu'elle n' a aucune valeur après.

J'ai rajouté une ligne du style tpLig = 7 pour essayer mais ça ne marche pas.

Voila
Merci à tous pour votre aide ,je cherche encore.

Cdt
Pat

 

[Softmama]

Re : Accélerer un traitement

Bonjour,

essaie en virant le Not dans la boucle Do While Not... ?

 

[Herdet]

Re : Accélerer un traitement

Bonsoir,

Herdet ,le problème de ton drapeau ,c'est que "Private Sub Worksheet_Change(ByVal R As Range)" n'est pas exécuté ,donc mes listes ne sont pas alimentés.

Cdt
Pat

Bonjour,
Ca marche très bien mais encore faut-il ajouter l'initialisation des 3 combos à la fin de la procédure Extrout.
J'ai renommé la procédure ini() en Combos_Initialisation()
Dans ce cas, je ne voie plus l'utilité de Private Sub Worksheet_Change(ByVal R As Range) car il ne s' exécute jamais.

Ci-joint fichier corrigé
A+
Robert

 

[Herdet]

Re : Accélerer un traitement

Pat,
Ci-joint version 03 simplifiée :
- suppression drapeau et Sub Worksheet_Change(ByVal R As Range)
- feuilles renommées : Sh_STJ, Sh_MMS080, Sh_TCDMMS080
dans le code VBA c'est plus pratique et sécurisé car indépendant des noms modifiables des feuilles
- initialisation : ajouté effacement du filtre et initialisation de l'index des combos

Robert

 

[Herdet]

Re : Accélerer un traitement

Patrice,
Il peut y avoir un problème dans les TCD car les formules à droite peuvent être écrasées suivant les filtres.
Feuille " 100-101 TDC MMS080" : filtres Année et Catégorie Ordre sur Tous ==> écrasement colonne BB avec formules.
C'est un peu risqué, non ?
Il vaudrait mieux :
- déplacer la colonne BB en A:A et insérer une colonne vide en B:B ainsi le TCD ne causera plus de dégâts.
- adapter la macro Extrout : .Range("BB6:BB2000") ==> .Range("A6:A2000") et .Range("A1:A2000") ==> .Range("C1:C2000")

 

[patoq]

Re : Accélerer un traitement

Bonjour Herdet,
Un énorme merci car là ça va carrément plus vite,et ça j'achèèèèèèteeeee!!!!!

Concernant le TCD j'avais vu ça , je vais déplacer la colonne; par contre vu que l'actualisation se fait par requetes query ,ça ne risque pas de chambouler mon tableau ? je pense que non .

Les feuilles renommées ne genent pas non plus pour l'actualisation ? Y a t'il un risque à les laisser nommer comme avant ?

En tout cas ,jolie travail ,chapeau 😀😀

Merci pour ton aide.

Cdt
Pat

 

[Herdet]

Re : Accélerer un traitement

Bonjour Herdet,
Concernant le TCD j'avais vu ça , je vais déplacer la colonne; par contre vu que l'actualisation se fait par requetes query ,ça ne risque pas de chambouler mon tableau ? je pense que non .
Les feuilles renommées ne gênent pas non plus pour l'actualisation ? Y a t'il un risque à les laisser nommer comme avant ?

La requête Planning article PF 3 mois alimente la base Master_Data et n'a pas d'incidence sur les TCD.
Dans tous mes programmes, j'ai toujours renommé les feuilles dans l'éditeur VBA ce qui a grandement pérennisé le code.
Par exemple, certains avaient une traduction VBA automatique (FR, AN,ES,...) et les noms d'onglets visibles étaient modifiés.

Par expérience : dans le VBA aucun nom et aucune référence en dur ("....") mais un maximum de noms définis pour les feuilles, les variables et les plages de cellules ce qui réduit les modifications du code VBA au strict nécessaire,
... et aussi mettre des explications dans l'en-tête des procédures et le maximum de lignes de commentaires
A+
Robert