Code optimisable ?

[zourite]

Salut !

J'utilise un code qui reproduit une formule (matricielle) puis la transpose.
Elle fonctionne mais est assez lente... environ 3 minutes de chargement...

Pensez-vous que en gardant un peu du mécanisme, on peut l'accélerer?

Sub Traitement_DateSemaine_BD_9_Ter()

    Range("A3").FormulaArray = _
        "=IF(RC[2]="""","""",IF(RC[2]=""Commande"",iso(RC[3]),IF(RC[2]=""Livraison"",iso(MAX(IF((RC[3]>=R3C15:R65536C15)*(RC[1]=R3C2:R65536C2),R3C15:R65536C15))))))"
    Range("A3").AutoFill Destination:=Range("A3", Cells(Range("B1"), 1)), Type:=xlFillDefault
    
    Range("A3", Cells(Range("B1"), 1)).Copy
    Range("A3").PasteSpecial Paste:=xlPasteValues

Je pense que la perte de temps se situe au niveau de l'AutoFill

merci pour vos conseils !

 

[tototiti2008]

Re : Code optimisable ?

Bonjour zourite,

ta formule fonctionne en utilisant une fonction "iso"
Il me semble que ce n'est pas une fonction native Excel (mais je peux me tromper)
Dans ce cas, tu devrais avoir du code VBA quelque part pour définir la "Function ISO" qui risque de beaucoup nous manquer pour te répondre
D'autre part, si nous savions sur quels types de données ta formule est appliquée, ça ne gâcherait rien...
un petit fichier exemple, peut-être ?

 

[zourite]

Re : Code optimisable ?

Bonjour tototiti2008,

Merci pour ton aide encore une fois

Voilà pour la fonction ISO: ca m'embete de pas donner l'auteur, mais je ne me souviens plus, je l'ai trouvé sur un poste et l'ai un peu modifié ...

Function ISO(r) 'Transcription ISO d'une date grégorienne.
Application.Volatile
Dim d2&, d3&, d4&
  d2 = r + 1 - Weekday(r, vbMonday)
  d3 = DateSerial(Year(d2 + 3), 1, 1)
  d4 = d3 + 1 - Weekday(d3, vbMonday) - (Weekday(d3, vbMonday) > 4) * 7
  ISO = Year(d3) & " S" & Format((d2 - d4) \ 7 + 1, "00")
End Function

et en joint mon fichier.. j'ai un peu galérer pour le réduire.. à la base il faisait 20mo
Donc, il faut prendre en compte le fait que normalement, il y a beaucoup plus de fournisseur différent, de ligne, de date.. etc...

d'avance merci !

 

[tototiti2008]

Re : Code optimisable ?

Re,

Ta formule matricielle se base sur le contenu de la colonne O qui est désespérément vide dans ton exemple
Est-ce normal ?

 

[zourite]

Re : Code optimisable ?

Re,
Absolument pas, je me suis trompé. J'ai refait le fichier joint et je l'ai vérifié.

Mercii !!

 

[tototiti2008]

Re : Code optimisable ?

Re,

Peut-être déjà limiter les plages de cellules dans la formule, éviter d'aller jusqu'en ligne 65536 mais seulement jusqu'à la dernière ligne remplie (ou la cellule B1, je ne connais pas trop la différence dans ton vrai fichier) ?

Sub Traitement_DateSemaine_BD_9_Ter()
Dim Ligne as long
Ligne = range("B1").value
    Range("A3").FormulaArray = _
        "=IF(RC[2]="""","""",IF(RC[2]=""Commande"",iso(RC[3]),IF(RC[2]=""Livraison"",iso(MAX(IF((RC[3]>=R3C15:R" & Ligne & "C15)*(RC[1]=R3C2:R" & Ligne & "C2),R3C15:R" & Ligne & "C15))))))"
    Range("A3").AutoFill Destination:=Range("A3", Cells(Range("B1"), 1)), Type:=xlFillDefault
    
    Range("A3", Cells(Range("B1"), 1)).Value = Range("A3", Cells(Range("B1"), 1)).Value

 

[ROGER2327]

Re : Code optimisable ?

Bonjour à tous

Un essai :

Sub Traitement_DateSemaine_BD_9_Ter()
Dim i&, j&, n&, x#, A As Range, B(), C(), D(), O(), oDat()
  With Range("B3")
    Set A = Range(.Cells, Cells(Rows.Count, .Column).End(xlUp)).Offset(0, -1)
  End With
  n = A.Count
  ReDim oDat(1 To n, 1 To 1)
  B = A.Offset(0, 1).Value
  C = A.Offset(0, 2).Value
  D = A.Offset(0, 3).Value
  O = A.Offset(0, 14).Value
  For i = 1 To n
    Select Case C(i, 1)
      Case "Commande": oDat(i, 1) = ISO(D(i, 1))
      Case "Livraison"
        x = 0
        For j = 1 To n
          If B(j, 1) = B(i, 1) Then If O(j, 1) > x Then If D(i, 1) >= O(j, 1) Then x = O(j, 1)
        Next
        If x Then oDat(i, 1) = ISO(x)
    End Select
  Next
  A.Value = oDat
End Sub

Quant à la fonction ISO, il me semble que j'ai naguère commis quelque chose du même genre,
à cette différence près que je respectais la notation préconisée par la norme ISO…​

ROGER2327
#5003

Dimanche 8 Pédale 138 (La machine à inspirer l'amour, ST)
12 Ventôse An CCXIX
2011-W09-3T00:14:46Z

 

[zourite]

Re : Code optimisable ?

Bonjour tototiti2008, ROGER2327,

Merci pour votre aide !!

Donc j'ai testé vos deux codes:

tototiti2008: Selon les tests que j'ai fait, ca à l'air de trés bien marcher
au niveau de la rapidité sur mon fichier original, je passe de 3-4 min de traitement à 10-20seconde, et beaucoup moins de risque de plantage du au chargement
Un tel résultat était pour moi inespéré ! Mais j'avais oublié que grâce à ce forum, tout est possible !!!

ROGER2327: J'ai également testé votre code cependant, je n'ai pas eu d'erreur, cependant il ne s'est rien produit (rien ne s'affiche dans la colonne A) pas de numéro de semaine.
Je pense que c'est due aux différences qu'il y a entre le fichier d'origine et le fichier d'exemple.. Mais je ne trouve pas l'origine de l'erreur..humm
Donc je vais continuer les tests pour essayer de comprendre le fonctionnement de ce code, et trouver l'origine de l'erreur !

Merci à vous !!

bonne journée

 

[ROGER2327]

Re : Code optimisable ?

Re…

Le code proposé fonctionne dans le classeur que vous avez fourni. Testé sur deux mille lignes, il donne le résultat en moins d'une seconde (sur ma machine).
Il va de soi que, si vos colonnes de travail ne sont pas les colonnes A, B, C, D, O et que les données ne se trouvent pas à partir de la ligne 3, il faut faire quelques aménagements…

J'ajoute des commentaires :

Sub Traitement_DateSemaine_BD_9_Ter()
Dim i&, j&, n&, x#, A As Range, B(), C(), D(), O(), oDat()
  With Range("B3") 'Première donnée de la colonne "Fournisseur"
   Set A = Range(.Cells, Cells(Rows.Count, .Column).End(xlUp)).Offset(0, -1) 'Plage de destination.
'Ici, colonne A, c'est-à-dire colonne des données "Fournisseur" décalée d'une colonne à gauche.
   End With
  n = A.Count 'Nombre de lignes à traiter.
   ReDim oDat(1 To n, 1 To 1)
  B = A.Offset(0, 1).Value 'Plage des données "Fournisseur".
'Ici, colonne B, c'est-à-dire plage de destination décalée d'une colonne à droite.
   C = A.Offset(0, 2).Value 'Plage des données "Commande ou livraison".
'Ici, colonne C, c'est-à-dire plage de destination décalée de deux colonnes à droite.
   D = A.Offset(0, 3).Value 'Première plage des données "Date".
'Ici, colonne D, c'est-à-dire plage de destination décalée de trois colonnes à droite.
   O = A.Offset(0, 14).Value 'Deuxième plage des données "Date".
'Ici, colonne O, c'est-à-dire plage de destination décalée de quatorze colonnes à droite.
   For i = 1 To n
    Select Case C(i, 1)
      Case "Commande": oDat(i, 1) = ISO(D(i, 1))
      Case "Livraison"
        x = 0
        For j = 1 To n
          If B(j, 1) = B(i, 1) Then If O(j, 1) > x Then If D(i, 1) >= O(j, 1) Then x = O(j, 1)
        Next
        If x Then oDat(i, 1) = ISO(x)
    End Select
  Next
  A.Value = oDat 'Dépôt du résultat dans la colonne de destination.
End Sub

En général, j'essaie de répondre au problème posé.
Si vous voulez une solution à un autre problème,
posez cet autre problème…​

ROGER2327
#5004

Dimanche 8 Pédale 138 (La machine à inspirer l'amour, ST)
12 Ventôse An CCXIX
2011-W09-3T10:35:01Z

 

[tototiti2008]

Re : Code optimisable ?

Bonjour zourite, Bonjour Roger,

Il est clair que Roger te propose une vraie optimisation, moi je me suis contenté de pointer un manque évident de ta macro d'origine, sa solution doit donc être beaucoup plus rapide que la mienne.
Je t'invite donc à regarder de plus près sa solution...

 

[zourite]

Re : Code optimisable ?

Bonsoir Roger, tototiti,

Merci de continuer à m'aider !

Désolés de ma réponse tardive, j'ai plus rallumer le pc ces derniers jours.
La réponse de Roger comme vous l'avez dit, est vraiment très rapide, et du coup grâce aux commentaires, ca m'a permis de l'adapter !

Mais vos deux réponses m'aident a avancer et à mieux comprendre mes erreurs!

Encore merci !
Et à bientot pour de nouvelles aventures