1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40
| Function RECHERCHEV2(matrice As Range, valeur As Variant, positionsearch As Integer, sens As Variant) As Variant
Dim zone As Variant
zone = matrice.Address
celbas = Right(zone, (Len(zone) - Application.Search(":", zone)))
celhaut = Left(zone, Application.Search(":", zone) - 1)
colonnedroite = Range(Right(zone, (Len(zone) - Application.Search(":", zone)))).Column
colonnegauche = Range(Left(zone, Application.Search(":", zone) - 1)).Column
Dim i As Long, t As String
For i = 1 To Len(celbas)
t = Mid$(celbas, i, 1)
If Asc(t) > 47 And Asc(t) < 58 Then lignebas = lignebas & t
Next
For i = 1 To Len(celhaut)
t = Mid$(celhaut, i, 1)
If Asc(t) > 47 And Asc(t) < 58 Then lignehaut = lignehaut & t
Next
If sens = -1 Then
colonnetarget = Range(celbas).Offset(0, -(positionsearch - 1)).Column
Else: colonnetarget = Range(celhaut).Offset(0, (positionsearch - 1)).Column
End If
adressegauche = Cells(lignehaut, colonnegauche).Address & ":" & Cells(lignebas, colonnegauche).Address
adressedroite = Cells(lignehaut, colonnedroite).Address & ":" & Cells(lignebas, colonnedroite).Address
Adressetarget = Cells(lignehaut, colonnetarget).Address & ":" & Cells(lignebas, colonnetarget).Address
If sens = -1 Then
Position_1ère_colonne = Range(adressedroite)
Else: Position_1ère_colonne = Range(adressegauche)
End If
RECHERCHEV2 = Application.Index(Range(Adressetarget), Application.Match(valeur, Position_1ère_colonne, 0))
End Function |
Partager