Macro Array pour accélérer des Sous totaux d'un classeur dans une autre feuille.

[Broch002]

Bonjour,

Je reviens vers vous, qui m'avez déjà tellement apporté de solutions.

Voila j'ai un classeur pouvant contenir 487 références sur 50 000 lignes. Je souhaites sur une autre feuilles du classeur générer des sous totaux par références (je ne peux pas passer par un tableau croisé dynamique).
Jusqu’à présent, je copiais par macro la feuille , la triais puis lançait toujours par macro la fonction sous total, mais là la macro devient terriblement longue plus le nombre de ligne et de référence augmentent.

j'ai chercher sur la toile et découvert la fonction array qui augmenterait énormément le vitesse des macros. J'ai téléchargé un fichier test et essayé de l'adapter à mon problème, mais je n'y arrive pas. pouvez-vous m'aider?

Voici la macro qui plante:
Sub TRIER_PAR_REFERENCE()
'macro trouvée sur Internet et modifié
Dim Tblo1() As Variant
Dim orders As ListObject
Dim Cel As Range
Dim f As Integer
Workbooks("test.xlsm").Activate
Set orders = Sheets("A-1").ListObjects("Tableau1")
For Each Cel In orders.DataBodyRange.Columns(1).Cells
If Cel.Value = "Jean" Then
f = f + 1
ReDim Preserve Tblo1(1 To 13, 1 To f)
Tblo1(1, f) = Cel.Offset(0, -2).Value
Tblo1(2, f) = Cel.Offset(0, -1).Value2
Tblo1(3, f) = Cel.Value
Tblo1(4, f) = Cel.Offset(0, 1).Value
Tblo1(5, f) = Cel.Offset(0, 2).Value
End If
Next Cel

With Sheets("A-1")
.Columns("H:K").Copy Destination:=Sheets("Réalisation").[B2]
End With

Workbooks("test.xlsm").Activate
Set orders = Sheets("A-1").ListObjects("Tableau1")
For Each Cel In orders.DataBodyRange.Columns(1).Cells
If Cel.Value = "Charles" Then
f = f + 1
ReDim Preserve Tblo1(1 To 13, 1 To f)
Tblo1(1, f) = Cel.Offset(0, -2).Value
Tblo1(2, f) = Cel.Offset(0, -1).Value2
Tblo1(3, f) = Cel.Value
Tblo1(4, f) = Cel.Offset(0, 1).Value
Tblo1(5, f) = Cel.Offset(0, 2).Value
End If
Next Cel

With Sheets("A-1")
.Columns("H:K").Copy Destination:=Sheets("Réalisation").[B3]
End With

Workbooks("test.xlsm").Activate
Set orders = Sheets("A-1").ListObjects("Tableau1")
For Each Cel In orders.DataBodyRange.Columns(1).Cells
If Cel.Value = "Christian" Then
f = f + 1
ReDim Preserve Tblo1(1 To 13, 1 To f)
Tblo1(1, f) = Cel.Offset(0, -2).Value
Tblo1(2, f) = Cel.Offset(0, -1).Value2
Tblo1(3, f) = Cel.Value
Tblo1(4, f) = Cel.Offset(0, 1).Value
Tblo1(5, f) = Cel.Offset(0, 2).Value
End If
Next Cel

With Sheets("A-1")
.Columns("H:K").Copy Destination:=Sheets("Réalisation").[B4]
End With

End Sub

 

[pierrejean]

Re : Macro Array pour accélérer des Sous totaux d'un classeur dans une autre feuille

Re

En supposant avoir compris !!

@ David : Utilisation de tableau resultat encore plus rapide que Transpose

 

[david84]

Re : Macro Array pour accélérer des Sous totaux d'un classeur dans une autre feuille

Re

@ David : Utilisation de tableau resultat encore plus rapide que Transpose

Effectivement puisque tu passes par un array que tu recopies d'un coup dans la feuille, cela est logique (bien vu) !
Ton fichier me semble correspondre à la demande mais je ne saisis toujours pas l'intérêt d'avoir toutes ces colonnes vides (mais si le demandeur le demande, c'est qu'il doit avoir ses raisons).
A+

 

[Broch002]

Re : Macro Array pour accélérer des Sous totaux d'un classeur dans une autre feuille

Re bonjour à tous.

je viens de tester le fichier, et cela fonctionne impécablement, avec la génération des lignes par le bouton création de la base.
Dès que j'importe mes données, soit 13 000 lignes actuellement, il se produit un bogue, au débogage l'erreur "erreur d'exécution 11 divisions par zéro" sur la ligne de la macro "tabres(n, 11) = b3(n) / b2(n)".
C'est un fait, j'ai dans les colonnes valeurs des zeros. lorsque je les remplace par des "1", l'erreur subsiste avec le message "dépassement de capacité"
D'où peut venir ce bug?::

Concernant les colonnes, elles viennent du traitement de ma macro, enregistrée par Excel, qui créait les sous-totaux
Par la fonction intégrée puis en recopiant les valeurs et en éliminant tout ce qui n'est pas total, il restait ces colonnes. elles peuvent être éliminées, puisque inutiles.

Amicalement.

Broch002

 

[pierrejean]

Re : Macro Array pour accélérer des Sous totaux d'un classeur dans une autre feuille

Re

teste deja comme ceci
Si persistance du bug reviens en indiquant si possible la valeur de b3(n)

 

[Broch002]

Re : Macro Array pour accélérer des Sous totaux d'un classeur dans une autre feuille

Re bonjour, pierrejean.

Je viens de tester et a priori cela fonctionne.

je l'intègre dans l'original et vous tiens informé.

A+

Broch002.

 

[Broch002]

Re : Macro Array pour accélérer des Sous totaux d'un classeur dans une autre feuille

Bonjour, à tous.

Bonne nouvelle pierrejean, ta macro intégrée à mon fichier fonctionne impécablement.
Comment puis-je modifier la colonne de trie pour obtenir les sous totaux (test sur la colonne 1) des colonnes 2 ou 3 ou 4. la feuille étant toujours "A-1".
La colonne 1 permet le trie du nom la 2 celle de la date etc...
j'ai essayé en modifiant la ligne:
"Tablo = Sheets("A-1").Range("A2:L" & Sheets("A-1").Range("A" & Rows.Count).End(xlUp).Row)
For i = LBound(Tablo, 1) To UBound(Tablo, 1)"
en:
Tablo = Sheets("A-1").Range("D2:L" & Sheets("A-1").Range("A" & Rows.Count).End(xlUp).Row)
For i = LBound(Tablo, 1) To UBound(Tablo, 1)
mais cela bogue.

Bonne journée.

Broch002

 

[Broch002]

Re : Macro Array pour accélérer des Sous totaux d'un classeur dans une autre feuille

Bonsoir, le forum.

Avez-vous une réponse à ma question concernant la modification de la macro de pierrejean pour avoir les
sous-totaux des autres colonnes de mon fichier (colonne G par exemple) dans la feuille réalisation .

Merci d'avance et bonne soirée.

Broch002

 

[david84]

Re : Macro Array pour accélérer des Sous totaux d'un classeur dans une autre feuille

Re
Les sous-totaux ramenés dans la feuille Réalisation n'ont rien à voir avec la macro de pierrejean.
Ce sont des formules. Utilise la fonction SOUS.TOTAL en choisissant l'argument 1 qui te convient (moyenne, NB, NBVAL, MAX,...).
L'argument 2 correspond à la colonne de l'onglet A-1 que tu veux traiter.
Par exemple, pour ramener le nombre de valeurs de la colonne G de A-1 :=SOUS.TOTAL(3;'A-1'!G:G)
Regarde l'aide d'Excel au sujet de cette fonction SOUS.TOTAL.
A+

 

[Broch002]

Re : Macro Array pour accélérer des Sous totaux d'un classeur dans une autre feuille

Bonsoir, david84.

j'ai du mal m'exprimer dans ma demande, la macro de pierrejean me renvoie des totaux de chaque client "colonne A", sous forme d'un listing dans la feuille réalisation, avec la quantité, la valeur et la marge.
Elle est parfaite
Ce que je cherche à faire, c'est de l'adapter pour pouvoir traiter les totaux de la colonne G "références produits" et les visualiser sur la feuille réalisation comme pour les clients (j'ai 500 références).

exemple:
Ref QT CA Marge
Ref284 581 2725 538,03
Ref002 621 2948 575

De pouvoir le faire avec la colonne D qui correspond aux commerciaux

Com QT CA Marge
Dede 581 2725 538
Momo 621 2948 575

Bien sûr qu'avec les formules pour un fichier Excel c'est la meilleur solution, mais pas pour des personnes ne connaissant pas les tableurs, et encore moins en clientèle. D'où la création de mon fichier qui équipé de macro enregistrée avec l'enregistreur d'Excel apporte les réponses aux questions que peuvent leur poser leurs clients.
Par contre, là remonté des utilisateurs est qu'elles sont de plus en plus longues, bientôt 5 mois d'utilisation.
J'avais entendu que l'on pouvait grâce aux Array accélérer les macro jusqu'à mille fois, d'ou ma question.
La macro de pierrejean répond parfaitement à nos besoins, ce que je cherche à faire c'est de l'adapter à d'autres tries comme expliqué précédemment.

Je renouvelle mes remerciements à vous, à pierrejean, à mapomme pour votre contribution.
Si vous pouviez juste me communiquer la clé dans la macro pour sélectionner la colonne à trier ce serait super.

Bonne soirée.

Broch002

 

[david84]

Re : Macro Array pour accélérer des Sous totaux d'un classeur dans une autre feuille

Re
Ta demande peut s'interpréter de différentes façons : nombre de références par nom ok, mais il existe des références en double pour un même nom donc quel doit être le résultat à obtenir ? Un bon exemple valant mieux qu'un discours et pour que ce soit plus clair, je te conseille de faire un fichier exemple avec une cinquantaine de lignes (comment veux-tu tester les résultats sur 50000 lignes, avec de surcroît une fonction ALEA qui empêche de "fixer" la base de données ?) et de noter manuellement les résultats attendus sur la feuille réalisation.
Je pense que l'on y verra plus clair (moi en tous cas !).
A+

 

[Broch002]

Re : Macro Array pour accélérer des Sous totaux d'un classeur dans une autre feuille

Bonjour, david84.

Je joins à ce message un fichier comportant la base contenant une cinquantaine de ligne et 3 feuilles réalisation.
le fichier originale n'en comportant qu'une, qui en fonction de la macro lancée, renseigne les infos contenues dans les colonnes A (celle de pierrejean), la colonne d ou la colonne G, h etc....

A+

Broch002.

 

[pierrejean]

Re : Macro Array pour accélérer des Sous totaux d'un classeur dans une autre feuille

Re

Vois si je t'ai bien compris

 

[Broch002]

Re : Macro Array pour accélérer des Sous totaux d'un classeur dans une autre feuille

Bonjour, pierrejean.

ton fichier remplis toutes les fonctions, c'est génial.
Dans mon fichier les macros sont commandées par des boutons dans le ruban, généré par Custom ui Editor.
Puis-je utiliser ce type de lancement avec ta macro. Et comment l'adapter.
Je voudrais bien vous faire parvenir le fichier, mais il est trop gros, 10 MO.

A+

Broch002

 

[david84]

Re : Macro Array pour accélérer des Sous totaux d'un classeur dans une autre feuille

Bonsoir,
@pierrejean : bravo à toi ! Heureusement que tu es là car je n'avais pas compris ce que le demandeur voulait obtenir.
@Broch002 : es-tu sûr de vouloir passer par la personnalisation du ruban pour lancer les macro ?
Quel en est l'intérêt ?
Pourquoi ne pas simplement les lancer à l'activation de la feuille ?
Ci-joint un lien vers une discussion qui t'explique comment procéder si tu choisis la solution du ruban personnalisé.
Si j'ai bien compris, cette personnalisation existe déjà : dans ce cas, il ne te reste plus qu'à ajouter un bouton de commande et d'y affecter la macro de pierrejean.
A+

 

[Broch002]

Re : Macro Array pour accélérer des Sous totaux d'un classeur dans une autre feuille

Bonjour, le forum.

J'ai bien avancé avec le superbe travail de pierrejean, après quelques jours de congés, je me replonge dans mon fichier.
Je dois répondre à une question des utilisateurs, est-il possible de synthétiser les résultats de l'année en cour avec celles de l'année passée (deux pages actuellement), en une seule.
J'ai essayé comme un grand mais je n'y arrive pas. Ci-joint le fichier test de pierrejean, complété de la feuille "A" (la même que "A-1" dans ce cas) et de la feuille réalisation telle que j'essaye d'obtenir.

Merci d'avance pour votre aide.

Broch002