XL 2010 Recherche index match avec 2 critères

lolobala · 24 Octobre 2022

Bonjour à tous,

Après avoir lu plusieurs forums et effectué plusieurs tests divers, je m'en remets à vos connaissances.
Le code vba que je tente de faire doit retrouver dans le fichier Nouvelle_gamme, le numéro d'opération contenu dans la colonne I en fonction du code cherchée en colonne D et en colonne J.

J'ai donc essayé de passer par la formule Index equiv suivante :
=INDEX('C:\Users\balaguer\Downloads\[Nouvelle_Gamme.xlsx]Nouveau Cas emploi'!$A$4:$O$11834;EQUIV(1;('C:\Users\balaguer\Downloads\[Nouvelle_Gamme.xlsx]Nouveau Cas emploi'!$D$4:$D$11834=C2)*('C:\Users\balaguer\Downloads\[Nouvelle_Gamme.xlsx]Nouveau Cas emploi'!$J$4:$J$11834=L2);0);9)

La formule fonctionne dans excel, j'ai essayé de la retranscrire en VBA et en rendant le code "dynamique" (Le chemin peut changer, ainsi que le nom de l'onglet etc ...)
Mais lorsque je tente de lire le programme j'ai le message Erreur '1004' Impossible de lire la propriété Index de la classe WorksheetFunction

Auriez-vous une solution à mon problème ?
D'avance merci pour votre aide,

Cordialement
Laurent

sylvanu · 24 Octobre 2022

Bonjour Lolobala,
Il existe des instructions XL qui ne sont pas connus du VBA, dans ce cas il faut les faire précéder de WorksheetFunction. . Par exemple Index devient WorksheetFunction.Index.

WorksheetFunction.Index (Excel VBA)

Des informations sur la procédure Index de la classe WorksheetFunction

www.codevba.com

danielco · 24 Octobre 2022

Bonjour,

Ta plage tablorecherche, étant monocolonne, la ligne :
wf.Index(tablorecherche...
plante quand le second paramètre est plus grand que 1.

Daniel

job75 · 24 Octobre 2022

Bonsoir lolobala, sylvanu, danielco,

Téléchargez les fichiers joints dans le même dossier (le bureau).

Le plus simple est d'entrer des formules de liaison en colonne O, exécutez cette macro du fichier .xlsm :

VB:

Sub Affectation()
Dim form$, derlig&
form = "='" & ThisWorkbook.Path & "\[Nouvelle_Gamme.xlsx]Nouveau Cas emploi'!"
Application.ScreenUpdating = False
ThisWorkbook.Names.Add "Plage1", form & "$A$4:$O$11834" 'nom défini
ThisWorkbook.Names.Add "Plage2", form & "$D$4:$D$11834"
ThisWorkbook.Names.Add "Plage3", form & "$J$4:$J$11834"
[O2].FormulaArray = "=INDEX(Plage1,MATCH(1,(Plage2=C2)*(Plage3=L2),0),9)" 'formule matricielle
derlig = [A1].CurrentRegion.Rows.Count
If derlig > 2 Then [O2].AutoFill Range("O2:O" & derlig) 'remplit la colonne
'Range("O2:O" & derlig) = Range("O2:O" & derlig).Value 'facultatif, supprime les formules
End Sub

Pour que FormulaArray accepte la formule il faut que la macro définisse les noms Plage1 Plage2 Plage3.

Sinon la formule de liaison serait trop grande et ne serait pas acceptée.

A+

lolobala · 25 Octobre 2022

Bonjour,

Merci pour votre aide. La solution de job75 marche très bien.

Une dernière chose, le nom de la feuille "Nouveau cas d'emploi" va changer suivant les cas.
Est-il possible de le mettre dans une variable ? J'imagine que oui mais dans ce cas comment écrire la ligne :
'form = "='" & ThisWorkbook.Path & "\[Nouvelle_Gamme.xlsx]Nouveau Cas emploi'!"

Désolé mon niveau en vba est très limité
Merci pour votre retour

Laurent

job75 · 25 Octobre 2022

Bonjour lolobala,

Avec la variable (type String) "feuille" :

VB:

Dim feuille$, form$, derlig&
feuille = "Nouveau Cas emploi" 'modifiable
form = "='" & ThisWorkbook.Path & "\[Nouvelle_Gamme.xlsx]" & feuille & "'!"

A+

lolobala · 25 Octobre 2022

job75 à dit:
Bonjour lolobala,

Avec la variable (type String) "feuille" :

VB:

Dim feuille$, form$, derlig& feuille = "Nouveau Cas emploi" 'modifiable form = "='" & ThisWorkbook.Path & "\[Nouvelle_Gamme.xlsx]" & feuille & "'!"

A+

Bonjour Job75,

Je faisais fausse route la solution est beaucoup plus simple que ce que j'imaginais.
Merci beaucoup.

Bonne journée

job75 · 26 Octobre 2022

Bonjour lolobala, le forum,

La macro précédente s'exécute chez moi en 0,44 seconde.

Sur un très grand tableau la durée sera beaucoup plus grande.

Pour aller plus vite il faut utiliser des tableaux VBA et le Dictionary :

VB:

Sub Affectation()
Dim feuille$, form$, tablo, d As Object, i&
feuille = "Nouveau Cas emploi" 'modifiable
form = "='" & ThisWorkbook.Path & "\[Nouvelle_Gamme.xlsx]" & feuille & "'!"
Application.ScreenUpdating = False
[P4:P11834].FormulaArray = form & "I4:I11834" 'formule de liaison matricielle
[Q4:Q11834].FormulaArray = form & "D4:D11834" 'formule de liaison matricielle
[R4:R11834].FormulaArray = form & "J4:J11834" 'formule de liaison matricielle
tablo = [P4:R11834] 'matrice, plus rapide
Set d = CreateObject("Scripting.Dictionary")
For i = 1 To UBound(tablo)
    d(tablo(i, 2) & tablo(i, 3)) = tablo(i, 1) 'mémorise la 1ère colonne
Next
[P4:R11834].ClearContents 'RAZ
With ActiveSheet.UsedRange: End With 'actualise la barre de défilement verticale
With [A1].CurrentRegion.Resize(, 15)
    tablo = .Value 'matrice, plus rapide
    For i = 2 To UBound(tablo)
        tablo(i, 15) = d(tablo(i, 3) & tablo(i, 12)) 'récupération
    Next
    .Columns(15) = Application.Index(tablo, , 15) 'restitution
End With
End Sub

L'exécution se fait maintenant en 0,08 seconde.

A+

A+

lolobala · 28 Octobre 2022

Bonjour Job75,

Merci de l'intérêt que tu portes à mon problème.
Je ne maitrise pas suffisamment le vba pour comprendre cette solution, surtout les tableaux, ça me paraît tellement complexe.

Je vais tout de même essayer de l'adapter à mon cas.

Encore merci
Bonne journée

lolobala · 28 Octobre 2022

Bonjour le forum,

J'ai essayé d'adapter ce code ci-dessus à mon programme mais étrangement j'ai une erreur "13" incompatibilité de type sur la dernière ligne.
La seule chose qui a changé c'est le nombre de lignes....

J'ai constaté que "d" contenait 256Item alors que j'ai beaucoup plus de ligne que ça....
Est-ce que le problème peut venir de cela ? Une idée ?

Merci pour votre retour
Laurent

job75 · 28 Octobre 2022

Bonjour lolobala,

Joignez vos 2 fichiers.

A+

job75 · 29 Octobre 2022

Bonjour lolobala, le forum;

Votre image montre que le Dictionary contient 11814 keys (clés) uniques.

Il y a autant d'items mais seulement 256 sont uniques, rien d'anormal à ça.

Ce sont des textes de 10 chiffres, ce qui montre que la colonne I du fichier .xlsx est au format Texte.

En entrant ces textes en colonne O du fichier .xlsm la macro les convertis en entiers de type Long.

Le bug peut s'expliquer par le fait qu'au départ cette colonne contient des entiers de type Integer 10 20 30.

Essayez donc en effaçant d'abord la colonne O :

VB:

    .Columns(15).Clear 'RAZ
    .Columns(15) = Application.Index(tablo, , 15) 'restitution

Si ça ne suffit pas joignez vos 2 fichiers.

A+

lolobala · 2 Novembre 2022

Bonjour job75, le forum

Désolé j'étais en week-end et je ne pouvais pas répondre à votre message.
Je vais essayer votre solution et je vous tiens au courant.

Merci
Laurent

lolobala · 2 Novembre 2022

Je viens de tester la solution mais j'ai toujours le même message d'erreur.
Je vous mets en pièce jointe les 2 fichiers.
Par contre je suis obligé de supprimer des lignes dans le fichier test_nouvelle_BOM. En réalité j'ai plus de 150000 lignes et le fichier fait plus de 7 mo, donc trop volumineux pour être en pièce jointe...

Merci pour votre aide
Laurent

job75 · 2 Novembre 2022

Bonjour lolobala,

Chez moi sur Win 11 Excel 2019 la macro fonctionne très bien avec vos nouveaux fichiers.

A+

XL 2010 Recherche index match avec 2 critères

lolobala

XLDnaute Nouveau

Pièces jointes

sylvanu

XLDnaute Barbatruc

WorksheetFunction.Index (Excel VBA)

danielco

XLDnaute Accro

job75

XLDnaute Barbatruc

Pièces jointes

lolobala

XLDnaute Nouveau

job75

XLDnaute Barbatruc

lolobala

XLDnaute Nouveau

job75

XLDnaute Barbatruc

Pièces jointes

lolobala

XLDnaute Nouveau

lolobala

XLDnaute Nouveau

job75

XLDnaute Barbatruc

job75

XLDnaute Barbatruc

lolobala

XLDnaute Nouveau

lolobala

XLDnaute Nouveau

Pièces jointes

job75

XLDnaute Barbatruc

Discussions similaires