Besoin aide VBA

Spinzi

XLDnaute Impliqué
Bonjour à tous,

étant toujours en quête d'optimisation et de rapidité, mais cependant nul en macro/VBA, je viens vers vous pour la construction d'un fichier que j'avais réussi avec des formules, mais qui s'avère légèrement long à l'éxécution (3-4 minutes).

Je pense procéder par phases (j'ai différentes bases mais surtout un gros fichier à optimiser).

J'ai déjà commencé par tout mettre en tableaux excel car j'ai lu plusieurs fois que les tableaux accéléraient la vitesse des programmes VBA.

Dans un premier temps je souhaite donc modifier le code VBA présent dans le fichier ci dessous qui permet d'alimenter les colonnes A, B et C avec des informations sans doublon.
Ces colonnes sans doublons serviront plus tard pour alimenter plusieurs listes de validation.

J'aimerai donc une modification du code pour prendre en compte le tableau qui est dynamique et qui est donc ajusté pile poil à la taille du tableau et donc des données : pas besoin de vérifier des milliers de lignes pour rien.

Si vous avez des questions, n'hésitez pas et merci d'avance pour les améliorations que vous pourriez apporter à ce fichier.
 

Pièces jointes

  • TravauxHercule.xlsm
    450.8 KB · Affichages: 195
  • TravauxHercule.xlsm
    450.8 KB · Affichages: 199
  • TravauxHercule.xlsm
    450.8 KB · Affichages: 204
Dernière édition:

Spinzi

XLDnaute Impliqué
Re : Besoin aide VBA

Bonjour,

ce rapport ressemble à ce que je recherche. Est-il possible de l'afficher dans l'onglet où il y a les listes de validation ?
Ajouter des totaux pour département et nom de centre de couts ? Parce que des lignes sont susceptibles d'être rajoutées à la main, après le moulinage de la macro. Ce qui fait que si les totaux sont édités par macro, les nouvelles lignes rajoutées ne seront pas prises en compte dans les totaux ? J'hésite donc encore à utiliser le résultat de la feuille budget ou de la feuille rapport, sachant qu'ensuite je fais une mini synthèse (sommeprod) en récapitulant l'évolution pour tel département.

Pour moi le vrai objectif c'est que les libellés de colonnes conditionnent vraiment les infos à rapporter. Si je mets en Cellule J2 "ARC 2012", je souhaiterai qu'il me fasse la correspondance avec la colonne nommée "ARC 2012" dans la base et me rapporte les infos.

Merci
 

Dranreb

XLDnaute Barbatruc
Re : Besoin aide VBA

Bonjour.
Serait-il possible d'ajouter une ligne devant la ligne de titres de Base en cours pour y sélectionner les colonnes qu'on veut dans le rapport ?
En attendant… pas très évident à mettre au point tout ça. Essayez comme ça :
VB:
Option Explicit

Private Sub Worksheet_Activate()
Dim Société As SsGroup, Déptmt As SsGroup, CCoût As SsGroup, Détail As Variant, Rés() As Variant, LRés As Long, Lr As Long, C As Long
Dim LDébTotDép As Long, LDébTotCC As Long, Zon As Range, Cel As Range
On Error GoTo Erreur
ReDim Rés(1 To 50000, 1 To [Base].Columns.Count)
For Each Société In GroupOrg([Base], 1, 2, 3)
   Lr = Lr + 1: Rés(Lr, 1) = "Société " & Société.Id
   For Each Déptmt In Société.Contenu
      Lr = Lr + 1: Rés(Lr, 2) = "Département " & Déptmt.Id
      LDébTotDép = Lr + 1
      For Each CCoût In Déptmt.Contenu
         Lr = Lr + 1: Rés(Lr, 3) = "Centre de coût " & CCoût.Id
         LDébTotCC = Lr + 1
         For Each Détail In CCoût.Contenu
            Lr = Lr + 1: For C = 4 To UBound(Rés, 2): Rés(Lr, C) = Détail(C): Next C
            Next Détail
         Lr = Lr + 1
         Rés(Lr, 12) = "Total centre de coût " & CCoût.Id
         Rés(Lr, 13) = "=" & LDébTotCC
         Next CCoût
      Lr = Lr + 1
      Rés(Lr, 12) = "Total département " & Déptmt.Id
      Rés(Lr, 13) = "=" & LDébTotDép
      Next Déptmt
   Next Société
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Me.Cells.Delete
Me.[A1].Resize(Lr, UBound(Rés, 2)).Value = Rés
For Each Zon In Me.Columns("M:M").SpecialCells(xlCellTypeFormulas, 1).Areas: For Each Cel In Zon
   Cel.Offset(, -1).HorizontalAlignment = xlRight
   With Cel.EntireRow.Borders(xlEdgeTop): .LineStyle = xlContinuous: .Weight = xlMedium: End With
   Lr = Cel.Value: Cel.Resize(, UBound(Rés, 2) - 13 + 1).FormulaR1C1 = "=SUBTOTAL(9,R" & Lr & "C:OFFSET(RC,-+1,0))"
   Next Cel, Zon
Exit Sub
Application.Calculation = xlCalculationAutomatic
Erreur: MsgBox Err.Description: Stop: Resume
End Sub
 

Spinzi

XLDnaute Impliqué
Re : Besoin aide VBA

Oui ma base est un copier coller (bientot une exctraction j'espere ... ^^) mais elle est tout a fait modifiable, tout du moins je peux mettre une ligne en plus avant de coller l'extraction.
J'avais une question de plus, concernant de fait les 3 colonnes avant ma base qui récupère les données de mon ancienne macro pour alimenter les listes de validation : sont elles encore d'actualité ? Sinon je pense qu'on peut les supprimer.

Le code fourni est a coller dans un nouveau module ?


ps : j'ai essayé de supprimer les 3 colonnes mais je crois que votre macro se base dessus. Dites moi si cela vous embarasse/rajoute du travail, c'est juste pour savoir si on peut supprimer ou non. Par contre pas de soucis apparent pour rajouter la ligne.

ps 2 : je n'avais pas ouvert la macro pour le rapport, je vous joins le fichier modifié avec votre nouveau code. J'ai dû supprimer une partie de la base pour tenir dans les conditions de taille du fichier.
 

Pièces jointes

  • RapportSpinziAmélioré.xls
    918 KB · Affichages: 27
Dernière édition:

Dranreb

XLDnaute Barbatruc
Re : Besoin aide VBA

La macro ne se base pas dessus mais la Worksheet_Deactivate de FBdB si !
Je crois qu'on va l'écrire comme ça dorénavant :
VB:
Private Sub Worksheet_Deactivate()
PlgUti([Base]).Name = "Base"
End Sub
Supprimez les 3colonnes et mettez manuellement comme référence initiale au nom Base ='Base en cours'!$A$3
Le code était bien à mettre dans FRapp.
Je vais mettre en place un système de sélection des colonnes souhaitées dans le rapport, utilisant la nouvelle ligne devant les titres.
Les colonnes constitutives de son organisation par société, département et centre de coût ne changeront jamais, n'est ce pas ! … ?? …
À +

Voilà où j'en suis. Pour arriver à le joindre moi, j'efface toute la feuille Rapport !
 

Pièces jointes

  • RapportSpinzi.xls
    925.5 KB · Affichages: 31
  • RapportSpinzi.xls
    925.5 KB · Affichages: 33
  • RapportSpinzi.xls
    925.5 KB · Affichages: 32
Dernière édition:

Spinzi

XLDnaute Impliqué
Re : Besoin aide VBA

Bonjour Dranreb,

Pour être plus précis, toute la partie en orange dans ma base est la référence (excepté le descriptif et encore) : ces colonnes sont le coeur de la base de données et c'est grace à elle qu'ont parvient à obtenir toutes les finromations que l'on veut.
Si je peux cependant me permettre des demandes : est ce que les totaux pourraient apparaitre en haut plutot qu'en bas ?

En tout cas merci pour votre travail, c'est vraiment hallucinant =)
 

Dranreb

XLDnaute Barbatruc
Re : Besoin aide VBA

Bonjour.
Oui, on peut les mette en face des titres de rubriques par exemple, et ne pas rajouter de ligne de totaux.
actuellement j'ajoute une ligne avec formule provisoire Rés(Lr, CrDébTot) = "=" & LigneDébutRubrique.
On peut au contraire faire Rés(LigneDébutRubrique, CrDébTot) = "=" & Lr
Lr représente le numéro de ligne courant de construction du résultat. À la fin il en est récupéré ce qui permet de construire la formule définitive "=SUBTOTAL(9,OFFSET(RC,1,0):R" & Lr & "C)"
Attention toutefois à ne pas ajouter manuellement de ligne derrière: elle ne serait pas englobée. À moins d'ajouter quand même une ligne vide en faisant quand même d'abord Lr = Lr + 1 dans la 1ère partie et à la fin:
VB:
Cel.Resize(, CrMax - CrDébTot + 1).FormulaR1C1 = "=SUBTOTAL(9,OFFSET(RC,1,0):OFFSET(R" & Lr & "C,-1,0))"
 

Spinzi

XLDnaute Impliqué
Re : Besoin aide VBA

Effectivement des lignes sont susceptibles d'être ajoutées à la main directement dans le rapport.
C'est à dire que dans un premeirtemps j'edite le rapport que je copie colle sur une autre feuille, que je retraite suivant quelques conditions, et j'envoie ça à la personne concernée. Cette personne à certaines colonnes à remplir mais est susceptible d'ajouter des lignes : (aprés à vous de me dire ce qui serait le plus simple) _ ajout a la fin d utableau en vrac et les formules de sous totaux en prennent quand même compte (dans la ligne sera renseigné la société, le département, enfin toutes les infos necessaires pour que le recoupement puisse se faire)
_ajout dans le centre de cout correspondant (si la personne veut ajouter une ligne dans le centre de cout adelphe, elle trouve ce centre de cout et ajoute la ligne à la fin => toujours laisser une ligne vide aprés chaque centre de cout pour les séparer et permettre cette insertion ?)
_créer un userform grace à un bouton "ajouter ligne", où la personne remplie la société, département, nom de centre de cout quelle peut choisir avec liste de validation, et une fois la selection terminée, l'incrémentation dans la bonne partie du rapport se fait.

Ce sont là des pistes, je ne prétends pas avoir la science infuse, mais la dernière piste me semble la plus réalisable et la plus simple en terme de prise en compte dans le total par la macro.
 

Dranreb

XLDnaute Barbatruc
Re : Besoin aide VBA

À moi aussi. Alors mettez la en œuvre. Allez ! Mettez un peu les mains dans le moteur pour un gain de pouvoir de maintenance !
Et si vous avez des question concernant son fonctionnement, n'oubliez pas de mettre mes réponses en commentaires derrières les instructions concernées.
 

Spinzi

XLDnaute Impliqué
Re : Besoin aide VBA

Bonjour

Ce fut un long week end ...
J'arrive à éditer un userfrom ... mais très pauvre ! En effet quand je selectionne l'objet liste de validation, ce dernier n'apparait pas comme une liste de validation ! P-e est ce parce que je n'ai pas rattaché de données a cette liste ...

De plus je n'ai pas reussis a modifier la formule pour faire apparaitre les totaux en haut ...
 

Pièces jointes

  • RapportSpinziV1.xls
    963.5 KB · Affichages: 30
  • RapportSpinziV1.xls
    963.5 KB · Affichages: 35
  • RapportSpinziV1.xls
    963.5 KB · Affichages: 44

Dranreb

XLDnaute Barbatruc
Re : Besoin aide VBA

je n'ai pas reussis a modifier la formule pour faire apparaitre les totaux en haut
Avez vous réellement essayé ?
VB:
…
      Cel.Offset(, -1).HorizontalAlignment = xlRight
      Dim RTit As String, RTot As String
      If Lr < Cel.Row Then RTit = Lr: RTot = "" _
                      Else RTit = "": RTot = Lr + 1
      Cel.Resize(, CrMax - CrDébTot + 1).FormulaR1C1 = _
         "=SUBTOTAL(9,OFFSET(R" & RTit & "C,1,0):OFFSET(R" & RTot & "C,-1,0))"
Et plus haut, évidemment :
VB:
'         Lr = Lr + 1
'         Rés(Lr, CrDébTot - 1) = "Total département " & Déptmt.Id
'         Rés(Lr, CrDébTot) = "=" & LDébTotDép
         Rés(LDébTotDép, CrDébTot) = "=" & Lr
Ensuite. Pourquoi un bouton dans la feuille rapport qui affiche un Userform ? La sélection d'une ligne pour je ne sais quel usage est un autre problème que le rapport, non ? En fait je croyais que ce problème de sélection tombait avec le rapport complet. Il serait aussi possible des faire une feuille rapport par société si vous voulez. Il suffirait de mettre le code ailleurs et d'y prévoir la création d'une nouvelle feuille juste après chaque For Each Société In GroupOrg([Base], 1, 2, 3)
 

Spinzi

XLDnaute Impliqué
Re : Besoin aide VBA

re Bonjour,

je comprends très bien que cela puisse agacer lorsque l'on travaille pour quelqu'un qui, on le pense, ne fait aucun effort.
Sans vouloir me dédouaner de toute responsabilité, j'ai trés clairement énoncé que je n'y connaissais RIEN en macro, ce pourquoi je venais ici poser des questions et essayer de répondre le plus clairement possible aux interrogations des personnes qui voulaient se pencher sur mon problème. De plus, mais ça vous le savez surement mieux que moi, reprendre la macro d'une autre personne c'est comme lire un livre dans une autre langue. Alors si on y ajoute en plus le fait qu'on ne sache pas lire la langue ... Juste un peu de tolérance svp.

Aprés ce bref passage, je vais essayer d'être clair une bonne fois pour toute sur ma demande car j'ai l'impression que je m'exprime mal.
Je souhaite remonter toutes les lignes correspondantes à plusieurs variables qui sont Société, Département et nom de centre de cout.
Comme toute règle il y a des exceptions : parfois je souhaiterai un éclatement par nom de centre de cout, parfois non, et j'aimerai avoir la possibilité de ce choix (d'où ma liste de validation ou quand je ne choisissai pas de centre de cout, il n'apparaissait pas).
Mais vous avez raison, avoir soit une feuille par département OU centre de cout; soit reprendre la structure de l'onglet rapport mais qu'il bouge suivant la liste de validation.

Je le répète encore une fois, mais si vous avez des questions n'hésitez pas. Je suis très patient et suis conscient que personne n'a la même perception des choses.

Bien à vous
 

Dranreb

XLDnaute Barbatruc
Re : Besoin aide VBA

D'accord. Le problème prend une toute autre dimension si je comprends bien.
Vous voulez toujours tout sortir, et il n'y a donc toujours plus de sélection dans certaines colonnes de valeurs spécifiques à prévoir.
Mais par contre vous voulez pouvoir retenir ou non aussi les critères de regroupement, voir en changer l'ordre de priorité de classement, et l'éclatement des plus globaux sur des feuilles séparées ? (ce qui n'empêchera pas ensuite de les bricoler à la main, en supprimer certaine lignes, d'en rajouter d'originales).
C'est bien cela ?
Intéressant.
C'est lors de la sélection d'une cellule de la ligne 1 de la Base en cours qu'il faudrait agir, et y préciser plus de choses qu'une simple croix ×. Naturellement un userform assitôt affiché se chargerait d'y établir un code de Statut de la colonne au moyen de cases à cocher et de boutons d'options clairs. Qu'est-ce qui devrait aussi pouvoir changer d'ordre des places horizontalement dans le rapport ? Mais d'abord, est-ce vers cette voie qu'il faut se diriger ?

(À part cela, ne déduisez d'une simple question un jugement exagéré de ma part)

P.S. Remarque: Ce dispositif pourrait s'accompagner d'un autre permettant de composer arbitrairement un rapport partiel (pour les cas tordus) par assemblage d'éléments choisis. Mettez des ComboBox à la place des TextBox mais n'essayez pas de le programmer: C'est un truc tout fait, un module de classe ComboBoxLiés dont j'équiperai votre classeur.

Ainsi ça pourrait aboutir à pouvoir avoir à la fois soit une feuille par département OU centre de cout ou ce que vous voudrez; soit un rapport construit de toute pièce qui bougerait suivant les choix de valeurs effectués dans les ComboBox d'un Userform.
 
Dernière édition:

Spinzi

XLDnaute Impliqué
Re : Besoin aide VBA

Bonjour Dranreb,

je souhaite une macro qui m'édite un rapport en fonction de valeurs contenues dans les listes de validation (Département, Nom de centre de cout et responsable). C'est un filtre en gros.
A la suite de ces choix dans la ou les listes, je voudrais que le rapport affiche tous les résultats correspondants.
Les colonnes indispensable, donc qui seront la base de mon rapport, sont les colonnes Société, Département, Nom de centre de cout, Code Budgétaire (ou Ordre, cela désigne le même élément), Libellé, Destination 1, Destination 2 (avec possibilité d'ajouter dans ce socle de base du rapport "Numéro de centre de cout" et "Code destination analytique").

Ensuite, je souhaiterai, soit par case a cocher dans la base, soit en inscrivant simplement l'intitulé de la colonne base que je désire dans mon rapport, que le rapport me remonte automatiquement les montants respectifs.

De plus, une fois que j'ai selectionné mes colonnes de la base qui remontent, je souhaiterai pouvoir ajouter (je pense via Userform mi manuel mi automatique) de nouvelles colonnes, à la suite car ce fichier sera ensuite rempli par d'autres personnes.
Ex : je souhaite remonter mes colonnes V1 car je suis en train de travailler sur un budget V2. Il faudrait que mes colonnes V1 remontent suivant mon choix dans la base, et qu'une macro créée exactement les mêmes mais pour une "V2" (Userform avec "Choisir version de budget" par exemple, a voir, pas le point principal pour l'instant je pense).

Ce rapport est ensuite transmis aux responsable de chaque département qui vont apporter des modifications à ce rapport (peut etre à ce moment là enlever les liens avec la base de données voir supprimer la macro dans le fichier édité). Ce modifications sont notamment l'ajout de lignes budgétaires. Cependant comme dans le rapport les lignes sont regroupées par centre de couts et par département, il faudrait un ajout de ligne par userform qui incrémente directement la ligne au bon endroit (bon centre de cout) et que de fait le total se recalcule pour prendre en compte cette nouvelle ligne.

_point d'attention 1 : j'aimerai, comme convenu avant, avoir un total par centre de cout, département, société en face de chaque regroupement.
_point d'attention 2 : j'aimerai que des qu'une valeur est selectionnée dans la liste de validation, cela affiche le résultat. En d'autres termes que la modification d'une valeur dans les listes de validation active la macro de rapport
_point d'attention 3 : j'aimerai que le rapport soit sur la même page que mes listes de validation et par liste de validation je n'entends pas combobox, mais bien les 3 listes qu ise situent en début de feuille "budget".

Voila c'est un peu militaire mais j'ai essayé d'exprimer au mieux mon besoin. J'aimerai une autre phase ensuite mais pour l'instant ce n'est pas la priorité.
Si vous souhaitez un fichier nu avec ce que j'attends, n'hésitez pas, ou toute autre information.

ps : j'ai aussi un peu de mal avec le langage technique que vous utilisez. Si jamais mes réponses ne répondent pas à vos questions, alors là aussi faites m'en part !

Merci à vous
 

Discussions similaires

Réponses
13
Affichages
261