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

Excel Discussion :

Comment créer une liste dans une liste ?


Sujet :

Excel

  1. #1
    Futur Membre du Club
    Comment créer une liste dans une liste ?
    Bonjour,

    J'ai une liste d'article avec la formule RECHERCHEV qui me permet de retrouver l'article soit par le code EAN, soit par le code ID.

    Mais je voudrais aller plus loin.

    Sur cette liste qui contient plusieurs milliers d'articles, je dois en sélectionner une partie 2x par semaine pour la commande.

    Je souhaite qu'à chaque recherche, il constitue une nouvelle liste annexe que je pourrais traiter pour ma commande.

    Est-ce possible avec Excel?

    Merciiiiii

    Vous trouverez en annexe mon fichier simplifier.

  2. #2
    Responsable
    Office & Excel

    Salut.

    Je ne suis pas certain d'avoir compris ta question et ton besoin.

    Avec XL2016, on travaille avec des tableaux structurés (voir mon tuto à ce sujet).

    Avec XL 2016, on peut utiliser Power Query pour extraire des données d'un tableau, de sorte que si tu ajoutes une colonne "Sélection" à ta table de données, tu peux facilement récupérer une liste avec les lignes sélectionnées.

    Attention que comme tu as des valeurs numériques formatées en code ID, tu vas devoir transformer ces valeurs avec Power Query car PQ récupère la valeur brute de la cellule, et donc 14003482 et pas 0014003482. Etapes Power Query:
    • Ajouter le tableau à la solution Power Query;
    • Adapter les formats car PQ ne le fait pas correctement vu tes données (1ère colonne colonne en type text3ième colonne en Int64.Type (Attetnion, PQ est sensible à la casse);
    • Ajouter la colonne formulée pour récupérer le bon format des Codes ID;
    • Supprimer la colonne initiale des Codes ID;
    • Remettre la nouvelle colonne à la bonne place;
    • Renommer la colonne;
    • Filtrer sur le O dans la colonne Sélection;
    • Supprimer la colonne de sélection.



    Il suffit alors de renvoyer la requête dans 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...
    ---------------

  3. #3
    Expert confirmé
    Bonjour,

    Autre solution en vba, comme ça vous aurez le choix.

    Ajout d'un bouton pour constituer la liste des articles et l'application à une liste déroulante (validation de données) pour sélectionner par la suite les articles à commander.
    A partir de la liste ainsi créée, sélectionnez l'article de votre choix et cliquez sur le bouton valider pour constituer la liste des commandes.
    J'ai mis en remarque dans le code la possibilité d'indiquer la quantité à commander, peut-être que cela peut vous intéresser.

    Le fichier


    Le code
    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
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    Dim DerLig_f1_f1 As Long, DerLig_f2 As Long
    Dim f1 As Worksheet, f2 As Worksheet
     
    Sub Listes()
        Application.ScreenUpdating = False
        Set f1 = Sheets("Listes")
        DerLig_f1 = f1.Range("A" & Rows.Count).End(xlUp).Row
     
        'Suppression des zones de noms existantes
        ActiveWorkbook.Names("Code_EAN").Delete
        ActiveWorkbook.Names("Code_ID").Delete
        ActiveWorkbook.Names("Articles").Delete
     
        'Création des nouvelles zones de noms
        ActiveWorkbook.Names.Add Name:="Code_EAN", RefersToR1C1:="=Listes!R2C1:R" & DerLig_f1 & "C1"
        ActiveWorkbook.Names.Add Name:="Code_ID", RefersToR1C1:="=Listes!R2C3:R" & DerLig_f1 & "C3"
        ActiveWorkbook.Names.Add Name:="Articles", RefersToR1C1:="=Listes!R2C2:R" & DerLig_f1 & "C2"
     
        'Création de la liste déroulante(validation de données)
        With f1.Range("F22:G22").Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=Articles"
            .IgnoreBlank = True
            .InCellDropdown = True
            .ShowInput = True
            .ShowError = False
        End With
     
        Set f1 = Nothing
    End Sub
     
    Sub Commandes()
        Application.ScreenUpdating = False
        Set f1 = Sheets("Listes")
        Set f2 = Sheets("Commandes")
        DerLig_f2 = f2.Range("A" & Rows.Count).End(xlUp).Row + 1
        'Qte = InputBox("Indiquez la quantité à commander", "Commande articles")
        Article = f1.Range("F22")
        Code_EAN = f1.Range("G23")
        Code_ID = f1.Range("G24")
        'Position = f1.Range("G25")
     
        f2.Range(f2.Cells(DerLig_f2, "A"), f2.Cells(DerLig_f2, "C")) = Array(Code_EAN, Article, Code_ID)
        Set f1 = Nothing
        Set f2 = Nothing
    End Sub



    Cdlt

  4. #4
    Responsable
    Office & Excel

    Salut Arturo,

    Je ne comprends pas pourquoi tu ne travailles pas avec des tableaux structurés. Et je ne comprends pas non plus pourquoi tu supprimes les plages nommées pour les recréer à chaque coup. Si, ailleurs dans l'appli que là où tu crées les listes de validation, on utilise ces plages, on va se prendre des #REF!...

    Il faut établir les listes une fois pour toutes, hors VBA, à la conception du classeur (que l'on utilise ou pas les tableaux structurés) et les utiliser là où l'on en a besoin.
    "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...
    ---------------

  5. #5
    Expert confirmé
    Bonjour Pierre,

    Je ne comprends pas pourquoi tu ne travailles pas avec des tableaux structurés
    Ce n'est pas que je ne veuille pas travailler avec des tableaux structurés, c'était pour s'adapter au fichier existant. mais structuré ou pas, l'utilisateur veut sélectionner un produit avec ses codes et le reporter dans une autre feuille pour faire ses commandes.

    je ne comprends pas non plus pourquoi tu supprimes les plages nommées pour les recréer à chaque coup
    Cette macro ne sert qu'à recréer les nouvelles listes au cas où des éléments du tableau seraient ajoutés, modifiés ou supprimés, car je suppose que la liste des articles sera perpétuellement en mouvement, donc il faudra bien rafraîchir la validation de données. Si le tableau des articles n'a pas subit de modifications, il n'y a pas lieu de lancer cette macro.

    Si, ailleurs dans l'appli que là où tu crées les listes de validation, on utilise ces plages, on va se prendre des #REF!...
    Entièrement d'accord, mais ce n'est qu'une proposition établie en fonction des données dont on dispose, et c'est à l'utilisateur de l'adapter selon ses besoins.

  6. #6
    Responsable
    Office & Excel

    Je pense qu'il est opportun, avec les nouvelles versions d'Excel de systématiquement proposer les tableaux structurés lorsque l'on voit que le demandeur ne les utilise pas. Enfin, lorsque je dis "nouvelles versions", je dis n'importe quoi, car ça fait quand même 13 ans que les tableaux structurés ou tables de données ou tableaux dynamiques existent, et ils serait vachement temps de les utiliser. J'explique dans mon tuto les nombreux avantages à travailler avec les tableaux structurés.

    Je rappelle que les tableaux structurés permettent en VBA de se détacher totalement de l'emplacement et de l'organisation du tableau sur une feuille. La seule chose qui ne peut être modifiée, c'est le nommage, tant du tableau que des colonnes. Mais normalement, le choix des noms fait partie de la réflexion à mener à la conception du tableau et les noms ne devraient plus être modifiés après, sauf pour des raisons exceptionnelles et donc très rares.

    Avec ou pas un tableau structuré, la création des plages nommées fait partie de la conception du classeur et, sauf cas rares et exceptionnels, ne devraient pas être créés en VBA et peuvent être créés de manière à évoluer avec l'ajout et la suppression de données dans les tableaux.

    Sur base d'un tableau structuré, créer une plage nommée dynamique se réalise en deux clics et un ENTER:
    • On sélectionne la colonne de données du tableau (activer une cellule de la colonne puis CTRL+ESPACE ou cliquer sur le dessus de l'entête de la colonne (petite flèche noire));
    • Aller dans la zone de noms et y saisir le nom souhaité et ne pas oublier ENTER.



    C'est tout! La plage est dynamique et va s'adapter à l'ajout et à la suppression des lignes dans le tableau. Si le tableau est déplacé, la plage nommée suit le déplacement, même sur une autre feuille du classeur.

    La colonne du tableau sélectionnée et le nom saisi dans la zone des noms (le nom ne reste pas affiché par la suite mais est valide et utilisable dans tout le classeur)


    Placement de la liste de validation sur une cellule (comme dit dans le tuto, si l'on place la liste de validation sur la colonne d'un tableau structuré, les lignes ajoutées contiennent la liste de validation)


    La liste avec les données de départ


    Le tableau et la liste après modifs dans le tableau (suppression de Jean Aymar et ajout de nouveaux contacts)



    Ca prend 2 minutes lors de la conception. Nul besoin de VBA pour réaliser cela. En Excel, renommer le tableau ou ses colonnes ne perturbe pas le fonctionnement de la liste de validation.



    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser 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...
    ---------------

  7. #7
    Futur Membre du Club
    Bonjour Pierre et Arturo, merci pour votre retour c'est super constructif.

    L'idéal pour moi, serait d'utiliser cette liste uniquement à ma commande.

    J'utilise donc un gun pour flasher soit le code EAN soit le code ID.

    Sur base de ce qu'à proposer Arturo, ce serait génial qu'à chaque fois que je flash un code EAN ou un code ID il constitue automatiquement cette liste de choix de ma commande?

    Est-ce possible?

    Merci

  8. #8
    Futur Membre du Club
    Bonjour Pierre,

    Ta solution serait plus stable si je comprend bien?

  9. #9
    Responsable
    Office & Excel

    Bonjour Stef...

    Citation Envoyé par stef2807 Voir le message
    Bonjour Pierre,

    Ta solution serait plus stable si je comprend bien?
    Oui



    Je ne connais pas ton niveau en VBA... Mais pour que la saisie d'une donnée puisse modifier la valeur d'une cellule, il faudra forcément du VBA

    Voici le code VBA qui va permettre de valoriser la cellule correspondante à un produit flashé dans la cellule de saisie. J'ai nommé la cellule H1 "SaisieGun" (j'ai supposé que ton gun valide mais sinon, il faudra faire le ENTER à la main).

    Lors de la modification de la valeur de cette cellule de saisie, on lève l'évènement Change de la feuille et on appelle la procédure qui fait le boulot (rappel, un évènement ne fait rien d'applicatif).

    Le code VBA détermine si tu as flashé un code EAN, sinon il considère que tu as flashé l'ID. Il recherche la position de la donnée dans la colonne correspondante du tableau. S'il trouve la valeur, il sélectionne la ligne en plaçant un "O" en regard de l'article sélectionné puis vide la saisie, sinon, la fonction renvoie 1.

    On remarque que nulle part dans le code on n'utilise la position du tableau et nulle part on ne doit déterminer la dernière ligne etc, etc. C'est le passé, tout cela, grâce aux tableaux structurés...

    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
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    Private Sub Worksheet_Change(ByVal Target As Range)
      On Error GoTo Catch
     
      If Target.Address = Range("SaisieGun").Address Then
        Application.EnableEvents = False
        If SelectRow(Target) = 1 Then MsgBox "Article inexistant"
      End If
     
    Catch:
      Application.EnableEvents = True
    End Sub
     
    Function SelectRow(Target As Range) As Long
      Dim Pos
      If Len(Target.Value) = 13 Then
        Pos = Application.Match(Target.Value, Range("t_Articles[Code ean]"), 0)
      Else
        Pos = Application.Match(Target.Value, Range("t_Articles[Code ID]"), 0)
      End If
      If Not IsError(Pos) Then
        Range("t_Articles[Selection]")(Pos).Value = "O"
        Target.ClearContents
      Else
        SelectRow = 1
      End If
    End Function


    J'ai ajouté une petite procédure à lancer sur clic de bouton pour initialiser la colonne de sélection. Là aussi, aucune notion d'emplacement, juste les références structurées.

    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    3
    Sub ClearSelection()
      Range("t_Articles[Selection]").ClearContents
    End Sub


    Le fichier d'exemple:
    "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...
    ---------------

  10. #10
    Expert éminent sénior
    Bonjour à tous

    Je partage l'avis de Pierre

    Les listes de données sont apparues dans 2003 soit 17 ans et l'essai a été transformé dans 2007, donc il est effectivement temps d'évoluer

    Si tu scannes un seul code barre avant de déclencher la recherche une cellule nommée suffit pour PowerQuery et une ligne de VBA peut déclencher l'actualisation de la requête si le clic sur un bouton d'Excel te gêne

    Si tu dois constituer une liste de plusieurs codes il faudra un peu de code VBA pour automatiser la RAZ du tableau de choix et un bouton pour déclencher une fois fini

    Donc précise les choses

    Edit : Pierre a répondu en même temps...
    Chris

    Quand un homme a faim, mieux vaut lui apprendre à pêcher que de lui donner un poisson.
    Confucius

    ----------------------------------------------------------------------------------------------
    En cas de résolution, n'hésitez pas cliquer sur c'est toujours apprécié...

  11. #11
    Futur Membre du Club
    Merci Pierre et merci Chris pour ta confirmation mais mon niveau VBA est très faible.

    Pour le premier code je dois créer un nouveau VBA?

    Merci

  12. #12
    Futur Membre du Club
    Lorsque je veux activer le VBA j'ai une erreur.

    L'image est en annexe si vous voulez bien regarder.

    Mercvi

  13. #13
    Responsable
    Office & Excel

    Ce que tu montres ne fait pas partie de mon code. Il faudrait donner le message d'erreur histoire que l'on puisse comprendre mieux le problème.

    Es-tu certain d'avoir une feuille nommée "Listes"? As-tu déclaré une ligne Option Explicit tout au début du module?

    Note que le but du forum n'est pas de te développer l'outil à coup de questions/réponses et d'essais/erreurs. Cela ne pourra en gros que produire une usine à gaz, surtout si tu prends des bouts de code à l'un ou l'autre. N'oublie pas que nous n'avons aucune vue sur l'entièreté de ton problème, de ton projet, ni probablement de ce dont tu disposes à la base. J'ai cru comprendre que tu souhaitais réaliser une commande en flashant des articles au lecteur de code-barres, et c'est un bon début. Le classeur que je t'ai fourni permet de réaliser ce début. A toi de voir et de dire ce que tu souhaites réaliser par la suite.

    Un bon début, c'est d'établir un cahier des charges, même sommaire, de ce que tu as et de vers quoi tu veux aller. Ca permettra de jeter les bases de la conception de ton classeur. Au stade actuel, il me semble largement prématuré de commencer à coder. Si par la suite la conception du classeur devait être revue, il y a fort à parier que le travail de codage déjà réalisé devrait être soit revu soit oublié, entraînant des pertes de temps pour toi et pour ceux qui t'assistent dans cette démarche sur le forum.
    "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...
    ---------------

###raw>template_hook.ano_emploi###