Fonction VBA Excel pour rechercher dans plusieurs colonnes

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Chercher dans plusieurs colonnes

C'est une nouvelle fonction VBA Excel que nous proposons de bâtir ici. Sa vocation est d'extirper une valeur correspondant à une donnée cherchée, lorsque ni la ligne ni la colonne de recherche ne sont connues à l'avance.



Sur l'exemple illustré par la capture, des équipes constituées de quatre membres chacune, ont obtenu des scores. Sur la droite, l'utilisateur souhaite connaître le résultat de l'un des participants. Il déploie donc une liste déroulante pour désigner son nom. Mais il ne connaît pas son équipe d'appartenance, donc la colonne dans laquelle il est enregistré. Qu'à cela ne tienne ! Cette fonction VBA Excel se charge de fouiller toutes les lignes et les colonnes du tableau qui lui est passé en paramètre, pour livrer le score du participant et de son équipe, quelques cellules plus bas.

Classeur Excel à télécharger
Pour construire cette nouvelle fonction VBA Excel, nous suggérons d'appuyer les travaux sur ce tableau des équipes.

• Télécharger le fichier chercher-dans-plusieurs-colonnes.xlsm en cliquant sur ce lien,
• Cliquer droit sur le fichier réceptionné,
• En bas du menu contextuel, choisir la commande Propriétés,
• Dans la boîte de dialogue, cocher la case Débloquer et valider par Ok,
• Puis, double cliquer sur le fichier pour l'ouvrir dans Excel,

Nous trouvons bien le tableau des équipes entre les colonnes B et G et entre les lignes 3 et 13. C'est en cellule I4 que l'utilisateur peut désigner l'un des ces membres avec une liste déroulante pour obtenir le score obtenu par son équipe en cellule I7. Mais la fonction VBA capable de cette prouesse n'existe pas encore.

La fonction et ses paramètres
La fonction que nous devons créer a besoin de trois renseignements. Il s'agit premièrement de la personne cherchée. Il s'agit ensuite du tableau de recherche et de la colonne des scores pour fournir le résultat correspondant en retour.

• Réaliser le raccourci clavier ALT + F11 pour basculer dans l'éditeur VBA Excel,
• Dans l'explorateur de projet sur la gauche, double cliquer sur l'élément Module1,
• Dans la feuille vierge de code au centre de l'écran, créer la fonction chercheDansTab,

Function chercheDansTab(texteCh As String, tabCh As Range, colRes As Range)
Application.Volatile

End Function

Le premier paramètre est déclaré comme un texte (As String) pour recevoir le nom de la personne cherchée. Les deux suivants sont déclarés comme des plages de cellules (As Range) pour accueillir respectivement le tableau de recherche et la colonne des scores. La méthode Volatile de l'objet Application est utilisée pour forcer le recalcul de la fonction à chaque actualisation de données. Elle est utile seulement en fonction de la version d'Excel.

Le tableau de variables
Pour parcourir toutes les données du tableau de recherche, nous avons besoin d'un tableau de variables représentant toutes ses informations. C'est ainsi que nous pourrons les parcourir dans une double boucle, sur les lignes comme sur les colonnes.

• A la suite du code, ajouter les déclarations et l'affectation suivantes :

...
Dim leTab As Variant
Dim i As Byte: Dim j As Byte

leTab = tabCh
...

Nous déclarons l'objet leTab que nous ne typons pas dans un premier temps (As Variant). Il prend implicitement son type par l'affectation qui suit (leTab = tabCh) afin de représenter le tableau de recherche sous forme de tableau de variables. Les variables i et j typées comme des entiers courts, seront utilisées comme des compteurs de boucles. Nous le comprendrons.

Parcourir toutes les cellules du tableau
Le tableau de variables est désormais fait de colonnes et de lignes. Pour trouver la personne cherchée, nous devons parcourir toutes ses lignes et pour chacune, toutes ses colonnes. En d'autres termes, nous devons engager une double boucle.

• A la suite du code VBA, créer la double boucle suivante :

...
For i = LBound(leTab) To UBound(leTab)
For j = LBound(leTab, 2) To UBound(leTab, 2)

Next j
Next i
...

Les fonctions LBound et UBound permettent respectivement de renseigner sur la borne inférieure et sur la borne supérieure d'un tableau de variables. C'est ainsi que nous engageons dans un premier temps une boucle parcourant toutes les lignes de ce tableau. Dans un second temps et à l'intérieur de la première boucle, nous agissons sur la deuxième dimension de ce tableau avec les mêmes fonctions pour parcourir toutes ses colonnes, pour chacune de ses lignes.

Tester la valeur cherchée
Désormais, à chaque passage dans cette double boucle, nous devons tester si la valeur cherchée, passée en premier argument de la fonction, correspond à la valeur placée dans cette case du tableau de recherche (i et j).

• A l'intérieur de la double boucle, ajouter l'instruction VBA suivante :

...
If tabCh(i, j) = texteCh Then chercheDansTab = colRes(i): Exit Function
...

Si la valeur du tableau pour la ligne et la colonne en cours (tabCh(i, j)) est égale à la donnée cherchée (texteCh), alors (Then), nous retournons le score correspondant sur la même ligne, issue du tableau de retour (colRes(i)). Pour cela, nous stockons son résultat dans le nom même de la fonction. Puis, nous mettons fin à l'exécution de la fonction (Exit Function), puisque l'information a été trouvée.

Trouver quelle que soit la colonne
Il ne nous reste plus qu'à tester cette nouvelle fonction, au code VBA relativement simple.

• Enregistrer les modifications (CTRL + S) et basculer sur la feuille Excel (ALT + Tab),
• Sélectionner la case du score à trouver en cliquant sur la cellule I7,

Il est à noter que les deux tableaux sont reconnus par des plages nommées. Le tableau de recherche se nomme membres. La colonne des scores est intitulée scores. Vous pouvez le constater en déployant la zone Nom en haut à gauche de la feuille Excel.

• Taper le symbole égal (=) pour initier la syntaxe de la formule,
• Inscrire le nom de la nouvelle fonction suivi d'une parenthèse, soit : chercheDansTab(,
• Cliquer sur la cellule I4 de la liste déroulante pour désigner la personne cherchée,
• Taper un point-virgule (;) pour passer dans l'argument du tableau de recherche,
• Désigner ce dernier par son nom, soit : membres,
• Taper un point-virgule (;) pour passer dans l'argument de la colonne de retour,
• Désigner cette dernière par son nom, soit : scores,
• Fermer la parenthèse de la fonction chercheDansTab,
• Puis, valider la formule par la touche Entrée du clavier,

Comme vous pouvez l'apprécier, le score du membre qui a été choisi, surgit aussitôt. Bien entendu, si vous changez de personne avec la liste déroulante en cellule I4, vous réalisez une nouvelle extraction en parfaite cohérence.



Vous notez de même l'apparition d'une mise en forme conditionnelle prédéfinie. Elle fait surgir en vert la donnée cherchée et son information numérique attachée, pour un meilleur croisement visuel des résultats.

Enfin et vous le savez, si vous souhaitez que cette nouvelle fonction soit disponible pour toutes les utilisations à venir, vous devez l'enregistrer dans la bibliothèque d'Excel.