Fonction recherche v decaler indirect

[fabpi]

Bonjour à tous,

Je rencontre un problème récurrent et j'espère que qq'un pourra m'être d'une aide !!

J'ai deux colonnes : En B, les villes et en C des références

Je souhaiterai voir afficher en colonne F, toutes les références liées à la cellule de saisie (le nom d'une ville)
Je met le fichier en jointure.

JE vous remercie d'avance

 

[R@chid]

Re : Fonction recherche v decaler indirect

Bonjour et Bienvenu sur XLD,
si les données sont triées sur la colonne B,
en F3,

=SI(LIGNES($3:3)<=NB.SI(B:B;E$3);INDEX(C:C;EQUIV(E$3;B:B;0)+LIGNES($3:3)-1);"")

@ tirer vers le bas
@ micalement

 

[fabpi]

Re : Fonction recherche v decaler indirect

Merci pour tout,

ça marche parfaitement !
Est-il posible de l'expliquer en quelques mots si le temps le permet ?

Cordialement

 

[Robert]

Re : Fonction recherche v decaler indirect

Bonjour Fabpi et bienvenu(e), bonjour Rachid (nouvel avatar !?), bonjour le forum,

Quand je vois que Rachid expédie ça en une seule ligne je suis vert...

Alors, dans la série "Comment faire simple quand on peut faire compliqué"... En pièce jointe ton fichier modifié avec une proposition VBA. Le code se trouve dans le composant ThisWokbook, la procédure Open qui met en place la validation de données en E2 en fonction des données de la colonne B et la macro événementielle Change du composant Feuil1(Feuil1) qui place les données correspondantes dans F2.
Le code à l'ouverture :

Public Sub Workbook_Open()
Dim dico As Object 'déclare la variable dico (DICtiOnnaire)
Dim dl As Integer 'déclare la variable dl (Dernière Ligne)
Dim pl As Range 'déclare la variable pl (PLage)
Dim cel As Range 'déclare la variable cel (CELlule)
Dim temp As Variant 'déclare la variable temp (tableau TEMPoraire)
Dim i As Integer 'déclare la variable i (Incrément)
Dim lst As String 'déclare la variable lst (LiSTe de validation)

Set dico = CreateObject("Scripting.Dictionary") 'définit le dictionnaire dico
With Sheets("Feuil1") 'prend en compte l'onglet "Feuil1"
        dl = .Cells(Application.Rows.Count, 2).End(xlUp).Row 'définit la variable dl (dernière ligné éditée de la colonne 2 (=B)
        Set pl = .Range("B3:B" & dl) 'définit la plage pl
    For Each cel In pl 'boucle sur toutes les cellules cel de la plage pl
        dico(cel.Value) = "" 'alimente le dictionnaire dico
    Next cel 'prochaine cellule de la boucle
    temp = dico.keys 'récupère la liste sans doublon
    Call tri(temp, LBound(temp, 1), UBound(temp, 1)) 'lance la procédure de tri alphabétique sans doublon
    For i = 0 To UBound(temp, 1) 'boucle sur tous les éléments du tableau temp
        lst = IIf(lst = "", temp(i) & ",", lst & temp(i) & ",") 'définit la liste lst
    Next i 'prochain élément de la boucle
    With .Range("E2").Validation 'prend en compte la validation de données de la cellle E2
        .Delete 'supprime une éventuelle validation de données déjà existante
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=lst 'ajoute la liste lst comme validation de données
    End With 'fin de la prose en compte de la validation de données de la cellule E2
End With 'fin de la prise en compte de l'onglet "Feuil1"
End Sub


Sub tri(a As Variant, gauc As Integer, droi As Integer) 'tiré du site de Jacques BOISGONTIER : [url=http://boisgontierjacques.free.fr/pages_site/Dictionnaire.htm]Objet dictionary[/url]
Dim ref As String
Dim g As Integer, d As Integer
Dim tmp As String
ref = a((gauc + droi) \ 2)
g = gauc: d = droi
Do
    Do While a(g) < ref: g = g + 1: Loop
    Do While ref < a(d): d = d - 1: Loop
    If g <= d Then
        tmp = a(g): a(g) = a(d): a(d) = tmp
        g = g + 1: d = d - 1
    End If
Loop While g <= d
If g < droi Then Call tri(a, g, droi)
If gauc < d Then Call tri(a, gauc, d)
End Sub

Le code au changement dans l'onglet Feuil1 :

Private Sub Worksheet_Change(ByVal Target As Range) 'au changement dans l'onglet
Dim dl As Integer 'déclare la variable dl (Dernière Ligne)
Dim pl As Range 'déclare la variable pl (PLage)

If Target.Address = "$E$2" Then 'condition : si le changement a lieu dans la cellule E2
    With Sheets("Feuil1") 'prend en compte l'onglet "Feuil1"
        .Columns(6).ClearContents 'efface le contenu de la colonne F
        If Target = "" Then Exit Sub 'ai E2 est effacée, sort de la procédure
        dl = .Cells(Application.Rows.Count, 2).End(xlUp).Row 'définit la variable dl (dernière ligné éditée de la colonne 2 (=B)
        Set pl = .Range("B3:B" & dl) 'définit la plage pl
        .Range("B2").AutoFilter 'lance la procédure de filtre automatique
        .Range("B2").AutoFilter field:=1, Criteria1:=Target.Value 'filtre automatique en B2 sur la colonne B et avec le critère sélectionné en E2
        pl.SpecialCells(xlCellTypeVisible).Offset(0, 1).Copy .Range("F2") 'copy les cellules visibles de la colonne C en F2
        .Range("B2").AutoFilter 'supprime le filtre automatique
    End With 'fin de la prise en compte de l'onglet "Feuil1"
End If 'fin de la condition
If Target.Column = 2 Then ThisWorkbook.Workbook_Open 'si le changement a lieu dans la colonne 2, lance la procédure d'ouverture du classeur
End Sub

le fichier :

 

[fabpi]

Re : Fonction recherche v decaler indirect

Merci Robert,

Cette macro va me servir pour des problèmes de listes que je rencontre !

Si j'avais su que les réponses étaient en instannées et aussi efficaces je serai arrivé depuis bien plus longtemps qu'aujourd'hui sur le site !!!

Encore merci

@+