RECHERCHEV dans Excel VBA - Comment écrire le code VLOOKUP dans VBA?

Vlookup est une fonction de feuille de calcul dans Excel mais elle peut également être utilisée dans VBA, la fonctionnalité de Vlookup est similaire à la fonctionnalité de VBA et dans la feuille de calcul à la fois, car il s'agit d'une fonction de feuille de calcul, la méthode pour utiliser Vlookup dans VBA est via Application.WorksheetFunction méthode et les arguments restent les mêmes.

Fonction RECHERCHEV dans Excel VBA

La fonction RECHERCHEV dans Excel est utilisée pour rechercher une valeur dans un tableau et renvoyer sa valeur correspondante à partir d'une autre colonne. La valeur à rechercher doit être présente dans la première colonne. Il est également nécessaire d'indiquer s'il faut rechercher une correspondance exacte ou une correspondance approximative. La fonction de feuille de calcul VLOOKUP peut être utilisée dans le codage VBA. La fonction n'est pas VBA intégrée et ne peut donc être appelée qu'à l'aide de la feuille de calcul.

La fonction RECHERCHEV dans Excel a la syntaxe suivante:

Dans lequel, lookup_value est la valeur à rechercher, table_arrray est la table, col_index_num est le numéro de colonne de la valeur de retour, range_lookup signifie si la correspondance est exacte ou approximative. range_lookup peut être TRUE / FALSE ou 0/1.

Dans le code VBA, la fonction RECHERCHEV peut être utilisée comme:

Application.WorksheetFunction.vlookup (lookup_value, table_array, col_index_num, range_lookup)

Comment utiliser VLookup dans Excel VBA?

Voici quelques exemples du code VLookup dans Excel VBA.

Code VLookup dans Excel VBA Exemple # 1

Voyons comment nous pouvons appeler la fonction de feuille de calcul VLOOKUP dans Excel VBA.

Supposons que vous ayez des données sur l'identité, le nom et les notes moyennes des élèves.

Maintenant, vous voulez rechercher les notes obtenues par un étudiant avec l'ID 11004.

Pour rechercher la valeur, procédez comme suit:

  • Accédez à l'onglet Développeur et cliquez sur Visual Basic.
  • Sous la fenêtre VBA, allez dans Insérer et cliquez sur Module.
  • Maintenant, écrivez le code VBA VLOOKUP. Le code VBA VLOOKUP suivant peut être utilisé.

Sub vlookup1 ()
Dim student_id As Long
Dim marks As Long
student_id = 11004
Set myrange = Range ("B4: D8")
marks = Application.WorksheetFunction.VLookup (student_id, myrange, 3, False)
End Sub

Tout d'abord, définissez un identifiant d'étudiant, qui est la valeur à rechercher. Par conséquent, nous définissons,

id_étudiant = 11004

Ensuite, nous définissons la plage dans laquelle la valeur et la valeur de retour existent. Puisque nos données sont présentes dans les cellules B4: D8, nous définissons une plage-myrange comme:

Définir myrange = Range ("B4: D8")

Enfin, nous entrons la fonction RECHERCHEV à l'aide de la fonction Feuille de calcul dans une variable, marquée comme:

marks = Application.WorksheetFunction.VLookup (student_id, myrange, 3, False)

Pour imprimer les marques dans une boîte de message, utilisons la commande suivante:

MsgBox "Étudiant avec ID:" & student_id & "a obtenu" & note & "notes"

Il retournera:

Un étudiant avec ID: 11004 a obtenu 85 points.

Maintenant, cliquez sur le bouton Exécuter.

Vous remarquerez qu'une boîte de message apparaîtra dans la feuille Excel.

Code VLookup dans Excel VBA Exemple # 2

Supposons que vous ayez les données sur les noms des employés et leur salaire. Ces données reçoivent les colonnes B et C. Maintenant, vous devez écrire un code VBA VLOOKUP tel que, étant donné le nom de l'employé dans une cellule, F4, le salaire de l'employé sera retourné dans la cellule G4.

Écrivons le code VBA VLOOKUP.

  1. Définissez la plage dans laquelle les valeurs sont présentes, c'est-à-dire les colonnes B et C.

Définir myrange = Range ("B: C")

  1. Définissez le nom de l'employé et saisissez le nom de la cellule F4.

Set name = Range («F4»)

  1. Définissez le salaire dans la cellule G4.

Définir le salaire = Fourchette ("G4")

  1. Maintenant, appelez la fonction RECHERCHEV à l'aide de WorksheetFunction dans VBA et saisissez-la dans salaire.Valeur. Cela renverra la valeur (sortie de la fonction Vlookup) dans la cellule G4. La syntaxe suivante peut être utilisée:

salaire.Valeur = Application.WorksheetFunction.VLookup (nom, myrange, 2, False)

  1. Maintenant, exécutez le module. La cellule G4 contiendra le salaire de l'employé après avoir exécuté le code VBA VLOOKUP.

Supposons que vous modifiez la valeur de la cellule F4 en «David» dans la feuille de calcul et que vous réexécutez le code, et il renverra le salaire de David.

Code VLookup dans Excel VBA Exemple # 3

Supposons que vous ayez les données de l'employé de votre entreprise, avec son identifiant, ses noms, son service et son salaire. En utilisant Vlookup dans VBA, vous souhaitez obtenir les détails du salaire d'un employé en utilisant son nom et son service.

Étant donné que la fonction vlookup d'Excel recherche la valeur de recherche dans une seule colonne, qui est la première colonne de table_array, il est nécessaire de créer d'abord une colonne contenant le «Nom» et le «Service» de chaque employé.

Dans VBA, insérons les valeurs «Nom» et «Département» dans la colonne B de la feuille de calcul.

Pour ce faire, accédez à l'onglet Développeur et cliquez sur Visual Basic. Ensuite, allez dans Insérer et cliquez sur Module pour démarrer un nouveau module.

Écrivons maintenant du code, de sorte que la colonne B contienne les valeurs de la colonne D (nom) et de la colonne E.

La syntaxe est donnée comme suit:

Tout d'abord, utilisez une boucle «for» à partir de i = 4 puisque les valeurs commencent à partir de la 4 e ligne dans ce cas. La boucle continuera jusqu'à la fin de la dernière ligne de la colonne C. Ainsi, la variable I peut être utilisée comme numéro de ligne dans la boucle «for».

Entrez ensuite la valeur à attribuer à Cell (numéro_ligne, colonne B), qui peut être indiquée sous forme de Cellules (i, «B»). Valeur, sous forme Cell (numéro_ligne, colonne D) & «_» & Cellule (numéro_ligne, colonne E ).

Supposons que vous vouliez attribuer la cellule B5 = D5 & «_» & E5, vous pouvez simplement utiliser le code comme:

Cellules (5, «B»). Valeur = Cellules (5, «D»). Valeur & «_» & Cellules (5, «E»). Valeur

Maintenant, cherchons la valeur de recherche dans le tableau B5: E24. Vous devez d'abord entrer la valeur de recherche. Prenons la valeur (Nom et Département) de l'utilisateur. Pour faire ça,

  1. définissez trois variables, nom, service et valeur_recherche sous forme de chaîne.
  2. Prenez le nom saisi par l'utilisateur. Utilisez le code:

name = InputBox ("Entrez le nom de l'employé")

Le contenu de la zone de saisie «Entrez le…» sera affiché dans la boîte de dialogue lorsque vous exécuterez le code. La chaîne saisie dans l'invite sera affectée à la variable de nom.

  1. Prenez le département de l'utilisateur. Le peut être fait de la même manière que ci-dessus.

department = InputBox ("Entrez le service de l'employé")

  1. Attribuez le nom et le service avec «_» comme séparateur à la variable lookup_val en utilisant la syntaxe suivante:

lookup_val = nom & "_" & département

  1. Écrivez la syntaxe vlookup pour rechercher le lookup_val dans la plage B5: E24 le renvoie dans un salaire variable.

Initialisez le salaire variable:

Salaire faible aussi longtemps

Utilisez la fonction Vlookup pour trouver le lookup_val. Le tableau_table peut être donné sous la forme Range («B: F»), et le salaire est présent dans la 5 e colonne. La syntaxe suivante peut donc être utilisée:

salaire = Application.WorksheetFunction.VLookup (lookup_val, Range ("B: F"), 5, False)

  1. Pour imprimer le salaire dans une boîte de message, utilisez la syntaxe:

MsgBox (Le salaire de l'employé est "& salaire)

Maintenant, exécutez le code. Une boîte de dialogue apparaîtra dans la feuille de calcul dans laquelle vous pouvez entrer le nom. Après avoir entré le nom (Say Sashi) et cliquez sur OK.

Cela ouvrira une autre boîte dans laquelle vous pourrez entrer dans le département. Après être entré dans le département, dites IT.

Il imprimera le salaire de l'employé.

Si le Vlookup peut trouver un employé avec le nom et le département, il donnera une erreur. Supposons que vous donniez le nom «Vishnu» et le département «IT», cela renverra l'erreur d'exécution «1004».

Pour résoudre ce problème, vous pouvez spécifier dans le code, que sur ce type d'erreur, imprimez plutôt «Valeur non trouvée». Pour faire ça,

  1. Avant d'utiliser la syntaxe vlookup, utilisez le code suivant:

Sur le message d'erreur GoTo

Vérifier:

Le code de fin (de Check :) sera surveillé, et s'il reçoit une erreur, il ira à l'instruction «message»

  1. À la fin du code (Before End Sub), spécifiez que si le numéro d'erreur est 1004, imprimez dans la boîte de message «Données d'employé non présentes». Cela peut être fait en utilisant la syntaxe:

Message:

Si Err.Number = 1004 Alors

MsgBox ("Données des employés non présentes")

Fin si

Module 1:

Sub vlookup3 ()
For i = 4 To Cells (Rows.Count, "C"). End (xlUp) .Row
Cells (i, "B"). Value = Cells (i, "D"). Value & "_ »& Cellules (i,« E »). Value
Next iDim name As String
Dim department As String
Dim lookup_val As String
Dim salaire As Longname = InputBox (« Enter the name of the employee »)
department = InputBox (« Enter the department of l'employé »)
lookup_val = nom &« _ »& departmentOn Erreur GoTo
Vérification du message :
salaire = Application.WorksheetFunction.VLookup (lookup_val, Range (« B: F »), 5, False)
MsgBox (« Le salaire de l'employé est ”& Salaire) Message:
Si Err.Number = 1004 Then
MsgBox (“ Données de l'employé non présentes ”)
End IfEnd Sub

Choses à retenir à propos de VLookup dans Excel VBA

  • La fonction Vlookup peut être appelée dans Excel VBA à l'aide de WorksheetFunction.
  • La syntaxe de la fonction vlookup reste la même dans Excel VBA.
  • Lorsque le code VBA vlookup ne peut pas trouver la valeur lookup_value, il donnera une erreur 1004.
  • L'erreur dans la fonction vlookup peut être gérée à l'aide d'une instruction goto si elle renvoie une erreur.

Articles intéressants...