VBA - RechercheV Polyvalente
RECHERCHE POLYVALENTE
Pour les accros et les inconditionnels des fonctions d'Excel, avant de crier au scandale, lisez les quelques lignes suivantes.
La RechercheV d'origine a beaucoup de limites:
- La colonne de la donnée doit être avec un offset positif.
- S'arrête à la première occurrence trouvée et ne permet pas de trouver toutes les occurrences d'une colonne de recherche, même si ce n'est pas un doublon tel Dupont Pierre et Dupont Claude
La rechercheV polyvalente que je propose n'a pratiquement pas de limite.
- La liste ne doit pas être triée
- La colonne de donnée peut être avec un offset négatif.
- L'offset n'a pas de limite du moment qu'il se trouve dans une valeur de colonne valide.
- Les formules peuvent faire une recherche multiple.(principal intérêt)
- Possibilité de faire la recherche dans une plage colonne définie.
- La liste peut comporter des cellules vides
- La fonction peut-être utilisée depuis un autre classeur que le classeur courant
- Les cellules paramètres de la fonction peuvent être dans des feuilles différentes du même classeur ou même dans des classeurs différents
- Possibilité d'inclure cette fonction dans une formule plus complexe.
- Possibilité d'utiliser plusieurs recherches différentes dans la même colonne, il ne confond pas les occurrences
Description
Les fonctions doivent être dans une colonne. Chaque fonction cherche dans sa propre colonne en remontant jusqu’en ligne 1 une formule absolument identique à elle-même (afin de trouver le n° d’occurrence).
Il faut utiliser les formats absolus $ pour les cellules paramètres
Dans la colonne E ligne de 3 à 9 se trouve la formule
=RechercheVmulti($C$3;$B$3;$H$3)
C3 = la colonne où se trouve la liste de recherche, la recherche commence à la ligne 3, cela pourrait être une autre ligne et la recherche commencerait au N° de cette ligne.
B3 = la cellule critère
H3 = la colonne où la donnée va être saisie, peut être n'importe où sur la même ligne.
Dans la colonne F ligne de 3 à 7 se trouve la formule
=RechercheVmulti($C$3;$B$3;$A$3; 20)
C3 = la colonne où se trouve la liste de recherche, la recherche commence à la ligne 3, cela pourrait être une autre ligne et la recherche commencerait au N° de cette ligne.
B3 = la cellule critère
A3 = la colonne où la donnée va être saisie, peut se situer n'importe où sur la même ligne.(ici offset négatif)
20 = la recherche s'arrête à la ligne 20, même s'il y a d'autres concordances plus bas.
Ce qui permet de délimiter la plage de la colonne - Début de recherche dans (C)3 et fin dans (C)20
Ce paramètre est optionnel : si pas renseigné, fait la recherche sur toute la colonne renseignée.
Le code
Collez le code ci-dessous dans un MODULE STANDARD (pas dans un module de feuille)
Option Explicit 'RD =cellule où commencer la recherche 'RC = cellule critère 'RDT = cellule où chercher la donnée 'Ligne rechercher jusque ligne... (optional) si 0 cherche jusqu'au bout de la ligne Public Function RechercheVmulti(RD As Range, RC As Range, RDT As Range, _ Optional Ligne As Long = 0) Dim i As Integer, e As Integer, Txt As String Dim LigE As Long, ColE As Long 'où écrire résultats Dim Col As Integer Dim Lig As Long, Occ As Long Dim FeuilE As String, FeuilRD As String, FeuilRDT As String On Error GoTo sortie LigE = Application.Caller.Row ColE = Application.Caller.Column FeuilE = Application.Caller.Parent.Name Application.Volatile Lig = RD.Row ' Ligne où commencer la recherche Col = RD.Column 'Colonne où commencer la recherche FeuilRD = RD.Parent.Name FeuilRDT = RDT.Parent.Name If Ligne = 0 Then Ligne = Range(Sheets(FeuilRD).Cells(65536, Col), Sheets(FeuilRD).Cells(65536, Col)).End(xlUp).Row End If 'Recherche le numéro de l'occurrence à trouver For Occ = LigE - 1 To 1 Step -1 Txt = Sheets(FeuilE).Cells(Occ, ColE).Formula If Txt = Sheets(FeuilE).Cells(LigE, ColE).Formula Then e = e + 1 End If Next Occ For i = Lig To Ligne If Sheets(FeuilRD).Cells(i, Col) = RC Then If e <> 0 Then e = e - 1 Else RechercheVmulti = Sheets(FeuilRDT).Cells(i, RDT.Column) Exit Function End If End If Next i 'Si plus trouvé de concordance RechercheVmulti = "" Exit Function sortie: 'si erreur dans la formule, non détectée par Excel. RechercheVmulti = "#FAUTE!" End Function