Discussion :

[stef2807]

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.

[Pierre Fauconnier]

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.

[ARTURO83]

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
Pièce jointe 584365

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

[Pierre Fauconnier]

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.

[ARTURO83]

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.

[Pierre Fauconnier]

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...

[stef2807]

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

[stef2807]

Bonjour Pierre,

Ta solution serait plus stable si je comprend bien?

[Pierre Fauconnier]

Bonjour Stef...

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: GrandeDistribution.xlsm

[78chris]

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...

[stef2807]

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

[stef2807]

Lorsque je veux activer le VBA j'ai une erreur.

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

Mercvi

[Pierre Fauconnier]

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.