Vlookup vers la gauche : OK formule "à la main"(index, equiv ...) mais plante en VBA

[Sebast]

Bonsoir à toutes et à tous,

je cherche à effectuer un recherchev vers la gauche car la stucture de mes données ne me permet pas d'inverser l'ordre des colonnes (ce qui aurait d'emblée réglé le problème … mais hélas c'est comme ça !)

J'arrive à écrire une formule qui marche mais pas à l'intégrer dans du code

Je veux passer par des ranges, pour une question de lisibilité et surtout parce que les zones sont appelées à bouger (ajouts, retraits)
Dans ma sub, je vois que ma formule n'est pas convenable car elle apparaît en rouge, mais je ne comprends pas pourquoi …

Qui pourrait me dire ce qui cloche ?

D'avance, merci beaucoup

Sub recherche_à_gauche()
With Sheets("Table")
        Dim i As Long
        Dim CodeMatricule As Variant
        Dim ZoneMatricule As Range
        Dim dernlign As Long
        
        ' 1 Nommer le range avec les matricules
         dernlign = .Range("A" & Rows.Count).End(xlUp).Row
         Set ZoneMatricule = .Range("A2:A" & dernlign)
        
        ' 2 Nommer le range avec les noms
        Dim ZonePrenom As Range
         Set ZonePrenom = .Range("B2:B" & dernlign)
        
        ' 3 fonction index/equiv
        For i = 2 To dernlign
                CodeMatricule=INDEX(ZoneMatricule;EQUIV(.cells(i,2);ZonePrenom;0))  'formule en rouge ...
                .Cells(i, 10).Value = CodeMatricule
        Next i
End With
End Sub

 

[Modeste geedee]

Re : Vlookup vers la gauche : OK formule "à la main"(index, equiv ...) mais plante en

Bonsour®

VBA parle US !!!!
CodeMatricule=INDEX(ZoneMatricule,Match(.cells(i,2),ZonePrenom,0))

 

[Sebast]

Re : Vlookup vers la gauche : OK formule "à la main"(index, equiv ...) mais plante en

Bonjour Modeste geedee,

bien vu, je pouvais toujours essayer !

Cependant ça plante encore avec le message

"erreur de compilation ..."

Quand j'adapte ma formule (donc match remplace equiv) ou quand je la remplace carrément par la tienne, même résultat.
Moi j'utilise les ";" mais toi la ","
Mais ça ne change rien, même message d'erreur

Quelqu'un a-t-il une idée où ça coince ?

Merci d'avance

 

[Modeste geedee]

Re : Vlookup vers la gauche : OK formule "à la main"(index, equiv ...) mais plante en

Bonsour®
il s'agit de fonction de feuille de calcul pas de fonction VBA

For i = 2 To dernlign
    CodeMatricule = WorksheetFunction.Index(ZoneMatricule, WorksheetFunction.Match(.Cells(i, 2), ZonePrenom, 0)) 'formule en rouge ...
    .Cells(i, 10).Value = CodeMatricule
Next i

 

[Sebast]

Re : Vlookup vers la gauche : OK formule "à la main"(index, equiv ...) mais plante en

Re,

tu as raison, j'avais bien essayé entre-temps de bidouiller avec application.worksheetFunction mais ne l'avais fait que dans le premier membre de la fonction (donc pas pour MATCH) ...

Encore merci pour ta clairvoyance !

PS : il n'y a vraiment pas de fonction intégrée réalisant ça ? Même avec les dernières version excel ?

 

[Modeste geedee]

Re : Vlookup vers la gauche : OK formule "à la main"(index, equiv ...) mais plante en

Bonsour®

PS : il n'y a vraiment pas de fonction intégrée réalisant ça ? Même avec les dernières version excel ?

les zonematricule et zonePrénom ne sont pas disjointes : unicité Prénom/matricule

je ne comprends pas la nécessité d'une recherche pour alimenter une plage décalée de même dimension ??
un simple offset devrait suffire ...
ou même :
For i = 2 To dernlign
.Cells(i, 10).Value = .cells(i,1).value
' .Cells(i, 10).Value = .cells(i,2).offset(0,-1).value
Next i

 

[Sebast]

Re : Vlookup vers la gauche : OK formule "à la main"(index, equiv ...) mais plante en

Re,

merci de t'être penché sur mon cas.

En fait j'avais mal formulé mon problème dès le départ.
En grandeur réelle, j'ai une table de référence (matricule et nom) et une feuille de données où les noms sont répartis de façon aléatoire (en tout cas dans l'ordre figurant dans mon exemple).
Il y a environ 15 000 lignes avec le matricule et j'ai besoin de connaître le nom correspondant.

Je pense que le INDEX/MATCH que tu m'as aidé à affiner est donc la meilleure solution, non ?
Le code avec offset ne peut pas s'appliquer mais je reconnais, à ma grande honte, que vous ne pouviez pas deviner ...


Encore désolé pour ma formulation approximative et encore merci pour l'aide apportée