vlookup et dénomination de cellules

[RichardS]

Mon VBA n'est pas très orthodoxe mais il fonctionne dans la plupart des cas. J'ai pour habitude de faire référence à mes cellules Excel en les nommant "cells(lig,col)" ce qui est bien pratique pour les faire évoluer.
Lorsque je fait appel à VLOOKUP dans une même feuille cela donne ce qui suit :
A B C D E
1 1 9 1 9
2 2 8
3 3 7

En A1, j'ai le critère et en B1, le résultat de la recherche
D1 à E3 est la matrice

'Sur la même feuille
Range("B1") = Application.VLookup(Range("A1"), Range("D1:E3"), 2, False) 'OK
Cells(1, 2) = Application.VLookup(Cells(1, 1), Range(Cells(1, 4), Cells(3, 5)), 2, False) 'OK

Si la matrice se trouve sur la feuille 2 exactement à la même place, cela ne fonctionne pas à tous les coups.

'Sur 2 feuilles différentes
Range("B1") = Application.VLookup(Range("A1"), Sheets("Feuil2").Range("D1:E3"), 2, False) 'OK
Cells(1, 2) = Application.VLookup(Cells(1, 1), Sheets("Feuil2").Range("D1:E3"), 2, False) 'OK
Cells(1, 2) = Application.VLookup(Cells(1, 1), Sheets("Feuil2").Range(Cells(1, 4), Cells(3, 5)), 2, False) 'erreur 1004
Cells(1, 2) = Application.VLookup(Cells(1, 1), Plage_recherche, 2, False) 'erreur 2042

'Plage_recherche =Feuil2!$D$1:$E$3
Pour contourner la difficulté, j'ai nommé la matrice "Plage-recherche" et cela ne fonctionne toujours pas.

POURQUOI ????
Comment faire pour que l'appellation "cells(lig,col)" fonctionne sur 2 feuilles différentes, sur 2 classeurs différents.

Merci pour votre réponse.

 

[RichardS]

Je ne comprend toujours pas pourquoi mais j'ai trouvé une solution que voici :
Set Feuil_Mat = Sheets("Feuil2")
Set Mat = Range(Feuil_Mat.columns(4), Feuil_Mat.columns(5))
Cells(1, 2) = Application.VLookup(Cells(1, 1), Mat, 2, False)

En fait le vlookup coince sur l'appel de la matrice en Feuil2 dans certaine configuration et je ne comprend pas pourquoi. Si quelqu'un pouvait m'expliquer ...
Cela me ferait énormément plaisir de ne pas mourir idiot.
Merci d'avance

 

[Staple1600]

Bonjour le fil, le forum, Bonjour RichardS

Nous, cela nous ferait énormément plaisir que tu penses à nous saluer 😉

(comme nous l'enseigne la charte du forum)

Pour ta question, un petit exemple illustratif pour comprendre le pourquoi du comment

Sub test()' à tester sur une feuille vierge
Dim f As Worksheet, a As Range, x, y
Set f = Sheets("Feuil2")
'en référençant toujours la feuille, plus d'erreur
f.Range("D1:E15").Formula = "=ROW()*COLUMN()*100"
Set a = f.Range(f.Cells(1, 4), f.Cells(Rows.Count, 5).End(3))
x = Application.VLookup(f.Cells(4, 4), a, 1, 0)
y = Application.VLookup(f.Cells(4, 4), a, 2, 0)
MsgBox x & " ->" & vbTab & y, vbInformation
End Sub

 

[RichardS]

Bonjour le fil, le forum, Bonjour RichardS

Nous, cela nous ferait énormément plaisir que tu penses à nous saluer 😉

(comme nous l'enseigne la charte du forum)

Pour ta question, un petit exemple illustratif pour comprendre le pourquoi du comment

Sub test()' à tester sur une feuille vierge
Dim f As Worksheet, a As Range, x, y
Set f = Sheets("Feuil2")
'en référençant toujours la feuille, plus d'erreur
f.Range("D1:E15").Formula = "=ROW()*COLUMN()*100"
Set a = f.Range(f.Cells(1, 4), f.Cells(Rows.Count, 5).End(3))
x = Application.VLookup(f.Cells(4, 4), a, 1, 0)
y = Application.VLookup(f.Cells(4, 4), a, 2, 0)
MsgBox x & " ->" & vbTab & y, vbInformation
End Sub

 

[RichardS]

Bonjour,

Oups pour le salut.
Désolé mais cette difficulté me prenait la tête au point d'en oublier d'être poli.

Ton exemple fonctionne parfaitement. Cela me donne en même temps une leçon de VBA.
J'en retire comme fondamental qu'il faut toujours déterminer la feuille et la cellule à sélectionner. Probablement aussi le classeur ?

Question concernant le END() ... dans Set a = f.Range(f.Cells(1, 4), f.Cells(Rows.Count, 5).End(3))
J'ai compris que c'était l'équivalent des flèches "début" et "fin" mais l'argument 3 à quoi sert-il et je suppose que 1, 2, etc sont également des arguments qui ont leur signification mais je n'arrive pas à les trouver sur le net.
Peux-tu me l'expliquer ou me dire où je pourrais trouver des compléments d'informations.

Merci.
Richard

 

[Staple1600]

Bonsoir le fil, le forum

Set a = f.Range(f.Cells(1, 4), f.Cells(Rows.Count, 5).End(3))
est la version "raccourcie" de
Set a = f.Range(f.Cells(1, 4), f.Cells(Rows.Count, 5).End(xlUp))

(voir dans l'aide VBA d'Excel)

 

[RichardS]

Bonjour à tous,
Toujours dans mes vlookup.
Je désire effectuer des recherches par vlookup sur des multi dossiers et multi feuilles. j'explique :
Je suis sur un dossier "Menu.xlsm" dans la feuille "Feuil1" et je désire avoir le résultat de ma recherche sur la cellule "A1".
Mon critère de recherche est sur le dossier "Classeur2.xlsx", feuille "Feuil2", cellule "A1".
Ma matrice de recherche est dans le dossier "Classeur3.xlsx", feuille "Feuil1", colonne 1 et colonne 2
L'information à retourner est dans la colonne 2.
Suivant l'exemple de Staple 1600 (que je remercie au passage) j'ai écrit :

Workbooks("Menu.xlsm").Sheets("Feuil1").Cells(1, 1) = Application.VLookup(Workbooks("Classeur2.xlsx").Sheets("Feuil2").Cells(1, 1), _
Range(Workbooks("Classeur3.xlsx").Sheets("Feuil1").Columns(1), _
Workbooks("Classeur3.xlsx").Sheets("Feuil1").Columns(2)), 2, False)

Et ça marche. Mais c'est #~[ment lourd. Toujours suivant le même exemple, pour simplifier, j'ai extrapolé ce qui suit :

Dim DM As Workbook, D2 As Workbook, D3 As Workbook
Dim F1 As Worksheet, F2 As Worksheet

Set DM = Workbooks("Menu.xlsm")
Set D2 = Workbooks("Classeur2.xlsx")
Set D3 = Workbooks("Classeur3.xlsx")
Set F1 = Sheets("Feuil1")
Set F2 = Sheets("Feuil2")

DM.F1.Cells(1, 1) = Application.VLookup(D2.F2.Cells(1, 1), _
Range(D3.F1.Columns(1), D3.F1.Columns(2)), 2, False)

ET CA PLANTE ... pourquoi ???

J'y suis presque, enfin je crois et c'est rageant.
Si quelqu'un pouvait m'éclairer ... un grand merci d'avance.

 

[Staple1600]

Bonsoir le fil, le forum

Normalement il faudrait mieux écrire
Dim DM As Workbook, D2 As Workbook, D3 As Workbook
Dim F1 As Worksheet, F2 As Worksheet

Set DM = Workbooks("Menu.xlsm")
Set D2 = Workbooks("Classeur2.xlsx")
Set D3 = Workbooks("Classeur3.xlsx")
Set F1 = DM.Sheets("Feuil1")
Set F2 = D2.Sheets("Feuil2")
F1.Cells(1, 1) = Application.VLookup(F2.Cells(1, 1), _
Range(D3.Sheets(1).Columns(1), D3.Sheets(1).Columns(2)), 2, False)

Je te laisse tester.

 

[RichardS]

Bonjour,

Ouf, ça fonctionne. Un grand merci.
On peut même réduire encore en créant par exemple un :
Set F3=D3.Sheets("Feuil1")
F1.Cells(1, 1) = Application.VLookup(F2.Cells(1, 1), _
Range(F3.Columns(1), F3.Columns(2)), 2, False)

J'en déduis qu'une instruction (au moins de type application) ne supporte pas de références multiples du genre D3.F1 et qu'il faut la lui présenter en condenser pour n'avoir qu'un référent.

Cordialement
RichardS

 

[Staple1600]

Bonjour le fil, le forum

On peut déduire de tes déductions ceci également 😉
NB: A tester sur un classeur vierge (avec deux feuilles)

Sub a()
Dim F1 As Worksheet, F2 As Worksheet
Set F1 = Sheets(1): Set F2 = Sheets(2)
F2.Range("A2:B5").Formula = "=ROW()*COLUMN()"
F1.[A2] = 4
MsgBox Application.VLookup(F1.[A2], F2.Columns("A:B"), 2, 0)
End Sub

 

[RichardS]

Bonjour,

Le principe étant acquis, il y a plusieurs variations possibles
Merci pour ta patience et tes infos

RichardS

 

[Staple1600]

Bonsoir le fil, le forum

@RichardS
C'était juste pour t'indiquer que

 F1.[A1]=Application.VLookup(F2.[A1], F3.Columns("A:B"), 2, 0)

est plus simple à écrire que

F1.Cells(1, 1) = Application.VLookup(F2.Cells(1, 1), _
Range(F3.Columns(1), F3.Columns(2)), 2, False)

 

[RichardS]

Bonsoir,

Tu as raison, c'est plus simple mais A1 est une constante alors que cells(1,1) ou plus justement cells(L,C) est une variable. C'est plus souple.

Joyeux Noël et bonne fêtes de fin d'année
RichardS