Discussion :

[Histriate]

Bonjour,

Je cherche à créer des outils facilitant pour notre préparatoire dans la pharmacie où je travaille.

Feuille "Formulaire PO" : Je souhaite faire apparaître la composition d'une préparation lorsqu'on sélectionne la préparation dans une liste (valeur en F7). J'ai un tableau avec les numéro d'ingrédient en colonne C (1 à 15), sachant que le nombre d'ingrédient est variable selon la préparation.
Feuille "Liste PO" : j'ai mis les compositions des préparations avec une clé d'identification sous forme "Nom de la prep_n" etc. pour chaque ingrédient (".._1" pour l'ingrédient numéro 1, ".._2" pour l'ingrédient numéro 2 etc)

J'arrive à le faire pour la première ligne mais je voudrais faire en sorte simplement que la totalité des lignes de ma composition apparaissent dans mon tableau (sans avoir à copier le code pour les 15 lignes potentielles).
Je ne sais pas si je suis très claire...

Voici le code que j'utilise pour le moment : (j"ai fait des essais de variable avec "Nb" mais je ne suis pas allée au bout)

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
 
Sub Enregistrement_PO()
 
ligne = Sheets("Liste PO").Range("B2").End(xlDown).Row
Dim Liste_PO As Range 'plage de recherche
    Set Liste_PO = Sheets("Liste PO").Range("A2:H" & ligne)
Dim Compo_PO As Range 'plage dans laquelle inscrire le résultat
    Set Compo_PO = Sheets("Formulaire PO").Range("E51:L65")
Dim Denomin_PO As Variant 'valeur recherchée
    Denomin_PO = Sheets("Formulaire PO").Range("F7").Value
Dim Compo As Range 'résultat de la recherche
 
Dim Nb As Integer 'définit la ligne concernée à partir de E51
    Nb = Range("C51").Value
Set Compo = Liste_PO.Find(what:=Denomin_PO & "_" & Sheets("Formulaire PO").Range("C51"), LookIn:=xlValues, LookAt:=xlWhole)
If Not Compo Is Nothing Then
    Sheets("Formulaire PO").Range("E51").Value = Compo.Offset(ColumnOffset:=3).Value
    Sheets("Formulaire PO").Range("F51").Value = Compo.Offset(ColumnOffset:=4).Value
    Sheets("Formulaire PO").Range("H51").Value = Compo.Offset(ColumnOffset:=5).Value
    Sheets("Formulaire PO").Range("J51").Value = Compo.Offset(ColumnOffset:=6).Value
    Sheets("Formulaire PO").Range("K51").Value = Compo.Offset(ColumnOffset:=7).Value
    Sheets("Formulaire PO").Range("L51").Value = Compo.Offset(ColumnOffset:=8).Value
 
Else
        Sheets("Formulaire PO").Range("E51").Value = "Erreur"
End If
 
End Sub

Merci pour votre aide je suis en train de m'arracher les cheveux et les yeux

[Invité]

Bonjour,

Pourquoi rechercher l'indice dans Liste_Po ? Pourquoi tester la dernière ligne sur la colonne B alors que les valeurs sont contenues dans la colonne A ?
J'utiliserais plutôt une boucle For Next plutôt qu'une recherche avec Find. Possible qu'il y ait des erreurs car pas testé sur votre exemple :

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
 
 
Sub Enregistrement_PO_V2()
 
Dim Liste_PO As Range 'plage de recherche
Dim Denomin_PO As String 'valeur recherchée
Dim I As Long, DerniereLigne As Long, LigneEnCours As Long
Dim ShFormulaire As Worksheet
 
    With Sheets("Liste PO")
         DerniereLigne = .Range("B2").End(xlUp).Row
         Set Liste_PO = .Range("A2:A" & DerniereLigne)
    End With
 
    Set ShFormulaire = Sheets("Formulaire PO")
    With ShFormulaire
        Denomin_PO = .Range("F7").Value
        LigneEnCours = 51
    End With
 
    For I = 1 To Liste_PO.Count
        If InStr(1, Liste_PO(I).Value, Denomin_PO, vbTextCompare) > 0 Then
           With ShFormulaire
                .Cells(LigneEnCours, "E") = Liste_PO(I).Offset(0, 2)
                .Cells(LigneEnCours, "F") = Liste_PO(I).Offset(0, 3)
                .Cells(LigneEnCours, "H") = Liste_PO(I).Offset(0, 4)
                .Cells(LigneEnCours, "J") = Liste_PO(I).Offset(0, 5)
                .Cells(LigneEnCours, "K") = Liste_PO(I).Offset(0, 6)
                .Cells(LigneEnCours, "L") = Liste_PO(I).Offset(0, 7)
                LigneEnCours = LigneEnCours + 1
           End With
        End If
    Next I
 
    Set Liste_PO = Nothing
    Set ShFormulaire = Nothing
 
End Sub

[Philippe Tulliez]

Bonjour,
Si j'ai bien compris le besoin.
A savoir la sélection d'un numéro de préparation devant faire apparaître une liste d'ingrédients correspondant à ce numéro de préparation, une simple ligne de code VBA devrait suffire en utilisant la méthode AdvancedFilter (Filtre avancé) de l'objet Range

Il suffit de sélectionner le nom de la préparation dans la liste déroulante (cellule B2 de la feuille nommée Formulaire pour voir la liste des ingrédients correspondante)

Organisation du classeur (trois feuilles)

1. Préparation : liste des ingrédients un tableau structuré nommé T_Mixture. la valeur de la propriété CodeName de cette feuille est shtMixture
2. Formulaire : pour la consultation. Elle contient deux plages nommées areaCriteria (B1:B2) et areaTarget (C6:E6) nécessaire pour l'utilisation du filtre avancé.
3. Parameter : contient la table des préparations

Code VBA
Code placé dans le module de la feuille de consultation

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
Private Sub Worksheet_Change(ByVal Target As Range)
  shtMixture.ListObjects(1).Range.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("areaCriteria"), CopyToRange:=Range("areaTarget")
End Sub

Illustration


A lire éventuellement Les filtres avancés ou élaborés dans Excel

[Histriate]

Bonjour,

Pourquoi rechercher l'indice dans Liste_Po ? Pourquoi tester la dernière ligne sur la colonne B alors que les valeurs sont contenues dans la colonne A ?

Bonjour Eric,
Je ne suis pas du tout une pro de VBA, je découvre et tâtonne depuis un petit mois donc il y a peut-être des choses incohérentes dans mon langage... J'ai préféré tester la dernière ligne sur la colonne B parce que c'est elle qui sera modifiée en premier si on ajoute une préparation à l'avenir (la première colonne étant la clé générée à partir de la colonne B) mais ça ne change pas grand chose il me semble...
Quand à la recherche d'indice c'est parce que je m'étais inspirée de commandes type recherchev sur plusieurs occurrence comme j'avais déjà fait sur d'autres feuilles.
Sinon, votre code ne fonctionne pas, ça m'a inséré des éléments mais pas dans les bonnes colonnes et j'ai voulu le rectifier puis plus rien ne se passe...


Philippe, votre proposition a l'air simple et pratique, ça me plait ! D'autant plus que j'aurai besoin de ce genre de chose pour créer des formulaires de consultation des préparations archivées.
Par contre je ne sais pas pourquoi elle ne veut pas se lire, si j'essaie de l'exécuter ça me demande de créer une macro, il y a certainement quelque chose que j'ai mal enregistré...
Une petite question pour ma compréhension de AdvancedFilter (j'ai lu votre tuto), est-ce que la table Parameter est indispensable si mon code préparation est mon libellé de préparation (c'est avec le libellé que les préparatrices choisissent la prep) ?
J'ai bien compris qu'il fallait que les libellés soient dans les plages et bien identiques dans les deux plages (1ere ligne de T_mixture et areaTarget), par contre je ne suis pas sûre d'avoir correctement défini mon tableau T_mixture (qui pour moi s'appelle Liste_PO).
Voilà ce que j'ai fait à partir de votre 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
 
Private Sub Worksheet_Change(ByVal Target As Range)
 
    ligne = Sheets("Liste PO").Range("B2").End(xlDown).Row
    Dim Liste_PO As Range 'plage de recherche
        Set Liste_PO = Sheets("Liste PO").Range("C1:H" & ligne) 'les deux premières colonnes sont d'autres valeurs
    Dim AreaCriteria As Range
        Set AreaCriteria = Sheets("Formulaire PO").Range("E6:E7")
    Dim Compo_PO As Range 'plage de la première ligne à partir de laquelle copier le résultat
        Set Compo_PO = Sheets("Formulaire PO").Range("D50:H50")
 
    Liste_PO.ListObjects(1).Range.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:="AreaCriteria", CopyToRange:="Compo_PO"
 
End Sub

Merci pour vos éclairages à tous les deux, et désolée si certaines de mes questions vous paraissent bêtes.

[Invité]

Mettez en ligne un exemple de votre fichier sans données confidentielles, si vous souhaitez qu'on y jette un oeil. Si votre fichier est .xlsm, zipez-le.

[Philippe Tulliez]

Bonjour,
Non, la feuille nommée Parameter n'est pas indispensable. Elle contient tout simplement une table qui devait permettre de référencer sa première colonne pour l'utiliser comme liste déroulante dans la première colonne de la table présente dans la feuille nommée Préparation mais j'ai omis de l'intégrer dans le classeur que j'ai joint comme exemple.

Votre code ne fonctionne pas car vous utiliser comme source un objet Range et moi un tableau structuré (ListObject) comme je l'ai précisé dans la ligne numérotée 1 dans le chapitre Organisation du classeur

Il est bien plus pratique et nettement plus avantageux d'utiliser une plage convertie en tableau.

A lire éventuellement Apprendre à utiliser les tableaux structurés Excel : création, manipulations et avantages(1)

[Histriate]

Mettez en ligne un exemple de votre fichier sans données confidentielles, si vous souhaitez qu'on y jette un oeil. Si votre fichier est .xlsm, zipez-le.

Voilà mon fichier simplifié et nettoyé en pj.
Le code d'Eric fonctionne uniquement sur la première ligne de ma préparation comme ce que j'avais déjà (même si le mien était moins bien écrit ).
J'ai essayé de modifier ma liste en tableau structuré, je ne suis pas certaine de l'avoir correctement nommé en vba...
Votre private sub n'apparaît même pas dans mes macros, est-ce normal ?

Merci !

[Philippe Tulliez]

Bonjour,

Votre private sub n'apparaît même pas dans mes macros, est-ce normal ?

C'est tout à fait normal. Elle est privée.
De plus il s'agit d'une procédure événementielle à placer dans le module de la feuille où doit avoir lieu la consultation, comme je l'ai d'ailleurs précisé au chapitre Code VBA

Avez-vous au moins téléchargé le fichier exemple que j'ai déposé ?
Manifestement pas

[Invité]

Attention, les valeurs Type Prepa dans le tableau structuré ne correspondent pas avec la valeur C7 du formulaire (un blanc à la fin).

Pour info : Dans votre fichier, la procédure événementielle proposée par Philippe que je salue ne peut pas être placée dans le module standard mais au même endroit que mon Private Sub Worksheet_Change(ByVal Target As Range) mentionnée ci-dessous.

La solution de Philippe est plus efficace car elle nécessite moins de code.

Dans le module standard :

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
 
Sub Composition_PO()
 
    Dim Liste_PO As Range 'plage de recherche
    Dim TypePrepa As String, Denomin_PO As String 'valeur recherchée
    Dim I As Long, LigneEnCours As Long, ColTypePrepa As Long, ColDenomination As Long
    Dim ShFormulaire As Worksheet
 
    With Sheets("Liste PO").ListObjects("t_listePO")
         Set Liste_PO = .ListColumns("Type prep").DataBodyRange
         ColTypePrepa = .ListColumns("Type prep").Range.Column
         ColDenomination = .ListColumns("Denomination PO").Range.Column
    End With
 
 
    Set ShFormulaire = Sheets("Formulaire PO")
    With ShFormulaire
         TypePrepa = .Range("C7").Value
         Denomin_PO = .Range("E7").Value
         LigneEnCours = 51
         .Range("D51:I65").ClearContents
    End With
 
    For I = 1 To Liste_PO.Count
        If Liste_PO(I).Value = TypePrepa And Liste_PO(I).Offset(0, ColDenomination - ColTypePrepa).Value = Denomin_PO Then
           With ShFormulaire
                .Cells(LigneEnCours, 4) = Liste_PO(I).Offset(0, 2)
                .Cells(LigneEnCours, 5) = Liste_PO(I).Offset(0, 3)
                .Cells(LigneEnCours, 6) = Liste_PO(I).Offset(0, 4)
                .Cells(LigneEnCours, 7) = Liste_PO(I).Offset(0, 5)
                .Cells(LigneEnCours, 8) = Liste_PO(I).Offset(0, 5)
                LigneEnCours = LigneEnCours + 1
           End With
        End If
    Next I
 
    Set Liste_PO = Nothing
    Set ShFormulaire = Nothing
 
End Sub

Dans le module de la feuille Formulaire_PO

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
 
 
Private Sub Worksheet_Change(ByVal Target As Range)
 
     If Target.Count > 1 Then Exit Sub
 
     If Not Intersect(Target, Range("C7")) Is Nothing Then
        Range("D51:I65").ClearContents
        Range("E7").ClearContents
        Exit Sub
     End If
 
     If Not Intersect(Target, Range("E7")) Is Nothing Then
        Composition_PO
        Exit Sub
     End If
 
End Sub

[Histriate]

Avez-vous au moins téléchargé le fichier exemple que j'ai déposé ?
Manifestement pas

Je l'ai téléchargé (je ne l'avais pas vu tout à l'heure). J'ai correctement créé mes plages et mon tableau (je crois), mis le code dans le module de la feuille et pas le général, et lors de la sélection de ma préparation il y a un message d'erreur : "la méthode "range" de l'objet "_Worksheet" a échoué". Si je mets les mêmes noms de plage que votre exemple ça me fait un autre message d'erreur : "le nom de champ est incorrect ou manquant dans la zone d'extraction". Savez-vous à quoi ça pourrait être dû ?
Merci en tout cas pour votre aide et votre temps

@Eric
Enorme merci, votre code fonctionne (j'ai renommé correctement mes en-têtes et mes données). En effet je préférerais un code plus simple pour que je puisse l'appliquer à d'autres choses car je risque de devoir faire ça souvent et le filtre avancé est vraiment un outil génial... Mais en attendant je vais pouvoir avancer sur le reste