Comment automatiser Vlookup à laide dExcel VBA

Vlookup est une fonction essentielle d'Excel et est devenue une partie importante du traitement des données. Il fournit un certain nombre de fonctions que vous pouvez combiner avec une base de données complète.

Cependant, si vous souhaitez automatiser cette fonction, vous le pouvez, surtout si vous savez utiliser le langage d'automatisation disponible dans Excel et VBA. Voici comment automatiser la fonction vlookup dans Excel VBA .

Formule de fonction Vlookup dans Excel

=vlookup(lookup_value, lookup_array, column_index, exact_match/partial_match)

Signification des arguments dans la formule :

• lookup_value: La valeur sous-jacente que vous souhaitez rechercher dans le tableau.
• lookup_array: Données source, lookup_value sera utilisé comme référence.
• column_index: La colonne renvoie la valeur.
• exact_match/partial_match: Utilisez 0 ou 1 pour spécifier le type de combinaison.

Fonction VBA Vlookup dans Excel

L'utilisation de la fonction Vlookup n'est pas très différente lorsque vous l'utilisez directement dans Excel ou via VBA. Qu'un analyste de données traite des lignes d'informations ou qu'un responsable d'entrepôt travaille constamment avec de nouveaux produits, la fonction vlookup est utile.

Lorsque vous travaillez avec une plage de recherche dynamique, il est préférable d’automatiser la formule afin d’éviter d’avoir à manipuler plusieurs fois la même formule dans Excel. En automatisant la fonction Vlookup dans VBA, vous pouvez effectuer des calculs multi-colonnes en un seul clic.

Illustration

Cet ensemble de données comporte deux parties : une table de recherche et des points de données cibles. La table de recherche contient 3 colonnes, avec des données dans les deux champs Sous-catégorie et Nom du produit :

La table cible contient les colonnes appropriées, sans sous-dossiers ni données de nom de produit. Notez que les valeurs de la colonne Order ID apparaissent également dans la même colonne de la table de recherche :

1. Effectuez les réglages nécessaires

Commencez par ouvrir l'éditeur de code (appuyez sur Alt + F11 ou accédez à l' onglet Développeur ) dans un nouveau classeur Excel et ajoutez le module pour commencer le codage. Dans l'éditeur de code, ajoutez les lignes suivantes en haut de la fenêtre de programmation pour créer un sous-programme :

Sub vlookup_fn1()End Sub

Le sous-programme est un conteneur pour le code VBA et est nécessaire pour l'exécuter correctement. Une autre alternative consiste à créer un formulaire utilisateur VBA pour rendre l'interface utilisateur plus interactive.

2. Déclarez les variables et créez des étendues de référence

Tout d'abord, vous devez déclarer les types de données des variables à l'aide de la commande Dim :

Dim i as integer, lastrow as long

Ensuite, créez une variable lastrow pour stocker la valeur de la dernière ligne renseignée dans la plage de recherche. La fonction lastrow utilise la fonction end(xldown) pour calculer la dernière référence de ligne dans une plage spécifique.

Dans cet exemple, la variable lastrow contient la valeur de la dernière ligne remplie dans la plage de recherche, plus précisément 17.

lastrow = Sheets("Sheet2").Range("F3").End(xlDown).Row

Dans l'exemple ci-dessus, la table de destination s'étend de A2:C17 et la table source s'étend de F2:H17. La formule vlookup parcourra chaque valeur de la colonne A, la recherchera dans la table source et collera les entrées combinées dans les colonnes B et C.

Cependant, avant d'entrer dans la boucle, vous devez stocker les valeurs de plage dans une nouvelle variable ( my_range ) comme suit :

my_range = Sheets("Sheet2").Range("F3:H" & lastrow)

Vous devez spécifier explicitement la référence de cellule de début (F3) et la référence de colonne de fin (H). VBA ajoute automatiquement des valeurs de ligne pour compléter le tableau de recherche.

3. Écrivez une boucle pour parcourir chaque valeur de recherche

Avant d'écrire la boucle, définissez la valeur de la ligne de départ, 2. La définition d'une valeur de départ pour la boucle est nécessaire lorsque vous traitez des composants dynamiques. Dans la table de destination, la ligne 2 est la première ligne de données. Modifiez cette valeur si les données commencent dans une ligne différente.

i = 2

Vous pouvez utiliser une boucle do-while pour traiter chaque ligne, en commençant à la ligne 2 et en terminant à la ligne 17. Comme le reste du code, cet aspect est flexible ; La boucle s'exécutera jusqu'à ce que la condition soit fausse. Utilisez la condition pour tester une valeur non vide dans la première cellule de la ligne actuelle.

Do While len(Sheets("Sheet2").Cells(i, 1).value) <> 0

A l'intérieur de la boucle, vous pouvez appeler la fonction Vlookup pour remplir les cellules :

Sheets("Sheet2").Cells(i, 2).Value = _ Application.WorksheetFunction.VLookup(Sheets("Sheet2") _ .Cells(i, 1).Value, my_range, 2, False)Sheets("Sheet2").Cells(i, 3).Value = _ Application.WorksheetFunction.VLookup(Sheets("Sheet2") _ .Cells(i, 1).Value, my_range, 3, False)

Ces commandes définissent les valeurs des cellules de la ligne actuelle, respectivement dans les colonnes 2 et 3. Ils utilisent l'objet WorksheetFunction pour appeler la fonction Vlookup, en passant la valeur correspondante de la colonne 1 à la recherche. Ils transmettent également la plage que vous avez initialement définie et l'index de la colonne concernée pour récupérer la valeur finale.

Le code vlookup est prêt, mais vous devez quand même incrémenter la variable row à chaque fois dans la boucle :

i = i + 1

Chaque fois que la boucle s'exécute, elle incrémente la valeur i de 1 . N'oubliez pas que la valeur de la ligne de départ est 2, l'incrément se produit à chaque exécution de la boucle. Utilisez le mot-clé loop pour définir la fin de ce bloc de code.

Lorsque l'intégralité du code est exécuté, il remplira les deux colonnes avec des résultats, en fonction des valeurs de la table source.

Ci-dessous le code de référence complet :

 Sub vlookup_fn1() Dim i As Integer lastrow = Sheets("Sheet2").Range("F3").End(xlDown).Row my_range = Sheets("Sheet2").Range("F3:H" & lastrow) i = 2 Do While Len(Sheets("Sheet2").Cells(i, 1).Value) <> 0 On Error Resume Next Sheets("Sheet2").Cells(i, 2).Value = _ Application.WorksheetFunction.VLookup(Sheets("Sheet2") _ .Cells(i, 1).Value, my_range, 2, False) Sheets("Sheet2").Cells(i, 3).Value = _ Application.WorksheetFunction.VLookup(Sheets("Sheet2") _ .Cells(i, 1).Value, my_range, 3, False) i = i + 1 LoopEnd Sub

4. Créez un bouton pour exécuter le code

Vous pouvez créer un bouton dans un tableau Excel pour l'exécuter en un clic. Insérez la forme souhaitée sur la feuille Excel vierge, faites un clic droit dessus et cliquez sur l' option Attribuer une macro .

Dans la boîte de dialogue, sélectionnez le nom du sous-programme et cliquez sur OK .

Lorsque vous souhaitez exécuter le code, cliquez sur le bouton pour voir immédiatement comment les données sont remplies.

Ci-dessus, comment utiliser Excel VBA pour automatiser les fonctions de recherche . J'espère que l'article vous sera utile.