XL 2013 Traitement trop long optimisation de code pour générer 2 états

[Merlin258413]

Bonjour à tous je me permets de solliciter votre aide dans le cadre d'une optimisation de code. Ces codes me permettent de sortir 2 états en TDC (ONGLET ETAT DES FAE attendu et ETAT DES FACTURES)
Ces codes fonctionnent parfaitement mais le temps de traitement est très long.
Ce fichier peut comporter énormément de ligne j'ai indiqué ici qu'une petite partie de janvier et février à fin octobre je suis à 1200 lignes d'où le temps du traitement.

Comme les 2 états sont issus du même tableau, une simplification peut être apportée, mais là j'ai atteint mes limites.
Mon projet en PJ explication
J'ai un onglet "Base" dans cet onglet j'ai aussi un tableau intitulé "base" source de mes TDC
Dans cet onglet lorsque je choisis un mois cellule C2 j'ai un code qui est généré.

1- le code va tout d'abord lancer une macro qui se trouve dans le module SUIVI
- Mise à blanc des états intermédiaires (FACTURE et FAE) qui servent à réaliser les TDC
- Section du tableau "Base" et copie sur l'état intermédiaire FAE
- Suppression des colonnes ne correspondant pas au mois sélectionné dans l'état FAE
- Suppression des lignes de cette colonne si j'ai la valeur vide ou ANNULEE
- Sélection de l'état intermédiaire FAE pour une copie dans l'état intermédiaire FACTURE : j'utilise cette étape intermédiaire car je n'aurais pas besoin dans mon état FAE de la colonne facture et dans mon état FACTURE de la colonne Programme
- Traitement de l'état FAE
- Suppression de toutes les valeurs différentes de FAE (à ce stade, je ne peux qu'avoir FAE, FACTURE ou ENCOURS)
- Tri par ordre croissant
- Traitement de l'état FACTURE
- Suppression de toutes les valeurs différentes de FACTURE (à ce stade, je ne peux qu'avoir FAE, FACTURE ou ENCOURS)
- Suppression des colonnes inutiles
- Tri par ordre croissant
- Protection de la feuille
2- Lancement du code MISE EN FORME ne concerne uniquement que la mise en forme de l'état des FAE avant d'obtenir le TDC final "ETAT DES FAE ATTENDU et enfin Actualisation de tous les TDC
J’ai réussi à réduire en le sauvegardant en xlb au lieu de xlsm.
Quand vous le télécharger il faudra le reconvertir en xlsm.
Le mot de passe de protection de l'onglet Base est ProgSN vous le retrouvez dans les modules
Ce fichier peut comporter énormément de ligne j'ai indiqué ici qu'une petite partie de janvier et février à fin octobre je suis à 1200 lignes d'où le temps du traitement.
En espérant avoir été clair et merci pour toute votre aide

 

[Dranreb]

Re : Traitement trop long optimisation de code pour générer 2 états

Je suis fondé à supposer qu'à ce moment là C2 ne montre plus un texte figurant parmi les titres du tableau. Peut être faudrait-il mettre comme source de la liste de validation :

=$H$4:$U$4

Oui c'est possible. If faut ajouter des SsGroup imbriqués si vous ne voulez plus le détail.
mais quels RBU (22), Programme (5) et Interlocuteur (23) faudrait il prendre pour la ligne groupée ?

 

[Merlin258413]

Re : Traitement trop long optimisation de code pour générer 2 états

Rien n'a été changé par rapport à votre fichier

Ce que nous avons en =$H$4:$U$4 ce sont des dates (c'est la ligne masquée)

Ce que nous avons aussi en C2 c'est une date aussi
Dans mon fichier de travail, j'avais une liste (dans un onglet paramètre) avec 01/09/2014.. 01/01/2015 et 01/02/2015.
Afin de réduire mon fichier au maximum pour le mettre sur le site j'ai supprimé cette notion de validation de données

Ce qui est étrange c'est pourquoi tout fonctionne puis après un autre essaie j'ai cette erreur d'exécution ?
Je viens de faire le mois de Mai est il y a aucun soucis je fais octobre est j'ai l'erreur

cdt

 

[Merlin258413]

Re : Traitement trop long optimisation de code pour générer 2 états

Oui c'est possible. If faut ajouter des SsGroup imbriqués si vous ne voulez plus le détail.
mais quels RBU (22), Programme (5) et Interlocuteur (23) faudrait il prendre pour la ligne groupée ?

Nous avons déja un état par BU donc c'est ok

Après je ne comprend pas trop la question

Si
L'activité est identique (3)
Le RBU identique car est fonction de la BU (c'est tout simplement une recherchev)
Le programme est identique (5)
L'interlocuteur aussi (23)

Alors il faut réaliser une somme

Vous ne connaissez pas un moyen pour que je vous envoie mon fichier complet car certainement il doit manquer des infos comme l'explication de l'erreur

 

[Dranreb]

Re : Traitement trop long optimisation de code pour générer 2 états

Il est peut être temps de joindre la version où j'ai fait les factures parce qu'il y a 2 vrais niveaux: les Presta et les BU.
Je me suis amusé à éviter les totaux qui répètent un même montant unique.

Bon ben on prendra les infos du .Contenu(1) alors…

 

[Merlin258413]

Re : Traitement trop long optimisation de code pour générer 2 états

C'est parfait pour le rapport de facture merci

 

[Merlin258413]

Re : Traitement trop long optimisation de code pour générer 2 états

Voila tout fonctionne je ne sais pas pourquoi j'ai cette erreur un coup oui un coup non sur cette ligne
ColDate = WorksheetFunction.Match(FBase.[C2].Text, .Rows(1), 0)

 

[Merlin258413]

Re : Traitement trop long optimisation de code pour générer 2 états

Faites un test sur votre fichier est positionné par exemple la ligne 13 la FAE de Février sur OCtobre
cela génère une erreur est ce que cela vient de là ?

 

[Merlin258413]

Re : Traitement trop long optimisation de code pour générer 2 états

Je viens de vous dire une bétise car pour pas qu'il est d'erreur je dois avoir obligatoirement FAE et Facture
Imaginons qu'un mois donné je n'ai pas de facture il va me remonter une erreur

 

[Dranreb]

Re : Traitement trop long optimisation de code pour générer 2 états

Si seul mars, mai, juin et août fonctionnent, ça pourrait venir de ce que ce sont les seuls à ne pas être abrégés par un point qui apparaît selon un certain format et pas l'autre…

 

[Merlin258413]

Re : Traitement trop long optimisation de code pour générer 2 états

Bonjour donc si je comprends bien c'est la notion de "format de date" qui pose problème. Pour contourner ce problème ne pourrait -on pas remplacer 01/10/2015 qui donc oct.-15 par 201510,201511,201512.... ?
La fonction Match va chercher quelle ligne la 4 ou la 5 je vois Rows (1) 1 à partir d'ou ? je n'arrive pas à voir dans les modules a quel moment on dimensionne le tableau base

merci

 

[Merlin258413]

Re : Traitement trop long optimisation de code pour générer 2 états

Voila je ne peux toujours pas vous envoyer le fichier mais cette parade fonctionne c'est pas très élégant mais ce n'est pas grave si nous avons que cette solution par contre dans les rapports en A1 la formule n'est plus bonne car il ne reconnait pas la date

 

[Merlin258413]

Re : Traitement trop long optimisation de code pour générer 2 états

C'est ok tout fonctionne avec juste le changement de date

Il reste je pense

1- Sortie d'un état vide si pas de valeur par exemple si je demande Novembre 2015
2- L'histoire des totaux de lignes dans les FAE pour une même BU même Programme et même prestation
3- Voir si on peut mettre un peu de couleur dans les sous totaux et total général

Merci

 

[Dranreb]

Re : Traitement trop long optimisation de code pour générer 2 états

Bonjour.
Non parce que ce n'est pas ce que vous voulez voir. Or pour les plages converties en tableaux Excel (qui est un dispositif nouveau et étrange pour moi), Excel 2016 semble exiger des textes pour leurs titres. Le mieux c'est de faire ce que je dis au #31.
Mais on pourrait faire autrement s'il est garanti que la colonne H sera toujours décembre de l'année précédente. Mais ça compliquerait pour pouvoir choisir aussi Etat, la colonne U de dernière situation.
C'est Row(1) à partir de la ligne de début de ce tableaux Excel justement, que vous avez créé vous même pour une raison sûrement bonne quelle qu'elle soit. Il faut donc l'utiliser, puisqu'il existe, votre objet de type ListObject nommé "Base" sinon à quoi servirait il ?

Oh là ! je répondais au poste #40 là, je n'avais pas vu les suivants…

 

[Merlin258413]

Re : Traitement trop long optimisation de code pour générer 2 états

Oui c'est ce que j'ai fait #31 et j'ai utilisé votre remarque #39 et ma réponse en #42. les états fonctionnent c'est super.
De mon coté j'essaie de plancher sur l'état des encours il sort mais je dois revoir la mise en forme
Vous penser que les 3 points #42 sont réalisables ?
merci

 

[Merlin258413]

Re : Traitement trop long optimisation de code pour générer 2 états

Bon j'ai aucun mérite voici le code du rapport des encours
Par contre je ne veux pas voir apparaître les colonnes RBU (2) Interlocuteurs(23)
Je pense qu'il faut que j'intervienne sur cette ligne ?
Ts(Ls, C + 1) = Détail(Choose(C, 3, 22, 5, 23, 4, 7)): Next C, Détail


Sub RapportEnCours()
Dim ColDate&, Te(), Le&, TLgn&(), LDéb1&, Ts(), Ls&, _
BU As SsGroup, Détail, C&, Cel As Range, CelP As Range

Rem. ——— Chargement des données
With FBase.ListObjects("Base").Range
ColDate = WorksheetFunction.Match(FBase.[C2].Text, .Rows(1), 0)
Te = .Rows(2).Resize(.Rows.Count - 1).Value
End With

Rem. ——— Préfiltrage en vue utilisation fonction GroupOrg
ReDim TLgn(1 To UBound(Te))
For Le = 1 To UBound(Te)
If Te(Le, ColDate) = "EN COURS" Then
Ls = Ls + 1: TLgn(Ls) = Le: End If: Next Le
ReDim Preserve TLgn(1 To Ls)
MClassement.Préfiltrer TLgn

Rem. ——— Remplissage de l'image de l'état.
ReDim Ts(1 To 50000, 1 To 7): Ls = 0
For Each BU In GroupOrg(Te, 2, , 3, 5, 4)
Ls = Ls + 1: Ts(Ls, 1) = BU.Id
LDéb1 = Ls + 1
For Each Détail In BU.Contenu
Ls = Ls + 1
For C = 1 To 6
Ts(Ls, C + 1) = Détail(Choose(C, 3, 22, 5, 23, 4, 7)): Next C, Détail
Ls = Ls + 1: Ts(Ls, 7) = "=""R[" & LDéb1 - Ls & "]C"""
Next BU

Rem. ——— Production de l'état et ajout de formules.
FEnCou.Rows(5).Resize(20000).Delete
FEnCou.[A3].Resize(Ls, 7).Value = Ts
Set CelP = FEnCou.[G2]
For Each Cel In FEnCou.[G3].Resize(Ls).SpecialCells(xlCellTypeFormulas)
CelP.Offset(1, 1).Value = Empty
Cel.Resize(, 1).FormulaR1C1 = "=SUBTOTAL(9,R[-1]C)"
Cel.Resize(, 1).Borders(xlEdgeTop).Weight = xlMedium
Set CelP = Cel: Next Cel
Set Cel = FEnCou.[G3].Offset(Ls)
Cel.EntireRow.Columns("A") = "Total général"
Cel.Resize(, 1).FormulaR1C1 = "=SUBTOTAL(9,R1C:R[-1]C)"
Cel.Resize(, 1).Borders(xlEdgeTop).Weight = xlThick
End Sub