Discussion :

[Philippe Tulliez]

Bonjour,
Je vous propose une nouvelle procédure à utiliser : LookupLabelRange qui ajoute une colonne à une liste de données en faisant référence à la colonne d'une autre liste par la fonction INDEX ou par le résultat de cette formule.
Petite explication
Fonction effectuant depuis une plage (SourceData) une recherche dans une autre plage (LookupData) en fonction d'une étiquette de colonne (LookupLabel).
Pour que cette recherche soit possible une clé unique (KeyLabel) doit exister dans les deux plages
Si l'argument KeyLabel est vide, la recherche s'effectue sur la première colonne de [LookupData] en cherchant la valeur de la colonne A.
Si l'argument LookupLabel n'est pas trouvé dans LookupData un message est renvoyé à l'utilisateur et la procédure est interrompue sans heurts.
Si l'argument facultatif ValueOnly (True par défaut) est à False, la formule de recherche est gardée

Pour cet exemple, illustré par les images et cette syntaxe

Code :

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

 LookupLabelRange shtReference, shtDbAddress, LookupLabel:="adresse", ValueOnly:=False

La formule dans la colonne $H$2:$H$16 de la feuille nommée [dbGeneral]

Code :

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

=INDEX(dbAddress!$A$2:$F$16; EQUIV($A2; dbAddress!$A$2:$A$16; 0); 4)

La propriété NumberFormat de la première cellule cellule de la plage source est également copiée sur l'ensemble de la colonne cible.
Elle renvoie un objet Range représentant la plage de la liste avec la colonne nouvellement créée.


Les arguments (Cinq arguments dont deux facultatifs).

SourceData (Object) : Peut-être de type WorkSheet ou Range. Plage où doit se trouver le résultat de la recherche (Données + Ligne des titres)
LookupData (Object) : Table de recherche (Données + Ligne des titres
LookupLabel (String) : Etiquette de colonne
[KeyLabel] (String) : Etiquette de référence (Première colonne si omis)
[ValueOnly] (Booléen) : (d:=True) doit garder le résultat, Si False garde la formule

La syntaxe

Résultat à la fin de la procédure


Un classeur à télécharger avec des exemples
Six exemples dans le classeur de démonstration à télécharger

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
Sub Exemples()
 Dim rngLookup As Range
 With ThisWorkbook
  Set rngLookup = .Worksheets("dbAddress").Range("A1").CurrentRegion
 End With
 ' Exemple 1 - L'étiquette "Ville" (argument LookupLabel) n'existe pas dans la feuille dbAddress
 LookupLabelRange SourceData:=shtReference, LookupData:=rngLookup, LookupLabel:="Ville"
 ' Exemple 2 - L'étiquette "myId" passé par l'argument KeyLabel n'est pas présent
 LookupLabelRange SourceData:=shtReference, LookupData:=rngLookup, LookupLabel:="adresse", KeyLabel:="myId"
 ' Exemples suivants, Arguments correctement passés.
 ' ... Exemple 3 - Colonne "adresse" ajoutée en gardant formule (ValueOnly à False)
 LookupLabelRange SourceData:=shtReference, LookupData:=rngLookup, LookupLabel:="adresse", ValueOnly:=False
 ' Exemples 4 et 5 - Le tableau commence en cellule G4 de la feuille [dbDateNaiss]
 '   et les références ("id") ne sont pas toutes présentes, si formule gardée renvoie erreur -> #N/A
 ' ... Exemple 4 - Colonne "CA"
 LookupLabelRange shtReference, shtdb2.Range("G4"), LookupLabel:="CA", ValueOnly:=False
 ' ... Exemple 5 - Colonne "DateNaiss"
 LookupLabelRange shtReference, shtdb2.Range("G4"), LookupLabel:="Date Naiss"
 ' ... Exemple 6 - Colonne "Enfant" dont les valeurs sont 0 ou -1
 '     un format personnalisé (;"Oui";"Non") est appliqué
 LookupLabelRange shtReference, shtDbAddress, LookupLabel:="Enfant"
End Sub

Malgré le soin apporté à la programmation de cette procédure et aux multiples tests réalisés, il est possible qu'il subsiste un bogue qui m'aurait échappé.
Vos réactions sont les bienvenues

[illight]

Bonjour Philippe,


Ne serait-ce pas un équivalent d'une simple RECHERCHEV ? Pourquoi utiliser une macro quand une fonction propre à Excel existe déjà ?

Alors effectivement, quand on a un gros fichier, il est évident que ta fonction sera surement plus rapide qu'utiliser la RECHERCHEV, qui mettra des plombes à calculer.

Merci pour ta réponse

[Philippe Tulliez]

Bonjour Olivier,
Merci pour ta question.
Ce n'est pas tout à fait l'équivalent d'un RECHERCHEV mais c'est assez proche.
J'utilise la combinaison de INDEX et EQUIV qui est moins contraignante et plus puissante que RECHERCHEV. Elle permet notamment la recherche sur une autre colonne que la première.
Le masque de formule que j'utilise

Code :

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

=INDEX(<LookupTable>, MATCH(<RefId>, <Row>, 0), <Col>)

après modification cela donne (pour l'exemple affiché)

Code :

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

=INDEX(dbAddress!$A$2:$F$16; EQUIV($A2; dbAddress!$A$2:$A$16; 0); 4)

Alors oui, on peut le faire manuellement mais je l'ai développé surtout évidemment dans le but de rapatrier rapidement sur une seule feuille des données qui seraient éparpillées sur plusieurs feuilles. D'ailleurs, par défaut la fonction conserve la valeur et pas la formule
J'avais lu quelques questions sur le forum traitant de ce sujet ce qui m'a donné l'idée de développer cette fonction.

[illight]

Bonjour Philippe,


Après analyse, il y a un autre avantage aussi sur ta fonction : la suppression de colonne n'impactera pas le résultat de la recherche.
En effet, lorsque l'on effectue une RECHERCHEV, le troisième argument est fixe, est n'est pas modifiant suite à la suppression d'une colonne.
L'avantage de ta solution est qu'elle prend le titre des colonnes, et non le numéro de celle-ci

Par contre, dans ton classeur exemple, il y a une faute de frappe dans ton image pour la formule. Tu as écrit ça :

Code :

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

LookupLabelRange SourceData:=Worksheets("dbGeneral"), LookupData:=Worksheets("dbAddresse"), LookupLabel:="Adresse", KeyLabel:="id"

Ta feuille ne s'appelle pas dbAddresse, mais dbAddress

[Philippe Tulliez]

Bonjour Olivier,

Par contre, dans ton classeur exemple, il y a une faute de frappe dans ton image pour la formule.

Je te remercie pour ta bonne observation.
J'ai effectué la modification et remplacé le classeur à télécharger et comme j'avais déjà ajouté une fonctionnalité supplémentaire à cette procédure, le classeur contient la nouvelle version 2.1
Maintenant, si la clé de référence est différente dans les deux tables, on peux indiquer dans l'argument [KeyLabel] le nom des deux étiquettes séparé par un point virgule.
Exemple :

Code :

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

1
2
3
4
5
6
Sub Exemple7()
 With ThisWorkbook
  LookupLabelRange .Worksheets("dbGeneral"), .Worksheets("dbCars"), _
                    LookupLabel:="Véhicule", keyLabel:="Id;General_FK"
 End With
End Sub

[EDIT]

En effet, lorsque l'on effectue une RECHERCHEV, le troisième argument est fixe

Je ne sais pas si j'ai bien compris cette remarque mais tu peux également utiliser la fonction EQUIV comme troisième argument de la formule RECHERCHEV afin de rendre dynamique la position de l'étiquette recherchée.
Exemple : (Toujours avec les deux fichiers de l'illustration)

Code :

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

=RECHERCHEV(A2;dbAddress!$A$2:$F$16;EQUIV("Adresse";dbAddress!$A$1:$F$1;0))

[illight]

Je ne sais pas si j'ai bien compris cette remarque mais tu peux également utiliser la fonction EQUIV comme troisième argument de la formule RECHERCHEV afin de rendre dynamique la position de l'étiquette recherchée.
Exemple : (Toujours avec les deux fichiers de l'illustration)

Code :

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

=RECHERCHEV(A2;dbAddress!$A$2:$F$16;EQUIV("Adresse";dbAddress!$A$1:$F$1;0))

Tu as parfaitement compris mon problème, et je te remercie pour la solution. Il faudra que je teste cette fonction à l'occasion


En tout cas, je vais mettre ton classeur dans mes favoris, car j'ai souvent des recherches de ce genre à faire

Maintenant, faut que je me prenne du temps pour comprendre la fonction (j'aime bien comprendre ), parce que je l'ai lu 2 fois, j'ai pas tout compris