Je vous propose une nouvelle fonction nommée 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
C’est une 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. Toutefois le nom des deux étiquettes peuvent être différentes (depuis la version 2.1). IL faut alors entrer le nom des deux étiquettes séparé par un point virgule (Exemple: keyLabel:= »Id;General_FK »).
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 conservée
Pour cet exemple, illustré par les images et cette syntaxe
LookupLabelRange shtReference, shtDbAddress, LookupLabel:="adresse", ValueOnly:=False
La formule dans la colonne $H$2:$H$16 de la feuille nommée [dbGeneral]
=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
Sept exemples dans le classeur de démonstration à télécharger.
Sub Exemples()
' Tous les 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"
' ... Exemple 7 - Fait référence au tableau de la feuille [dbCars]
' dont l'étiquette de la clé étrangère est différente de la clé primaire
With ThisWorkbook
LookupLabelRange .Worksheets("dbGeneral"), .Worksheets("dbCars"), _
LookupLabel:="Véhicule", keyLabel:="Id;General_FK"
End With
End Sub
Commentaires récents