XL 2013 Faire une recherche dans un tableau

[Benoit72]

Bonjour à tous,
Je cherche à créer un fichier de défauts / remèdes sous excel avec un onglet de recherche rapide.
Je détaille un peu.
Dans le fichier Excel, j'ai une feuille contenant un tableau. Le tableau comprend environs 16 colonnes de défaut et environs 20 lignes de remèdes.
Pour chaque défaut, j'attribue 3 ou 5 remèdes selon le défaut.
J'ai ensuite un second onglet. Dans ce second onglet, il y a une liste déroulante me permettant de choisir un type de défaut (ceux parmi les 16 colonnes)
En fonction, du chois de la liste déroulante, je veux afficher le(s) remède(s) correspondant au défaut choisi.
Pour afficher par exemple le 2ème remède de la colonne E, j'utilise une fonction du type : =INDEX(BA4:BA41;EQUIV(2;E4:E41;0);EQUIV("REMEDES";BA3;0))
Par contre, la colonne E varie en fonction du défaut choisi en liste déroulante.
J'ai un problème pour définir la colonne de recherche.
J'utilise la formule : =EQUIV("Lobe";A3:AZ3;0) pour trouver le numéro de colonne.
J'utilise un module macro pour convertir la valeur numérique de la colonne en valeur texte. Exemple, ici je convertie la colonne N°5 en valeur texte "E" = 5ème colonne.
Je veux maintenant le résultat "E" dans ma formule =INDEX(BA4:BA41;EQUIV(2;E4:E41;0);EQUIV("REMEDES";BA3;0)), mais ca ne fonctionne pas.
Est ce que je m'y prends correctement ou y a t'il une autre solution ?

 

[soan]

Bonjour à tous,

ma solution VBA est fonctionnelle et donne les résultats attendus ; cependant, Marcel32 a fait bien mieux que moi, et sans VBA !

donc je recommande à tous les lecteurs de cette conversation de privilégier la solution proposée par Marcel32 dans son post #4. (cliquez à gauche sur le lien bleu pour aller directement dessus) ; un grand BRAVO pour Marce32 !!!
​

---

j'suis tenté d'supprimer ma solution VBA, mais j'la laisse quand même pour ceux qui ont envie d'lire un code VBA : ça pourra être un bon p'tit exercice d'entraînement !
​

---

sur la 3ème feuille "Liste_défauts", nouvelle formule en A2 :

=SUPPRESPACE(DECALER(REMEDES!A$3;0;LIGNE()-2))

pour l'instant, la fonction SUPPRESPACE() te semble inutile, mais ne t'en préoccupe pas : tu vas voir très bientôt qu'en fait c'est nécessaire.​

(j'ai déjà étiré cette formule vers le bas, jusqu'en ligne 53)

---

va sur la 2ème feuille "REMEDES".

regarde la ligne 3 des en-têtes de colonnes ; c'est plus lisible, hein ? pour faire ça, on ne peut pas utiliser l'alignement gauche car le texte est vertical ; j'ai donc dû ajouter 7 espaces devant chaque libellé d'en-tête de colonne ➯ ça remonte le texte au-dessus des petites flèches de filtre et de tri ➯ le début du texte n'est plus caché : on le voit très clairement ; bien sûr, tu comprends maintenant pourquoi SUPPRESPACE() est nécessaire dans la formule précédente : c'est pour enlever les 7 espaces du début. ​

(sur cette feuille, je n'ai rien changé d'autre)

---

va sur la 1ère feuille "Questionnaire".

là, je te laisse faire tous les tests que tu veux en C6 ; vérifie bien toutes les actions correspondant au type de défaut choisi ; vérifie la sélection d'un item, le double-clic, et la touche Entrée ; vérifie aussi ce que ça fait quand tu effaces le contenu de C6 avec la touche Suppression ; c'est ok ? ça te va ? ben alors il ne te reste plus qu'à t'plonger dans la lecture du code VBA ci-dessous ! (bonne lecture, et étudie bien ! )​

---

code VBA de Feuil1 "Questionnaire" (51 lignes) :

Option Explicit

Dim f As Worksheet, a()

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Dim mémo$, n&
  With Target
    If .CountLarge > 1 Or .Address(0, 0) <> "C6" Then ComboBox1.Visible = 0: Exit Sub
    If mémo <> "" Then If IsError(Application.Match(Range(mémo), a, 0)) Then Range(mémo) = ""
    Set f = Worksheets("Liste_défauts"): n = f.Cells(Rows.Count, 1).End(3).Row
    Do While f.Cells(n, 1) = "": n = n - 1: Loop: a = Application.Transpose(f.[A2].Resize(n - 1))
    ComboBox1.List = a: ComboBox1.Height = .Height + 3: ComboBox1.Width = .Width
    ComboBox1.Top = .Top: ComboBox1.Left = .Left: ComboBox1 = .Value
    ComboBox1.Visible = -1: ComboBox1.Activate: mémo = .Address
  End With
End Sub

Private Sub ComboBox1_Change()
  Dim d1 As Object, tmp$, c, n As Byte, v As Byte, col%, lg1&, lg2&
  If ComboBox1 <> "" And IsError(Application.Match(ComboBox1, a, 0)) Then
    Set d1 = CreateObject("Scripting.Dictionary"): tmp = UCase$(ComboBox1) & "*"
    For Each c In a
      If UCase$(c) Like tmp Then d1(c) = ""
    Next c
    ComboBox1.List = d1.keys: ComboBox1.DropDown
  End If
  [C6] = ComboBox1: col = ComboBox1.ListIndex + 1: If col = 0 Then Exit Sub
  Application.ScreenUpdating = 0: [C8].Resize(7).ClearContents: lg2 = 8
  With Worksheets("REMEDES")
    n = .Cells(42, col): If n = 0 Then Exit Sub
    For lg1 = 4 To 41
      v = Val(.Cells(lg1, col))
      If v > 0 Then
        Cells(lg2, 3) = .Cells(lg1, "BA"): lg2 = lg2 + 1
        n = n - 1: If n = 0 Then Exit For
      End If
    Next lg1
  End With
End Sub

Private Sub ComboBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
  Dim n&: n = f.Cells(Rows.Count, 1).End(3).Row: Do While f.Cells(n, 1) = "": n = n - 1: Loop
  ComboBox1.List = Application.Transpose(f.[A2].Resize(n - 1)): ComboBox1.DropDown
End Sub

Private Sub ComboBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
  If KeyCode <> 13 Then Exit Sub
  If IsError(Application.Match(ActiveCell, a, 0)) Then ActiveCell = ""
  [C7].Select
End Sub

soan

 

[TooFatBoy]

Bonjour,

Une autre proposition en pièce jointe.


[edit]
Bon, comme me l'a très justement fait remarquer l'ami @soan ça ne fonctionne pas mon truc !
[/edit]

 

[TooFatBoy]

Alors je remplace par la version 2, qui marche (l'ami @soan l'a testée ).

 

[soan]

@Benoit72

ne prends PAS la "solution" du post #3 de Marcel32 car elle est erronée, et NON fonctionnelle ! la solution VBA que j'ai proposée dans mon post #2 est fonctionnelle : elle marche bien, et honnêtement elle est quand même pas mal ! cependant, Marcel32 a fait beaucoup mieux, et sans VBA : c'est la solution qu'il a proposée dans son post #4, et je te conseille vivement de la choisir comme solution à la place de la mienne ! d'ailleurs, je laisse la mienne uniquement pour que tu puisses t'entraîner à lire et comprendre un code VBA ; ça pourrait éventuellement te servir pour d'autres choses. lien sur le post #4 de Marcel32 ; tu pourras lui mettre un vote de +1 (ce que j'ai déjà fait) ; et comme c'est toi le demandeur, toi seul peut marquer ce post #4 comme solution.​

soan

 

[TooFatBoy]

Euh... merci @soan, mais ma proposition comme tu le sais était juste un exercice de style : faire presque aussi bien que ta proposition, mais sans VBA.
Je ne suis franchement pas un cador, alors c'était un challenge pour moi. Et comme j'ai quasiment réussi à faire ce que je voulais, j'ai voulu partager ma solution. C'est tout.

Perso, je conseille sans la moindre hésitation de prendre la solution de l'ami @soan qui est mille fois mieux et plus facile à maintenir grâce au VBA.

 

[Benoit72]

@Benoit72

ne prends PAS la "solution" du post #3 de Marcel32 car elle est erronée, et NON fonctionnelle ! la solution VBA que j'ai proposée dans mon post #2 est fonctionnelle : elle marche bien, et honnêtement elle est quand même pas mal ! cependant, Marcel32 a fait beaucoup mieux, et sans VBA : c'est la solution qu'il a proposée dans son post #4, et je te conseille vivement de la choisir comme solution à la place de la mienne ! d'ailleurs, je laisse la mienne uniquement pour que tu puisses t'entraîner à lire et comprendre un code VBA ; ça pourrait éventuellement te servir pour d'autres choses. lien sur le post #4 de Marcel32 ; tu pourras lui mettre un vote de +1 (ce que j'ai déjà fait) ; et comme c'est toi le demandeur, toi seul peut marquer ce post #4 comme solution.​

soan

Merci pour votre aide, c'est exactement ce que je voulais faire.