XL 2021 Optimisation Code VBA

roybaf · 10 Octobre 2023

Bonjour à tous,

Je cherche un moyen d'optimiser un code VBA car mon classeur prend de plus en plus de volume a chaque exécution de la macro et celle ci met de plus en plus de temps à s'exécuter. Quand le fichier repart à 0, 20 secondes, quand je l'ai utilisé 2 ou trois fois, 15 min voir plus ou plantage. (Je traite 1 000 000 de lignes potentiellement)

VB:

Sub Ventes()

Application.DisplayAlerts = False
Application.ScreenUpdating = False

Sheets("Export_ventes").Range("a2:f1000000").Clear

Sheets("Fichier facture client").Select
        
        
L = Sheets("Fichier facture client").Range("B" & Rows.Count).End(xlUp).Row + 1

For X = 2 To L

    v = Sheets("Export_ventes").Range("A" & Rows.Count).End(xlUp).Row + 1
    
    
    
    If Sheets("Fichier facture client").Range("AK" & X) = "FR" Then
    
    Sheets("Export_ventes").Range("A" & v) = Sheets("Fichier facture client").Range("L" & X).Value
    Sheets("Export_ventes").Range("B" & v) = Sheets("Fichier facture client").Range("M" & X).Value
    Sheets("Export_ventes").Range("C" & v) = Sheets("Fichier facture client").Range("N" & X).Value
    Sheets("Export_ventes").Range("D" & v) = Sheets("Fichier facture client").Range("O" & X).Value
    Sheets("Export_ventes").Range("E" & v) = Sheets("Fichier facture client").Range("U" & X).Value
    
    Sheets("Export_ventes").Range("A" & v + 1) = Sheets("Fichier facture client").Range("L" & X).Value
    Sheets("Export_ventes").Range("B" & v + 1) = Sheets("Fichier facture client").Range("M" & X).Value
    Sheets("Export_ventes").Range("C" & v + 1) = Sheets("Fichier facture client").Range("R" & X).Value
    Sheets("Export_ventes").Range("D" & v + 1) = Sheets("Fichier facture client").Range("O" & X).Value
    Sheets("Export_ventes").Range("F" & v + 1) = Sheets("Fichier facture client").Range("P" & X).Value
    
    Sheets("Export_ventes").Range("A" & v + 2) = Sheets("Fichier facture client").Range("L" & X).Value
    Sheets("Export_ventes").Range("B" & v + 2) = Sheets("Fichier facture client").Range("M" & X).Value
    Sheets("Export_ventes").Range("C" & v + 2) = "44571000"
    Sheets("Export_ventes").Range("D" & v + 2) = Sheets("Fichier facture client").Range("O" & X).Value
    Sheets("Export_ventes").Range("F" & v + 2) = Sheets("Fichier facture client").Range("T" & X).Value
    
    Else:
    
    If Sheets("Fichier facture client").Range("BB" & X) = "oui" Then
    
    
    
    Sheets("Export_ventes").Range("A" & v) = Sheets("Fichier facture client").Range("L" & X).Value
    Sheets("Export_ventes").Range("B" & v) = Sheets("Fichier facture client").Range("M" & X).Value
    Sheets("Export_ventes").Range("C" & v) = Sheets("Fichier facture client").Range("N" & X).Value
    Sheets("Export_ventes").Range("D" & v) = Sheets("Fichier facture client").Range("O" & X).Value
    Sheets("Export_ventes").Range("E" & v) = Sheets("Fichier facture client").Range("U" & X).Value
    
    Sheets("Export_ventes").Range("A" & v + 1) = Sheets("Fichier facture client").Range("L" & X).Value
    Sheets("Export_ventes").Range("B" & v + 1) = Sheets("Fichier facture client").Range("M" & X).Value
    Sheets("Export_ventes").Range("C" & v + 1) = Sheets("Fichier facture client").Range("R" & X).Value
    Sheets("Export_ventes").Range("D" & v + 1) = Sheets("Fichier facture client").Range("O" & X).Value
    Sheets("Export_ventes").Range("F" & v + 1) = Sheets("Fichier facture client").Range("P" & X).Value
    
    Sheets("Export_ventes").Range("A" & v + 2) = Sheets("Fichier facture client").Range("L" & X).Value
    Sheets("Export_ventes").Range("B" & v + 2) = Sheets("Fichier facture client").Range("M" & X).Value
    Sheets("Export_ventes").Range("C" & v + 2) = "44520000"
    Sheets("Export_ventes").Range("D" & v + 2) = Sheets("Fichier facture client").Range("O" & X).Value
    Sheets("Export_ventes").Range("E" & v + 2) = Sheets("Fichier facture client").Range("T" & X).Value
    
    Sheets("Export_ventes").Range("A" & v + 3) = Sheets("Fichier facture client").Range("L" & X).Value
    Sheets("Export_ventes").Range("B" & v + 3) = Sheets("Fichier facture client").Range("M" & X).Value
    Sheets("Export_ventes").Range("C" & v + 3) = "44529000"
    Sheets("Export_ventes").Range("D" & v + 3) = Sheets("Fichier facture client").Range("O" & X).Value
    Sheets("Export_ventes").Range("F" & v + 3) = Sheets("Fichier facture client").Range("T" & X).Value
        
     Else:
    
    Sheets("Export_ventes").Range("A" & v) = Sheets("Fichier facture client").Range("L" & X).Value
    Sheets("Export_ventes").Range("B" & v) = Sheets("Fichier facture client").Range("M" & X).Value
    Sheets("Export_ventes").Range("C" & v) = Sheets("Fichier facture client").Range("N" & X).Value
    Sheets("Export_ventes").Range("D" & v) = Sheets("Fichier facture client").Range("O" & X).Value
    Sheets("Export_ventes").Range("E" & v) = Sheets("Fichier facture client").Range("U" & X).Value
    
    Sheets("Export_ventes").Range("A" & v + 1) = Sheets("Fichier facture client").Range("L" & X).Value
    Sheets("Export_ventes").Range("B" & v + 1) = Sheets("Fichier facture client").Range("M" & X).Value
    Sheets("Export_ventes").Range("C" & v + 1) = Sheets("Fichier facture client").Range("R" & X).Value
    Sheets("Export_ventes").Range("D" & v + 1) = Sheets("Fichier facture client").Range("O" & X).Value
    Sheets("Export_ventes").Range("F" & v + 1) = Sheets("Fichier facture client").Range("P" & X).Value
    
    Sheets("Export_ventes").Range("A" & v + 2) = Sheets("Fichier facture client").Range("L" & X).Value
    Sheets("Export_ventes").Range("B" & v + 2) = Sheets("Fichier facture client").Range("M" & X).Value
    Sheets("Export_ventes").Range("C" & v + 2) = "44571000"
    Sheets("Export_ventes").Range("D" & v + 2) = Sheets("Fichier facture client").Range("O" & X).Value
    Sheets("Export_ventes").Range("F" & v + 2) = Sheets("Fichier facture client").Range("T" & X).Value
    

    
     End If
     End If
    
    
    Next X
    
    Application.ScreenUpdating = True
    
    MsgBox ("Ecritures exportées avec succès sur la feuille 'Export_ventes'")
    
    Application.DisplayAlerts = True
    
End Sub

Celui ci tourne mais je pense qu'il n'est pas très optimisé.

Merci d'avance à tous.

Dranreb · 10 Octobre 2023

Bonjour.
Chargez les données de la Sheets("Fichier facture client") dans un tableau dynamique,
Retaillez un autre tableau dynamique aux dimensions nécessaires,
Versez y les données du premier,
Enfin versez le dans la Sheets("Export_ventes").

Dranreb · 10 Octobre 2023

Vous savez pas faire, je parie, hein ?
Et je ne souhaite pas figer tant de choses dans un code que je pourrais vous écrire. Alors :
Il pourrait être intéressant d'établir une feuille supplémentaire "Règle de ventilation" comportant en L2:U2 les désignations de ces colonnes dans la feuille "Fichier facture client", ainsi que 3 groupes de lignes en A:F les reprenant par de simples formules =$L$2 par exemple, le 1ère groupe devant s'appliquer si la colonne AK des données vaut "FR", le 2ème, sinon, si la colonne BB des données vaut "oui", et le dernier groupe si non plus.
Je pourrais déduire de ces formules les numéros de colonnes à reprendre dans les transferts d'éléments de tableaux.
Vous préparez tout ça et joignez le classeur d'assez peu de données fictives dans les autres feuilles concernées.

roybaf · 10 Octobre 2023

Dranreb à dit:
Vous savez pas faire, je parie, hein ?
Et je ne souhaite pas figer tant de choses dans un code que je pourrais vous écrire. Alors :
Il pourrait être intéressant d'établir une feuille supplémentaire "Règle de ventilation" comportant en L2:U2 les désignations de ces colonnes dans la feuille "Fichier facture client", ainsi que 3 groupes de lignes en A:F les reprenant par de simples formules =$L$2 par exemple, le 1ère groupe devant s'appliquer si la colonne AK des données vaut "FR", le 2ème, sinon, si la colonne BB des données vaut "oui", et le dernier groupe si non plus.
Je pourrais déduire de ces formules les numéros de colonnes à reprendre dans les transferts d'éléments de tableaux.
Vous préparez tout ça et joignez le classeur d'assez peu de données fictives dans les autres feuilles concernées.

J'essaie de déchiffrer

roybaf · 10 Octobre 2023

roybaf à dit:
J'essaie de déchiffrer

Cette Macro n'est qu'une partie du besoin du classeur. Le tableau "Facture Clients" regroupe pas mal d'autres données que j'utilise pour dautres besoins. J'ai préalablement converti les sommes au cours du jours sur plusieurs devises, un calcul de produit constaté d'avance... L'idéal est de maintenir la feuille en l'état et de prendre seulement les quelques infos necessaires à mes écritures comptables.
Il y a dix onglets sur le fichier. Par exemple, je dois rechercher dans différents onglets une clé de chiffrement de paiement pour indiquer le numéro de facture lié, c'est assez volumineux.
J'avais pensé créer les onglets spcécifiques à mes ecritures mais plus les mois passent et plus il y a de données et le classeur s'allourdi et devient inutilisable.

Dranreb · 10 Octobre 2023

En quoi ça remet en cause ce que je vous suggérais ?
N'équipez le classeur que vous joindrez que d'un modèle des feuilles concernées pour le problème posé, ainsi que de la feuille des règles de ventilation en paquets de 3 lignes.

roybaf · 10 Octobre 2023

Dranreb à dit:
En quoi ça remet en cause ce que je vous suggérais ?
N'équipez le classeur que vous joindrez que d'un modèle des feuilles concernées pour le problème posé, ainsi que de la feuille des règles de ventilation en paquets de 3 lignes.

Je suis en train de construire un classeur avec la orme et quelques données

roybaf · 10 Octobre 2023

Dranreb à dit:
En quoi ça remet en cause ce que je vous suggérais ?
N'équipez le classeur que vous joindrez que d'un modèle des feuilles concernées pour le problème posé, ainsi que de la feuille des règles de ventilation en paquets de 3 lignes.

je n'arrive pas a charger mon fichier car trop volumineux malgrès la suppréssion d'onglets et de données

dysorthographie · 10 Octobre 2023

Bonjour,
place ton fichier ICI et fournis nous le lien.

roybaf · 10 Octobre 2023

roybaf à dit:
je n'arrive pas a charger mon fichier car trop volumineux malgrès la suppréssion d'onglets et de données

dysorthographie à dit:
Bonjour,
place ton fichier ICI et fournis nous le lien.

https://www.cjoint.com/c/MJkm1PRdemJ

Merci

Dranreb · 10 Octobre 2023

Je vois que vous n'avez pas fait les feuilles de contrôles des éclatements en 3 lignes comme je vous l'avais suggéré. Ne pouvez vous pas au moins mettre des formules dans les feuilles destinatrices qui récupèrent les infos ? Genre =DECALER(CelluleSource;ENT((LIGNE()-1)/3);0) la CelluleSource étant une cellule de la 1ère ligne des données indiquée en référence de ligne absolue.
Normalement votre classeur aurait du passer.
Supprimez toutes les lignes qui suivent la dernière de chaque feuille.
Ou alors farcissez vous les tonnes de TRésu(LR, 1) = TDon(LD, 18) qui seront 100 × plus rapides que ces nombreuse accès au cellules !

XL 2021 Optimisation Code VBA

roybaf

XLDnaute Occasionnel

Dranreb

XLDnaute Barbatruc

Dranreb

XLDnaute Barbatruc

roybaf

XLDnaute Occasionnel

roybaf

XLDnaute Occasionnel

Dranreb

XLDnaute Barbatruc

roybaf

XLDnaute Occasionnel

roybaf

XLDnaute Occasionnel

dysorthographie

XLDnaute Accro

roybaf

XLDnaute Occasionnel

Dranreb

XLDnaute Barbatruc

Discussions similaires