Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Macros et VBA Excel Discussion :

Tri scripting dictionnary


Sujet :

Macros et VBA Excel

  1. #21
    Nouveau membre du Club
    Bonjour,

    merci pour votre lien sur powerquery j'ai effectivement testé et effectivement pas très compliqué de créer les requêtes.
    je tente désormais de mettre en application la méthode VBA que je préfère.

    Aussi j'ai tenté d'examiner vos fichiers et les comprendre et tente de mettre en application la méthode dans mon fichier de test.
    Si je comprends bien, il s'agit de fusionner mes trois tableaux projects / investigators / budget dans un fichier temporaire pour ensuite les trier et en ressortir les infos requises.

    Aussi j'ai bien remis en forme les trois tableaux de mon fichier test et j'ai créer un feuille autre dans laquelle j'ai mis un tableau temporaire dont je ne connais pas la taille de base donc j'ai sélectionner un champ large... et encore une autre feuille destiné au tableau final que j'ai appelé Report


    je commence donc par le nettoyage du tableau temp
    le dénominateur commun à mes onglets est l'id projet pour information

    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    3
     
      ' Nettoyage du tableau temporaire
      If Not Range("T_Temp").ListObject.DataBodyRange Is Nothing Then Range("T_Temp").ListObject.DataBodyRange.Delete


    premier soucis avec cette ligne, j'ai une erreur d'éxécution 1004... ca commence bien


    ensuite et c'est là que je me pose des questions:
    me faut il ici déclarez mes 3 tableaux à fusionner ?

    je me base donc sur votre exemple et commence par le tableau projet

    ' Copie des taches dans le temporaire
    Set Target = Range("T_Temp").ListObject.ListRows.Add().Range(1)
    Target.Resize(Range("Projects").Rows.Count, 2).Value = Range("Projects").Value


    me faut il ici directement faire la même chose avec Investigators et Budget?
    Target.Resize(Range("Investigators").Rows.Count, 2).Value = Range("Projects").Value


    merci par avance pour vos éclarcissements

  2. #22
    Expert confirmé
    hello Pierre,
    Citation Envoyé par Pierre Fauconnier Voir le message

    Evidemment, tu la crées et la supprime après, mais ça induit du VBA là où ce n'est pas forcément nécessaire. De plus, si l'ordre de tri change, ça impose une modification du code VBA alors que la colonne d'ordre peut être modifiée par "n'importe quel utilisateur" sans connaissance de VBA. De plus, ça ne résout pas le problème des statuts à ne pas prendre en compte
    1 - Si l'ordre de tri change pas besoin de modifier le code VBA c'est la liste de tri personnalisée dans le classeur qui est utilisée.
    2 - Les status à ne pas prendre en compte sont dans la liste Filtre.

    Ami calmant, J.P
    Jurassic computer : Sinclair ZX81 - Zilog Z80A à 3,25 MHz - RAM 1 Ko - ROM 8 Ko

  3. #23
    Responsable
    Office & Excel

    Citation Envoyé par jurassic pork Voir le message
    [...]
    Je n'avais pas vu que tu utilisais une plage pour créer ta liste.

    Et tout cela
    • sans tableaux structurés alors qu'ils simplifient l'utilisation d'Excel et le codage en VBA;
    • en obligeant à utiliser une feuille TEMP alors qu'elle n'est pas nécessaire;
    • en utilisant du VBA (et donc de la fragilité) là où ça n'est pas nécessaire;
    • avec en plus plein de données en dur dans le code qui le rendent encore plus fragile et instable;
    • en utilisant cette syntaxe [M2];
    • ...



    Franchement, très peu pour moi, je reste sur l'idée de ma colonne de tri que tout utilisateur normal d'Excel va comprendre et va pouvoir modifier, qui permet en plus de se passer de VBA si on veut...


    Mais libre à chacun de faire ses choix, quitte à ce qu'ils soient mauvais. Pour moi, la programmation, c'est la rigueur, la réflexion, la recherche de la meilleure solution (je conçois que "meilleure" soit une notion très subjective), la recherche, surtout, de la fiabilité (donc le moins de code possible, le moins de "fantaisies" possible, coller au mieux à Excel: Quand on programme en VBA pour Excel, on pense Excel avant de penser VBA, l'écriture de code réutilisable et donc testé et testable, ...). Je ne vais pas me faire des amis en écrivant cela et chacun en pense ce qu'il veut, mais j'ai parfois beaucoup de mal à comprendre certaines solutions proposées et l'objectif poursuivi à les proposer.
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Mes remarques et critiques sont purement techniques. Ne les prenez jamais pour des attaques personnelles...
    Le VBA ne palliera jamais une mauvaise conception de classeur ou un manque de connaissances des outils natifs d'Excel...
    Ce ne sont pas des bonnes pratiques parce que ce sont les miennes, ce sont les miennes parce que ce sont des bonnes pratiques
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    Vous avez apprécié la réponse? =>
    ---------------

  4. #24
    Nouveau membre du Club
    Bonjour Jurassic Pork

    Merci pour votre proposition.
    je tente de la mettre en place mais ai le droit a une erreur 1004


    une idée du pourquoi svp?

  5. #25
    Responsable
    Office & Excel

    Darkangel,

    Chez moi, pas d'erreur avec ce code.


    Tiens, au passage... Comment vas-tu agir lorsque tu auras ajouté des lignes à ton tableau? Parce que le code que tu utilises, il va de la ligne 1 à la ligne 78... Tu vas aller modifier ton code chaque fois que tu vas ajouter ou supprimer des lignes?


    On pourrait rester sérieux ? Et, par exemple, utiliser les tableaux structurés et coder proprement?
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Mes remarques et critiques sont purement techniques. Ne les prenez jamais pour des attaques personnelles...
    Le VBA ne palliera jamais une mauvaise conception de classeur ou un manque de connaissances des outils natifs d'Excel...
    Ce ne sont pas des bonnes pratiques parce que ce sont les miennes, ce sont les miennes parce que ce sont des bonnes pratiques
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    Vous avez apprécié la réponse? =>
    ---------------

  6. #26
    Nouveau membre du Club
    bizarre car de mon coté j'ai toujours la même erreur

    je n'ai pas abandonné votre méthode du début mais rencontrais également des soucis mentionnés dans un post précédent ou je posais par ailleurs des questions

    je testais cette autre méthode pour voir comment elle fonctionne (ca peut toujours etre utile un jour )
    pour repondre à votre question cependant, non je ne me limiterais pas à la ligne 78 mais on peut indiquer jusqu'a la derniere ligne du tableau .

  7. #27
    Expert confirmé
    moi je ne suis pas un spécialiste Excel alors je proposais cela pour voir et pour explorer une autre possibilité
    D'ailleurs j'ai un drôle de phénomène quand j'exécute ma macro : Excel se ferme au bout de quelques minutes sans prévenir. Une idée sur la cause possible ? Un virus dans le fichier de DarkAngel ? Un bug avec les listes personnalisées ?

    Ami calmant, J.P
    Jurassic computer : Sinclair ZX81 - Zilog Z80A à 3,25 MHz - RAM 1 Ko - ROM 8 Ko

  8. #28
    Responsable
    Office & Excel

    Utilise la solution que tu préfères, c'est ton classeur et c'est toi qui te débrouillera avec

    Bien sûr, on peut déterminer la dernière ligne utilisée, encore faut-il savoir comment...


    Démonstration de l'utilité des tableaux structurés

    SANS tableaux structurés, recherche de la plage du tableau





    Arf... Pas bon à cause de l'absence de prénom sur la dernière ligne... En plus de la longueur de la ligne de code...

    On devrait plutôt utiliser CurrentRegion...



    Oui mais non, car si je déplace la plage pour mettre un titre au dessus... Aïe aïe aïe






    Si je déplace le tableau dans la feuille pour des raisons de présentation, pas bon non plus... Idem si je le déplace sur une autre feuille...





    Si je renomme la feuille... Ouille ouille ouille






    On le voit, on a pas mal de problèmes et d'instabilité...
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Mes remarques et critiques sont purement techniques. Ne les prenez jamais pour des attaques personnelles...
    Le VBA ne palliera jamais une mauvaise conception de classeur ou un manque de connaissances des outils natifs d'Excel...
    Ce ne sont pas des bonnes pratiques parce que ce sont les miennes, ce sont les miennes parce que ce sont des bonnes pratiques
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    Vous avez apprécié la réponse? =>
    ---------------

  9. #29
    Responsable
    Office & Excel

    Solution avec tableau structuré...






    Déplacement? Même code





    Renommage de la feuille? Déplacement sur une autre feuille? Même code



    Ajout de colonnes? Même code






    La seule restriction, c'est de ne pas renommer le tableau, mais comme dit, par d'autres ou par moi, dans d'autres discussions, normalement, le nommage des tableaux et des colonnes se réfléchit à la conception, et il y a peu de raisons de renommer un tableau t_Contacts en quoi que ce soit d'autre...


    J'espère que cette démo en deux messages permettra de bien comprendre l'utilité de travailler avec des tableaux structurés, et de penser Excel lorsque l'on développe en VBA pour Excel
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Mes remarques et critiques sont purement techniques. Ne les prenez jamais pour des attaques personnelles...
    Le VBA ne palliera jamais une mauvaise conception de classeur ou un manque de connaissances des outils natifs d'Excel...
    Ce ne sont pas des bonnes pratiques parce que ce sont les miennes, ce sont les miennes parce que ce sont des bonnes pratiques
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    Vous avez apprécié la réponse? =>
    ---------------

  10. #30
    Responsable
    Office & Excel

    Citation Envoyé par jurassic pork Voir le message
    moi je ne suis pas un spécialiste Excel alors je proposais cela pour voir et pour explorer une autre possibilité
    JP,

    Je n'ai rien contre toi, crois-le bien, et mes "attaques" ne sont pas personnelles (en plus, j'ai aussi commencé avec un ZX81 ). Je veux juste insister sur les bonnes pratiques et ce que je considère être la bonne conception d'un classeur et d'un code...

    Citation Envoyé par jurassic pork Voir le message

    D'ailleurs j'ai un drôle de phénomène quand j'exécute ma macro : Excel se ferme au bout de quelques minutes sans prévenir. Une idée sur la cause possible ? Un virus dans le fichier de DarkAngel ? Un bug avec les listes personnalisées ?

    Ami calmant, J.P
    N'ayant jamais eu, sauf cas d'école en formation, à programmer des listes personnalisées, je ne saurais pas te renseigner sur un bug éventuel lors de la création/suppression d'une liste perso en VBA
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Mes remarques et critiques sont purement techniques. Ne les prenez jamais pour des attaques personnelles...
    Le VBA ne palliera jamais une mauvaise conception de classeur ou un manque de connaissances des outils natifs d'Excel...
    Ce ne sont pas des bonnes pratiques parce que ce sont les miennes, ce sont les miennes parce que ce sont des bonnes pratiques
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    Vous avez apprécié la réponse? =>
    ---------------

  11. #31
    Nouveau membre du Club
    merci a vous pour cette demonstration en terme de comparaison.
    je soutien votre méthode avec les tableaux structurés et souhaiterais donc m'orienter dans cette voie.

    seulement j'ai tout de même les problèmes/ question soulevées dans mon post antérieur


    ps: je ne pense pas avoir de virus je tourne sur mon pc de boulot bien protégé
    quand je teste la méthode de jurassic sur ma base pour générer la page temporaire j'ai la meme erreur ..

  12. #32
    Nouveau membre du Club
    j'ai trouvé l'erreur pour tester la macro de jurassic:
    il faut mettre des parenthèses sinon de mon coté ça plante

    Application.AddCustomList (rngLstTri)

    toujours est il que le filtre se place sur la 2 ème ligne et pas la premiere ou les champs titres sont...

  13. #33
    Expert confirmé
    et moi j'ai trouvé pourquoi j'ai ce phénomène :
    Citation Envoyé par jurassic pork Voir le message
    Quand j'exécute ma macro : Excel se ferme au bout de quelques minutes sans prévenir
    C 'est bien un problème Excel voir ici
    Custom List Deletion Causing Excel to Crash When Save File
    En fait c'est quand arrive l'enregistrement automatique que j'ai un crash.
    La solution proposée :
    A coworker found the answer for me, so I thought I'd share in case anyone else is seeing this. After each sort I have to add the following:
    ActiveSheet.Sort.SortFields.Clear
    donc voilà ce que je fais maintenant :
    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    3
    4
        r.Sort key1:=[M2], order1:=1, ordercustom:=Application.CustomListCount + 1, _
               key2:=[G2], order2:=1
        Sheets("Temp").Sort.SortFields.Clear
        Application.DeleteCustomList Application.CustomListCount


    Ami calmant, J.P
    Jurassic computer : Sinclair ZX81 - Zilog Z80A à 3,25 MHz - RAM 1 Ko - ROM 8 Ko

  14. #34
    Responsable
    Office & Excel

    Super. Merci JP

    Cela dit, avec un tableau structuré (j'y tiens), on n'est pas obligé de passer par une liste perso, on peut passer par une chaine de caractères que l'on recompose au besoin, alors que sur un objet Range, on ne peut, je crois, utiliser qu'une liste personnelle.

    Sur base des tableaux ci-dessous:




    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    Sub Test()
      Dim SortList As String
      Dim i As Long
     
      For i = 1 To Range("t_Services").Rows.Count
        SortList = SortList & Range("t_Services[service]")(i) & ","
      Next i
      SortList = Left(SortList, Len(SortList) - 1)
     
      With Range("t_Contacts").ListObject.Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range("t_Contacts[Service]"), CustomOrder:=CStr(SortList)
        .Apply
      End With
    End Sub
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Mes remarques et critiques sont purement techniques. Ne les prenez jamais pour des attaques personnelles...
    Le VBA ne palliera jamais une mauvaise conception de classeur ou un manque de connaissances des outils natifs d'Excel...
    Ce ne sont pas des bonnes pratiques parce que ce sont les miennes, ce sont les miennes parce que ce sont des bonnes pratiques
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    Vous avez apprécié la réponse? =>
    ---------------

  15. #35
    Expert confirmé
    Citation Envoyé par Darkangel2014 Voir le message

    toujours est il que le filtre se place sur la 2 ème ligne et pas la premiere ou les champs titres sont...
    c'est parce que le range r commence en ligne 2.
    Avec cela :
    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
     
      Sheets("Temp").Range("A1:N78").AutoFilter Field:=13, Criteria1:="<>" & Sheets("Temp").Range("Q2")


    le filtre se place sur la première ligne
    Jurassic computer : Sinclair ZX81 - Zilog Z80A à 3,25 MHz - RAM 1 Ko - ROM 8 Ko

  16. #36
    Responsable
    Office & Excel

    Citation Envoyé par jurassic pork Voir le message
    c'est parce que le range r commence en ligne 2.[...]
    Perso, je précise le Header dans la commande:

    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    range("a1:b8").Sort range("a1:a8"),header:=xlYes


    Avec le tri personnalisé sur base d'une chaine et le fait que pour un listobject, on n'a pas besoin de préciser le header, ça commence à faire pas mal d'avantages pour le tableau structuré ( c'est juste un )
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Mes remarques et critiques sont purement techniques. Ne les prenez jamais pour des attaques personnelles...
    Le VBA ne palliera jamais une mauvaise conception de classeur ou un manque de connaissances des outils natifs d'Excel...
    Ce ne sont pas des bonnes pratiques parce que ce sont les miennes, ce sont les miennes parce que ce sont des bonnes pratiques
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    Vous avez apprécié la réponse? =>
    ---------------

  17. #37
    Nouveau membre du Club
    merci jurassic le fichier temp est désormais bien fonctionnel


    néanmoins j'attends désormais de voir par comparaison la méthode avec tableaux structurés mais attends l'aide de Pierre quant aux questions soulevées

  18. #38
    Responsable
    Office & Excel

    Citation Envoyé par Darkangel2014 Voir le message
    Si je comprends bien, il s'agit de fusionner mes trois tableaux projects / investigators / budget dans un fichier temporaire pour ensuite les trier et en ressortir les infos requises.
    Oui


    Citation Envoyé par Darkangel2014 Voir le message
    j'ai créer un feuille autre dans laquelle j'ai mis un tableau temporaire dont je ne connais pas la taille de base donc j'ai sélectionner un champ large
    Ce n'est pas nécessaire puisque le tableau est vidé avant traitement


    Citation Envoyé par Darkangel2014 Voir le message
    et encore une autre feuille destiné au tableau final que j'ai appelé Report
    Ok

    Citation Envoyé par Darkangel2014 Voir le message
    premier soucis avec cette ligne, j'ai une erreur d'éxécution 1004... ca commence bien
    Probablement une faute de frappe pour le nom du tableau


    Citation Envoyé par Darkangel2014 Voir le message
    me faut il ici déclarez mes 3 tableaux à fusionner ?
    Je ne sais pas ce que tu entends par "Déclarer"... Tu dois amener le premier tableau dans ton temp, puis écrire en VBA les formules qui permettent de récupérer les données des autres tableaux, comme je l'ai montré avec .Formula = "=Index..." (qui place un INDEX/EQUIV dans les colonnes en fonction des tableaux qui contiennent tes valeurs. Si les clés communes sont dans la première colonne, tu pourrais utiliser .Formula= "=VLOOKUP(...) (qui place un RECHERCHEV en VBA) si cette fonction t'est plus familière. Perso, je préfère INDEX/EQUIV car je ne suis ainsi pas lié à la position de mes colonnes (C'est toujours une recherche du code le plus stable, en fait).

    Après, lorsque les formules ont été ajoutées et que j'ai mon tableau complet avec toutes les colonnes liées, il reste à filtrer, trier et pousser dans le tableau de résultat pour les colonnes qui sont reprises dans le résultat.

    Question: Connais-tu RECHERCHEV ou INDEX/EQUIV en Excel?
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Mes remarques et critiques sont purement techniques. Ne les prenez jamais pour des attaques personnelles...
    Le VBA ne palliera jamais une mauvaise conception de classeur ou un manque de connaissances des outils natifs d'Excel...
    Ce ne sont pas des bonnes pratiques parce que ce sont les miennes, ce sont les miennes parce que ce sont des bonnes pratiques
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    Vous avez apprécié la réponse? =>
    ---------------

  19. #39
    Responsable
    Office & Excel

    Darkangel, tu sais remettre ton fichier?

    EDIT: Je les ai retrouvés, mais je persiste et signe, Power Query serait vachement plus simple...

    quelles sont les colonnes reprises dans le fichier DEST?
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Mes remarques et critiques sont purement techniques. Ne les prenez jamais pour des attaques personnelles...
    Le VBA ne palliera jamais une mauvaise conception de classeur ou un manque de connaissances des outils natifs d'Excel...
    Ce ne sont pas des bonnes pratiques parce que ce sont les miennes, ce sont les miennes parce que ce sont des bonnes pratiques
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    Vous avez apprécié la réponse? =>
    ---------------

  20. #40
    Nouveau membre du Club
    ok merci pour cette confirmation Pierre.

    Ce n'est pas nécessaire puisque le tableau est vidé avant traitement
    ok mais du coup je déclare ce tableau temp dans ma feuille de rapport directement?

    Probablement une faute de frappe pour le nom du tableau
    même pas je pense simplement pas savoir correctement le déclarer

    j'ai du reste toujours le bug (voir fichier joint)


    Je ne sais pas ce que tu entends par "Déclarer"... Tu dois amener le premier tableau dans ton temp, puis écrire en VBA les formules qui permettent de récupérer les données des autres tableaux, comme je l'ai montré avec
    ok je comprends mieux et ce n'est pas ce que j'avais compris à l'initial!!!


    donc en une fois le tableau projet dans le tableau temp j'ajoute les recherches directement via excel via la recherche ou index equiv que je connais directement dans le document temporaire pour ensuite générer le ficier rapport final apres avoir virer les colonnes qui ne me servent pas.

    voici le fichier ou j'en suis c'est à dire a temp.. donc toujours au début

###raw>template_hook.ano_emploi###