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:

Tirou

XLDnaute Occasionnel
Re : Besoin aide VBA

J'aurai plusieurs questions :

1) est-ce que le fichier que je t'ai proposé fonctionne chez toi avec ta base de donnée plus importante?

2) Si j'ai bien compris, ta macro fait (et ne fait que) dresser la liste sans doublon de 3 champs, histoire de les réutiliser dans de la validation de donnée ? Bon, après avoir relu tes posts, oui c'est bien ça ... bon, on oublie le VBA, et on va creuser du coté des formules.
Je reviens quand j'ai avancé

Bon, ça me dépasse en formules, à cause du fait qu'il y ait 3 champ de même niveau (au lieu d'un champ maitre et un champ esclave)
 
Dernière édition:

Spinzi

XLDnaute Impliqué
Re : Besoin aide VBA

1)En fait je vois que tu as modifié la macro pour les doublons. Elle fonctionne aussi (sauf pour la colonne responsable) mais si une macro peut directement alimenter les listes de validation en cascade sans passer par un stockage en feuille "base de données", cela m'arrange !

2) Oui elle ne fait que ça ... mais comme énoncé plus haut, si tu peux faire un fichier sans lister les sans doublon, ça m'importe peu en fait, je voudrais juste le plus optimisé à savoir le plus fiable et le plus rapide (et le ce qui est le plus facile à mettre en oeuvre pour l'instant). Donc si une macro/formule pouvait alimenter directement les listes de validation en cascade, ce serait le top. l'inconvénient des formules c'est que j'ai quelques 3000 lignes à traiter ...

Merci de votre aide !

edit : le format tableau associé à la base de donnée peut tout a fait être changé et il en est de même pour l'onglet budget.
 

Tirou

XLDnaute Occasionnel
Re : Besoin aide VBA

Ok, merci d'avoir confirmé qu'elle fonctionne. Mais est-ce que tu y a vu un gain de temps dans ton application?

Sinon, à tout hasard, je te propose de retirer le calcul automatique et de le forcer à la fin de ta macro :
Code:
Option Base 1

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.Calculation = xlCalculationManual
    
    If Target.Count > 1 Then Exit Sub
    tempAddress = Target.Address
    If tempAddress = "$B$1" Or tempAddress = "$C$1" Or tempAddress = "$D$1" Then
        Set dept = CreateObject("scripting.dictionary")
        Set centre = CreateObject("scripting.dictionary")
        Set resp = CreateObject("scripting.dictionary")
        
        With Sheets("Base en cours") 'Extraction données sources
            derLigne = .Range("E1048000").End(xlUp).Row
            derCol = .Range("AAA1").End(xlToLeft).Column
            tempTableau = .Range(.Cells(1, 1), .Cells(derLigne, derCol))
            End With
        tempTableau2 = Sheets("Budget").Range("B1:D1").Value
            
            
            For i = 2 To UBound(tempTableau, 1)
                If (tempTableau2(1, 1) = "" Or tempTableau(i, 5) = tempTableau2(1, 1)) And _
                    (tempTableau2(1, 2) = "" Or tempTableau(i, 6) = tempTableau2(1, 2)) And _
                    (tempTableau2(1, 3) = "" Or tempTableau(i, 7) = tempTableau2(1, 3)) Then
                    
                    dept(Trim(tempTableau(i, 5))) = 1: centre(Trim(tempTableau(i, 6))) = 1: resp(Trim(tempTableau(i, 7))) = 1
                    End If
                Next i
            
            tailleTableau = Application.Max(dept.Count, centre.Count, resp.Count)
            ReDim tempTableau3(tailleTableau, 3)
            ligne = 1
            For Each k In dept: tempTableau3(ligne, 1) = k: ligne = ligne + 1: Next k: ligne = 1
            For Each k In centre: tempTableau3(ligne, 2) = k: ligne = ligne + 1: Next k: ligne = 1
            For Each k In resp: tempTableau3(ligne, 3) = k: ligne = ligne + 1: Next k: ligne = 1
            
            With Sheets("Base en cours")
                .Range("A2").Resize(2 ^ 10, 3).ClearContents
                .Range("A2:C" & 1 + UBound(tempTableau3, 1)).Value = tempTableau3
                End With
            Application.Calculate
        End If
End Sub
 

Spinzi

XLDnaute Impliqué
Re : Besoin aide VBA

Oui par contre avant j'étais obligé d'attendre que la liste "cherche" les résultats ou qu'elle les vide. Ici il y a un gain de temps. par contre il y a un décalage pour la colonne sans doublon responsable, où les données commencent à apparaitre en C3 et non en C2 de la feuille base.

Je test ton nouveau code.

Super début

edit d'après reflexion : ok j'ai compris pourquoi il y avait un décalage, simplement parce qu'il y a des cellules vides dans responsable et que pour lui c'est une occurence. Mais c'est trés bien comme ça.
Par contre avec ton nouveau code c'est un peu plus long qu'avec le premier ...
 
Dernière édition:

Tirou

XLDnaute Occasionnel
Re : Besoin aide VBA

Mmm, je pense que la lenteur viens du Application.calculate. Après, je ne connais pas bien le fonctionnement automatique d'excel à ce niveau: est-ce qu'il repasse en calcul automatique à la fin des macros (et auquel cas, la ligne est inutile)? je n'en sais rien.

Pour t'éviter les cellules vides, je te propose de vérifier que les données sont bien "pleines" avant de les entrer dans le dico.

Code:
if Trim(tempTableau(i, 5)) <> "" then dept(Trim(tempTableau(i, 5))) = 1
if Trim(tempTableau(i, 6)) <> "" then centre(Trim(tempTableau(i, 6))) = 1
if Trim(tempTableau(i, 7)) <> "" then resp(Trim(tempTableau(i, 7))) = 1
 

Spinzi

XLDnaute Impliqué
Re : Besoin aide VBA

Bonjour Tirou,

pour les celulles vides, il ne doit pas y en avoir. Donc logiquement ce test ne servirait à rien ci ce n'est ralentir un tout petit peu plus la macro ?
En tout cas après une légère modification dans ton premier test (page 1) j'arrive à obtenir ce que je désire ! (je ne voulais pas la colonne 7 mais 15; mais c'est changé, c'est bon)
On va pouvoir passer à la seconde phase qui est la transformation de ma formule matricielle en macro ? =)
 

pedrag31

XLDnaute Occasionnel
Re : Besoin aide VBA

Bonjour Spinzi, bonjour le fil,

On va pouvoir passer à la seconde phase qui est la transformation de ma formule matricielle en macro ? =)

C'est ce qu'il me semblait comprendre dans tes premières explications, d'où ma question sur le résultat final que tu souhaitais obtenir pour faire la macro d'un coup (gestion doublons + rapport), incluant les optimisations de rapidité.

Ce fichier à pour but, grâce au choix de la liste de validation, de remonter tous les ordres suivant un choix de département et/ou de nom de centre de couts et/ou de responsable. Ensuite j'aimerai avoir une mise en page un peu particulière ...

Poste un exemple précis avec ce que tu veux comme résultat, c'est à dire un exemple de rapport des ordres que tu auras fait "à la main" qui montre ce qu'il faudra obtenir au final, et également les étapes que tu souhaites que l'utilisateur emprunte pour y arriver (exple 1. Sélection du département dans cellule A1 2. Sélection du responsable 3. Clic sur bouton "Rapport", etc...)

Bonne journée :)
 
Dernière édition:

Spinzi

XLDnaute Impliqué
Re : Besoin aide VBA

Bonjour à tous,

oui au final je me suis rendu compte avec toi que mes explications n'étaient pas claires, le étape par étape n'étant pas forcément la marche à suivre ...

Vous trouverez ci joint un fichier exemple (avec la macro de Tirou intégrée) et une espece de procédure ou de mode opératoire, qui compile le fichier comme je l'aimerais en version final !

Si vous avez une question, n'hesitez pas, je reste à votre entière disposition

edit :
Je pense que pour cacher les lignes/colonnes, ce code devrait être adaptable ?
Sub test()
Dim i&, R As Range
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
On Error Resume Next
For i = 3 To 50
If Cells(i, 9) + Cells(i, 14) + Cells(i, 18) = 0 Then
If Not R Is Nothing Then
Set R = Union(R, Rows(i))
Else
Set R = Rows(i)
End If
End If
Next i
If Not R Is Nothing Then R.EntireRow.Hidden = True
Application.Calculation = xlCalculationAutomatic
End Sub
 

Pièces jointes

  • TravauxHercule2.xlsm
    364.7 KB · Affichages: 60
  • TravauxHercule2.xlsm
    364.7 KB · Affichages: 71
  • TravauxHercule2.xlsm
    364.7 KB · Affichages: 70
Dernière édition:

Tirou

XLDnaute Occasionnel
Re : Besoin aide VBA

Salut Spinzi,

Alors, je viens d'essayé de me lancer dans l'adaptation macro de ta formule. Je me heurte à un soucis : je ne sais pas ce qu'il faut faire ...
1) Dans tes posts, je n’ai pas trouvé d'explication/indication sur ce que tes formules font.
2) les formules disponibles (uniquement dans ton fichier indicé 1) sont assez infâmes :
Code:
{=SIERREUR(INDEX(TableauBase[[#Données];[Code budgétaire]];PETITE.VALEUR(SI((EQUIV(TableauBase[[#Données];[Département]]&TableauBase[[#Données];[Code budgétaire]];TableauBase[[#Données];[Département]]&TableauBase[[#Données];[Code budgétaire]];0)=LIGNE(INDIRECT("1:"&LIGNES(TableauBase[[#Données];[Code budgétaire]]))))*(TableauBase[[#Données];[Département]]=Budget!$B$1)*(TableauBase[[#Données];[Nom Centre de coûts]]=Budget!$C$1);LIGNE(INDIRECT("1:"&LIGNES(TableauBase[[#Données];[Code budgétaire]]))));LIGNE(INDIRECT("1:"&LIGNES(TableauBase[[#Données];[Code budgétaire]])))));"")}
Code:
=SI(ET(D1="";B1<>"";C1<>"");Formule1;SI(ET(D1="";B1<>"";C1="");Formule2;SI(ET(D1<>"";B1="";C1<>"");Formule3;SI(ET(D1<>"";B1="";C1="");Formule4;SI(ET(D1="";B1="";C1<>"");Formule5;SI(ET(D1<>"";B1<>"";C1="");Formule6;SI(ET(D1="";B1="";C1="");"";Formule7)))))))
Et je ne sais même pas sur laquelle partir ....

3) J'avoue ne pas comprendre ton essai de macro (R c'est quoi? les colonnes sont-elles un exemple ou vraiment ce que tu cherches ? )


Explique nous, avec des mots, ce que tu attends de ton traitement automatique.
 

Spinzi

XLDnaute Impliqué
Re : Besoin aide VBA

Bonjour Tirou,

Ce que je cherche à faire, c'est afficher tous les ordres liés aux listes de validation. Quand dans la liste validation département je cherche "Lyon", je souhaiterais tous les ordres qui ont pour département "Lyon". Si les autres listes de valdiation sont vides, alors il ne cherche que sur ce critère.
Si dans la liste de validation département je choisis "Lyon" et dans la liste de valdiation nom de centre de coûts je choisis "Centre cout1", je recherche tous les ordres qui ont pour département "Lyon" et pour centre ce couts "Centre cout1".

Une fois que je pourrais visualiser ce résultat j'aimerai une mise en forme mais je pense qu'il faut déjà commencer par la formule.

La formule qui était présente en matricielle et en E3 servait seulement à choisir qu'elle référence appeler (Formule 1, Formule 2, etc.) suivant le remplissage des listes de validation.

Le bout de code que j'ai joins est un bout de code piqué sur le site qui permet de masquer des colonnes ou lignes je crois. C'était pour essayer d'avancer le projet de mo ncoté en trouvant des codes adaptables ... mais non je n'ai pas essayé de l'implémenter, c'est juste à titre informatif

Suis-je plus clair ?
N'hésite pas à me harceler =)

Et encore merci pour l'intérêt que tu portes à mon fichier
 

Tirou

XLDnaute Occasionnel
Re : Besoin aide VBA

Salut Spinzi,

Je te propose un début de code pour que tu puisses toi-même l'adapter et essayer de voir comment tu veux construire ton onglet final.

Je sais que je m’arrête alors que le travail n'est pas fini, mais reconstruire par macro un tableau croisé dynamique ne me motive pas vraiment.

Je reste à ta disposition pour t'aider à adapter.
 

Pièces jointes

  • TravauxHercule2.xlsm
    373 KB · Affichages: 48
  • TravauxHercule2.xlsm
    373 KB · Affichages: 54
  • TravauxHercule2.xlsm
    373 KB · Affichages: 45
Dernière édition:

Dranreb

XLDnaute Barbatruc
Re : Besoin aide VBA

Bonjour.

Je signale que tout cela est quand même assez facile à écrire à l'aide des modules de services de OutIdx, et en particulier des dictionnaires arborescents. Si les choix étaient à effectuer dans des ComboBox d'un userform, j'aurais depuis longtemps proposé ma solution immédiate standard. Pour des plages utilisées par des listes déroulantes de formulaire ou de validation j'ai commencé un nouveau module de classe pour les gérer. Seriez vous intéressé de participer à sa mise au point ?
 

Tirou

XLDnaute Occasionnel
Re : Besoin aide VBA

Perso, je suis intéressé d'avoir un bout de code exemple des différents outils que tu cites (outIdx, dictionnaire arborescent)
Maintenant que tu viens de m'éclairer sur l’existence de ces objets, j'ai de la biblio à faire ! :D
 

Dranreb

XLDnaute Barbatruc
Re : Besoin aide VBA

Il y a cette vieille discussion :
https://www.excel-downloads.com/thr...taire-modules-de-classe-et-indexation.187857/
Mais les fichiers ne sont plus à jour depuis longtemps, OutIdx a bien évolué. Et il évolue encore puisque j'y ajoute un module de classes ListesLiées. Mais je suis trop navré par le manque d'implication des autres XLDnautes pour avoir envie d'y reporter les mises à jour.
Mais il n'y aurait sans doute pas à chercher très longtemps dans mes discussion pour y trouver des modules plus récents. C'est bien la moitié de celles auxquelles je participe, du moins j'en ai l'impression.
 
Dernière édition:

Discussions similaires

Réponses
13
Affichages
261

Statistiques des forums

Discussions
311 732
Messages
2 081 995
Membres
101 857
dernier inscrit
mt60400