Formule pour afficher liste déroulante après recherche

Bonjour à tous,

je souhaiterais savoir s'il est possible de créer une liste déroulante dans une cellule avec seulement les résultats d'une recherche.

Je m'explique :

dans une feuille cachée, j'ai deux colonnes :

• colonne A : un numéro, code unique d'une référence de pièce.
• colonne B : la description de la pièce.

Dans une autre feuille, quand je tape le numéro référence de la pièce dans une cellule, la cellule d'à coté me donne la description.

Jusque là tout va bien.

Mais, si je ne connais pas le numéro, je souhaiterais faire une recherche inverse.

Mettre dans une cellule, un mot clef, qui me permettrait d'avoir la liste complète (sous forme de liste déroulante, ou autre) de toutes les descriptions qui contiennent ce mot clef.

Pour ensuite retrouver ce bon numéro.

Je précise, que l'on ne peut pas avoir accès à la feuille des descriptions, et donc pas de recherche ou de filtres.

Je vous remercie d'avance du temps que vous voudriez bien m'accorder.

Bonjour,

Mettre un modèle représentatif de ce que tu veux obtenir serait indiqué...

Bonjour,

je joins à un fichier d'exemple, avec des explications.

Je vous remercie.

Re,

Macro placée dans le module de la feuille "Feuil1" :

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim d As Object, aa, i%, mc$
    If Target.Address <> "$B$9" Then Exit Sub
    mc = Target
    Target.Offset(1).Validation.Delete
    If mc = "" Then Exit Sub
    Set d = CreateObject("Scripting.Dictionary")
    aa = Worksheets("A cacher").Range("A1").CurrentRegion
    For i = 2 To UBound(aa)
        If InStr(1, aa(i, 2), mc) > 0 Then d(aa(i, 2)) = ""
    Next i
    aa = Join(d.keys, ",")
    Target.Offset(1).Validation.Add xlValidateList, , , aa
End Sub

Cordialement.

Bonjour,

je vous remercie pour votre réponse.

Mais, j'ai une erreur sur la dernière ligne du code.

Est-elle du à un format de cellule s'il vous plait?

L'erreur survient quand le mot clef n'a aucune correspondance.

J'ai alors "Erreur d'excecution 1004"

Saurait-on ignorer cette partie, ou alors, afficher un message "Aucune correspondance" s'il vous plait?

D'avance je vous remercie.

Si erreur sur la mise en place de la liste de validation, c'est que tu as tapé un mot clé n'existant pas dans ta liste, donc la liste échoue...

Mettre la fin sous condition :

    If d.Count > 0 Then
        aa = Join(d.keys, ",")
        Target.Offset(1).Validation.Add xlValidateList, , , aa
    End If

Eventuellement, tu pourras s'il n'y a pas d'élément à lister t'afficher un message le signalant...

Cordialement.

Je te remercie.

Cela fonctionne parfaitement.

J'aurais aimé avoir un peu plus d'explications sur ton code.

Est-il possible d'y rajouter des commentaires s'il te plait?

Si besoin de le modifier ultérieurement.

Merci

Et comment faire pour ne pas respecter la case, s'il te plait?

Par exemple ; "module" donne un résultat de la recherche

MAIS "Module" n'en donne aucun.

Je sais que cela fait beaucoup de questions, mais j'aimerais juste comprendre s'il te plait.

Je te remercie.

En supposant que voulant ignorer la casse, tu ne vas pas pousser le vice à taper tes mots-clés avec des majuscules mais que tu les tapes en minuscules :

        If InStr(1, LCase(aa(i, 2)), mc) > 0 Then d(aa(i, 2)) = ""

En passant l'élément dans lequel tu recherches ton mot-clé en minuscules, il sera donc trouvé quelle que soit la casse initiale.

Pour te garantir de toute intrusion intempestive de majuscule dans le mot-clé, tu peux faire de même lors de sa récupération :

    mc = LCase(Target)

Oui, merci.

J'avais aussi mis "Option Compare Text" en haut de la page.

Par contre, je n'arrive pas à remplir la case B11 (Le numéro associé à la description).

Meme avec "RECHERCHEV"

Explications :

La procédure est relativement simple, d'abord il s'agit d'une procédure que l'on qualifie habituellement d'évènementielle, soit qui se déclenche automatiquement lorsque survient l'évènement qui la concerne.

Elle doit se trouver obligatoirement placée dans le module de Document, objet sur lequel on surveille l'évènement, pour fonctionner. Au cas particulier, dans le module de la feuille sur laquelle tu opères ta recherche par mot-clé.

L'évènement attendu étant la saisie d'un mot-clé, soit un changement de contenu d'une cellule de la feuille, c'est l'évènement Change qui est mis à contribution...

Mais cet évènement va se déclencher chaque fois qu'une cellule de la feuille va se trouver modifiée, or seule la cellule destinée à accueillir le mot-clé nous intéresse. S'agissant d'une seule cellule, on peut aisément la cibler par son adresse, d'où :

    If Target.Address <> "$B$9" Then Exit Sub

on sort de la procédure sans attendre dès lors que notre cellule n'est pas concernée (NB : la propriété Address sans paramètres renvoie l'adresse sous forme texte dans le style référence absolue, si on omettait les "$", la comparaison échouerait donc).

La cellule étant concernée, on récupère le mot-clé dans une variable pour utilisation ultérieure. Mais la modification peut aussi bien consister en la saisie d'un mot-clé qu'en l'efffacement de la cellule, l'évènement reste le même. Dans ce dernier cas, il n'y aurait rien à faire sauf éliminer une liste préexistante éventuelle, ce qu'on aura à faire dans tous les cas. On le fait donc, et si notre variable est vide on s'en tient là et on sort...

    Target.Offset(1).Validation.Delete
    If mc = "" Then Exit Sub

Si mot-clé effectif, on va aller chercher les expressions de la liste dans lesquelles il figure. Et on recueille ces expressions au moyen de l'outil Dictionnaire.

Un dictionnaire est un ensemble de couples clé-valeur dans lequel la clé est un élément unique (pas de doublon). On l'utilise fréquemment car il s'agit d'un outil particulièrement rapide à l'exécution, et que ne laissant pas passer de clé doublon il constitue un filtre naturel pour obtenir une liste d'éléments uniques. Ici, étant donné que les descriptions ne sont pas susceptibles en principe de constituer des doublons, cette question ne se pose donc pas, mais le choix du dico dans le cas qui nous occupe est motivé par la capacité de former un tableau des clés (ou des valeurs) par la simple affectation à une variable, par exemple : aa = d.keys, d étant le dictionnaire constitué.

On teste donc si chaque expression (description) contient le mot-clé et si c'est le cas on forme un élément de dico dont la clé sera la description et la valeur sera vide (pas d'utilité) : d(aa(i, 2)) = "". (A noter que l'on a affecté les données à tester à une variable de type Variant, permettant d'obtenir un tableau, plus rapide à parcourir que la plage Excel.)

On constitue la liste de validation à partir des clés, en utilisant la fonction Join :

        aa = Join(d.keys, ",")

On réutilise la variable aa redevenu disponible une fois le dico constitué, d.keys renvoie un tableau des clés du dico, Join transforme ce tableau en chaîne (texte) en intercalant un séparateur entre chaque élément, ici une virgule (séparateur obligatoire d'élément d'une liste de validation passée sous forme de chaîne en VBA).

Cordialement.

L' Option Compare Text est bien sûr possible...

Je dirai que j'évite au maximum les options qui dérogent aux options par défaut, et qui peuvent être source d'erreur lorsque l'on reprend un code ultérieurement...

Jamais l'Option Base 1, car celle-ci on peut toujours s'en passer. Quant à l'Option Compare, il est des cas où l'on opère quantité de comparaison, notamment utilisant Like, et cela compliquerait de s'en passer. Dans ce cas, j'isole toute les procédures concernées dans un module standard dédié, muni de l'Option Compare Text, ce qui me garantit qu'elle ne sera active que dans ce module...

Tu verrras à l'usage... !

RECHERCHEV opérant la recherche dans la première colonne, pour renvoyer le contenu de la première à partir de la seconde, c'est avec INDEX (sur la 1re) et EQUIV (pour chercher la ligne sur la 2nde) qu'il faut opérer.

(Tu pourrais aussi avec DECALER et EQUIV...)

Cordialement.

Bonjour MFerrand,

je vous remercie pour votre aide et vos explications.

C'est parfait et complet.

Cordialement

Bonjour,

Apparemment, la liste déroulante des propositions des descriptions est réduite à un certains nombres de caractères.

De ce fait, si la description est longue, elle est sur plus d'une ligne dans la liste déroulante.

Est-il possible de l'avoir que sur une ligne, ou une option qui ne permet pas la limitation de caractères, s'il vous plait?

Merci