VLOOKUP VBA probleme

Version imprimable

Bonjour a tuos
j'ai un problème sur VBA, j'aurai aimé savoir si vous pouviez m'aider.

je suis entrain d'automatiser un reporting, et sur 1 colonne, je fais un recherche v qui va me chercher des données sur un fichier externe.
cette manoeuvre, je la fais 5 fois, je l'ai fait via l automatisation de macro.

Code:

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
    Range("BS3").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-69],'[Extract - EUR - Tuesday, January 15, 2013.xlsx]ODS'!C3:C52,50,FALSE)"
    Range("BS3").Select
    Selection.FillDown
    ActiveWindow.SmallScroll Down:=-200
 
    Range("BT3").Select
    ActiveCell.FormulaR1C1 = _
        "=(VLOOKUP(RC[-70],'[Extract - EUR - Tuesday, January 15, 2013.xlsx]ODS'!C3:C55,53,FALSE)/RC[-1])"
    Range("BT3").Select
    Selection.FillDown
    ActiveWindow.SmallScroll Down:=-200
 
    Range("BU3").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-71],'[Extract - EUR - Tuesday, January 15, 2013.xlsx]ODS'!C3:C32,30,FALSE)"
    Range("BU3").Select
    Selection.FillDown
    ActiveWindow.SmallScroll Down:=-200
 
    Range("U3").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-6]=""EUR"",RC[-1],RC[-1]/VLOOKUP(RC[-6],'[Extract - EUR - Tuesday, January 15, 2013.xlsx]Conversion Rate'!C1:C2,2,FALSE))"
    Range("U3").Select
    Selection.FillDown
    ActiveWindow.SmallScroll Down:=-200

je ne sais pas si c est le bon, mais j arrive pas à le compiler.

Merci enormement par avance pour votre aide.

Cordialement.

17/01/2013, 17h14
Kimy_Ire

Pourquoi utilises-tu FormulaR1C1 alors qu'il existe en VBA la fonction VLookup :

Code:

.Range("BS3") = WorksheetFunction.VLookup(...)

Je n'aime pas trop manipuler "Formula", mais à ta guise si tu le souhaites.
Malgré cela, avec FormulaR1C1 tu peux y arrivé, d'où ma question, que places-tu exactement comme paramètre ? A quoi veux-tu aboutir ?

J'ai du mal avec le "RC[-69]" par exemple. Je pense qu'il y a plus simple. Tu veux pointer 69 colonnes sur la gauche de le celulle active, c'est ça ?

"[Extract - EUR - Tuesday, January 15, 2013.xlsx]ODS'!C3:C52" => ton fichier "Extract - EUR - Tuesday, January 15, 2013.xlsx" est-il ouvert lors de l'éxecution de ta macro ?

En bref, tu veux comparer ce qu'il y a dans une cellule 69 colonnes à gauche de BS3 avec le tableau qui se trouve dans ton fichier Extract - EUR - Tuesday, January 15, 2013.xlsx sur la range "C3:C52" et placer en BS3 la valeur de la 50ème colonne de ce tableau ?
17/01/2013, 17h23
bentor22

bonjour Limy-Ire et merci de ton aide precieuse.

"[Extract - EUR - Tuesday, January 15, 2013.xlsx]ODS'!C3:C52" => mon fichier "Extract - EUR - Tuesday, January 15, 2013.xlsx" onglet ODS.
il n'est pas forcement ouvert.
sinon je sais que je peux le faire avec nom=ActiveWorkbook.Name pour aller rechercher le fichier.

Citation:

En bref, tu veux comparer ce qu'il y a dans une cellule 69 colonnes à gauche de BS3 avec le tableau qui se trouve dans ton fichier Extract - EUR - Tuesday, January 15, 2013.xlsx sur la range "C3:C52" et placer en BS3 la valeur de la 50ème colonne de ce tableau ?

Excellente analyse, c'est exactement ce que je cherche a faire.

MERCI ENCORE UNE FOIS
17/01/2013, 17h26
Kimy_Ire

Ensuite, il y a un problème avec "FillDown". En effet, cette fonction recopie la première ligne de ta Range sur toute la selection.

Puisque ta range n'est qu'une seule case, il y a un problème. ^^

Dernier point : "ActiveWindow.SmallScroll Down:=-200". Tu fais un scroll down négatif ? Donc un scroll up en bref ? =D
17/01/2013, 17h33
bentor22

au fait, j aimerai à la fin faire un "ActiveWindow.SmallScroll Down:=-200".
pour donner à chaque cellule de la colonne sa valeur associé.

et c est exact ce que tu dis pour le filldown.

Saurais tu comment arranger la macro stp?

Code:

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 Sub fill() 'Place après Filemane le nom COMPLET (avec le chemin) de ton classeur Workbooks.Open Filename:="C:\Users\...\Desktop\Extract - EUR - Tuesday, January 15, 2013.xlsx" 'Pour répondre à te besoin que je n'ai pas saisi n = 50 'Avec ton classeur d'origine (dont je n'ai pas le nom), avec ta feuille d'origine (dont je n'ai pas le nom, non plus) With Workbooks("test").Worksheets("test") 'Tu te places en BS3 'puis tu réalises ta fonction VLookup 'Premier arg : ta range BS3 avec un offset de 69 colonnes - tu peux également placer en "dur" la range souhaitée soit B3 si je calcul bien 'Deuxième arg : avec ton autre classeur, dans l'onglet ODS, sur la bonne Range => explication en dessous 'Troisième arg : ta colonne - 50 .Range("BS3") = WorksheetFunction.VLookup(.Range("BS3").Offset(0, -69), Workbooks("Extract - EUR - Tuesday, January 15, 2013.xlsx").Worksheets("ODS").Range("A2:BV19"), 50, False) 'Et là, le "FillDown" comme je ferais avec n = le nombre de fois que tu veux recopier ta cellule For i = 1 To n .Range("BS3").Offset(i, 0) = .Range("BS3") Next i End With 'Et tu fermes ton classeur sans sauvegarder Workbooks("Extract - EUR - Tuesday, January 15, 2013").Close SaveChanges:=False End Sub
A toi de faire le reste, j'ai fais sur ton premier élément, j'ai testé ça fonctionne. Si ca ne fonctionne pas de ton côté c'est qu'un argument a dû mal être placé.

Je te donne un exemple : tu as une range "C3:C52" - c'est-à-dire un tableau avec 1 seule colonne... Comment veux-tu aller chercher là 50ème ?

Bref tiens moi au courant si mes explications ne sont pas suffisament claire ! :ccool:

EDIT : ajoute le Scroll, je ne l'ai pas implémenté.

Citation:

au fait, j aimerai à la fin faire un "ActiveWindow.SmallScroll Down:=-200".
pour donner à chaque cellule de la colonne sa valeur associé.

Ou un Scroll up, c'est ce que je te dis : ActiveWindow.SmallScroll Up:=200.
Mais as-tu bien compris ce qu'un un scroll ? => "pour donner à chaque cellule de la colonne sa valeur associé" => Qu'est-ce que cela signifie ?

Salut Kimy,

voici le code que j ai tapé:
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 Sub MTLT() ' ' MTLT Macro ' Macro enregistrée le 18/01/2013 par moi ' ' Workbooks.Open Filename:="S:\Syndication\Loan Syndication\SLOT\LoanTracker\Extract - EUR - Wednesday, January 16, 2013.xlsx" n = 50 With Workbooks("syndications CR+LCL consolidé 2013 base avec macro 2.xls").Worksheets("Syndications intragroupe") .Range("BS3") = WorksheetFunction.VLookup(.Range("BS3").Offset(0, -69), Workbooks("Extract - EUR - Wednesday, January 16, 2013.xlsx").Worksheets("ODS").Range("A2:BV19"), 50, False) For i = 1 To n .Range("BS3").Offset(i, 0) = .Range("BS3") Next i End With ActiveWindow.SmallScroll down:=-200 Workbooks("Extract - EUR - Wednesday, January 16, 2013.xlsx").Close SaveChanges:=False End Sub
ils me mettent Erreur d execution 1004: Impossible de lire la propriété vlookup de la classe Worksheet Function.

Citation:

Je te donne un exemple : tu as une range "C3:C52" - c'est-à-dire un tableau avec 1 seule colonne... Comment veux-tu aller chercher là 50ème ?

C est un tableau avec 50 colonnes, donc je vais rechercher la 50e.
car le nombre de colonne de 3 à 52 est (52-3+1=50).

D'une maniere générale, le fichier d ou je vais extraire mes données est actualisé tous les jours, du coup pourrais je changer ma macro de facon à ce qu il m'affiche une fenetre parcourir, et qu 'a partie de la, je selectionne moi meme le fichier d ou extraire mes données.Sachant que la structure de l interieur du fichier ne change pas.

Derniere question: que veux dire pour toi, à l interieur de Vlookup Range("A2:BV19).

Merci enormement d avance pour ton aide et tes reponses.

I-
Code:

1 2 3 4 5 6 7 8 9 10 11 Dim str As String 'Open the directory "C:\Users\...\Desktop" to be able to find the correct report With Application.FileDialog(msoFileDialogFilePicker) .InitialFileName = "C:\Users\...\Desktop" .Show 'and select the report on which you click If .SelectedItems.count > 0 Then Workbooks.Open (.SelectedItems(1)) End With str = ActiveWorkbook.Name
Cette partie de code ouvre "C:\Users\...\Desktop" et te permet donc d'aller chercher le dossier que tu souhaites.
Je rentre dans "str" le nom de ce classeur que tu devras donc placer dans :
1) Workbooks(str).Worksheets("ODS").Range("A2:BV19")
2) Workbooks(str).Close SaveChanges:=False

II-
Alors... restons calme... C3:C52... Qu'est-ce que c'est ? La réponse, c'est une range de 1 colonne et de 50 lignes. Bref, je ne vais pas plus loin ! :ccool:

III-
Tiens moi au courant ! :ccool:

Bonjour Kimy et merci pour ta réponse.

Voici mon code:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 Dim str As String
 
'Open the directory "C:\Users\...\Desktop" to be able to find the correct report
With Application.FileDialog(msoFileDialogFilePicker)
    .InitialFileName = "S:\Syndication\Loan Syndication"
    .Show
    'and select the report on which you click
    If .SelectedItems.Count > 0 Then Workbooks.Open (.SelectedItems(1))
End With
 
str = ActiveWorkbook.Name
    n = 50
    With Workbooks(str).Worksheets("ODS")
    .Range("BS3") = WorksheetFunction.VLookup(.Range("BS3").Offset(0, -69), Workbooks(str).Worksheets("ODS").Range("C2:AZ29701"), 50, False)
    For i = 1 To n
       .Range("BS3").Offset(i, 0) = .Range("BS3")
       Next i
       End With
    ActiveWindow.SmallScroll down:=-200
 
    Workbooks(str).Close SaveChanges:=False

En compilant, ils me mettent toujours erreur d'execution 1004.
Impossible de lire la fonction Vlookup de la classe WorksheetFunction.

Donc du coup, il ne peut pas compiler.

Saurais tu ou se cache l'erreur.

Encore une fois, merci pour ton aide

Code:

1
2
3
4
5
If WorksheetFunction.CountIf(Workbooks(str).Worksheets("ODS").Range("C4:C29701"), .Range("BS3").Offset(0, -69)) > 0 Then
    .Range("BS3") = WorksheetFunction.VLookup(.Range("BS3").Offset(0, -69), Workbooks(str).Worksheets("ODS").Range("C2:AZ29701"), 50, False)
Else
    .Range("BS3") = "Non trouvé"
End If

A chaque VLookup, met ceci stp, et dis moi ce que tu obtiens. :ccool:

Bonjour,

avec les modification, j ai

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
  Dim str As String
 
'Open the directory "C:\Users\...\Desktop" to be able to find the correct report
With Application.FileDialog(msoFileDialogFilePicker)
    .InitialFileName = "S:\Syndication\Loan Syndication"
    .Show
    'and select the report on which you click
    If .SelectedItems.Count > 0 Then Workbooks.Open (.SelectedItems(1))
End With
 
str = ActiveWorkbook.Name
    n = 50
    If WorksheetFunction.CountIf(Workbooks(str).Worksheets("ODS").Range("C4:C29701"), .Range("BS3").Offset(0, -69)) > 0 Then
    .Range("BS3") = WorksheetFunction.VLookup(.Range("BS3").Offset(0, -69), Workbooks(str).Worksheets("ODS").Range("C2:AZ29701"), 50, False)
    Else
    .Range("BS3") = "Non trouvé"
    End If
    For i = 1 To n
       .Range("BS3").Offset(i, 0) = .Range("BS3")
       Next i
       End With
    ActiveWindow.SmallScroll down:=-200
       
    Workbooks(str).Close SaveChanges:=False

J'ai une erreur avec le code que j ai mis en gras.

Attends... je n'avais pas vu mais tu as changé des choses, et pas comme je l'avais spécifié :

Code:

1
2
3
4
5
6
With Workbooks(str).Worksheets("ODS")
    .Range("BS3") = WorksheetFunction.VLookup(.Range("BS3").Offset(0, -69), Workbooks(str).Worksheets("ODS").Range("C2:AZ29701"), 50, False)
    For i = 1 To n
        .Range("BS3").Offset(i, 0) = .Range("BS3")
    Next i
End With

...

Code:

1
2
3
4
5
6
With Workbooks("syndications CR+LCL consolidé 2013  base avec macro 2.xls").Worksheets("Syndications intragroupe")
    .Range("BS3") = WorksheetFunction.VLookup(.Range("BS3").Offset(0, -69), Workbooks(str).Worksheets("ODS").Range("C2:AZ29701"), 50, False)
    For i = 1 To n
        .Range("BS3").Offset(i, 0) = .Range("BS3")
    Next i
End With

Tu dois mettre dans le "With", le nom du Workbook dans lequel tu envoies tes données et non pas celui que tu ouvres et dans lequel tu recherches...

Code:

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
Sub MTLT()
'
' MTLT Macro
' Macro enregistrée le 18/01/2013 par moi
'
 
Dim str As String
 
'Open the directory "C:\Users\...\Desktop" to be able to find the correct report
With Application.FileDialog(msoFileDialogFilePicker)
    .InitialFileName = "S:\Syndication\Loan Syndication"
    .Show
    'and select the report on which you click
    If .SelectedItems.Count > 0 Then Workbooks.Open (.SelectedItems(1))
   End With
 
   str = ActiveWorkbook.Name
    n = 50
    If WorksheetFunction.CountIf(Workbooks(str).Worksheets("ODS").Range("C4:C29701"), .Range("BS3").Offset(0, -69)) > 0 Then
    .Range("BS3") = WorksheetFunction.VLookup(.Range("BS3").Offset(0, -69), Workbooks(str).Worksheets("ODS").Range("C2:AZ29701"), 50, False)
    Else
    .Range("BS3") = "Non trouvé"
    End If
    With Workbooks("syndications CR+LCL consolidé 2013  base avec macro 2.xls").Worksheets("Syndications intragroupe")
    .Range("BS3") = WorksheetFunction.VLookup(.Range("BS3").Offset(0, -69), Workbooks(str).Worksheets("ODS").Range("C2:AZ29701"), 50, False)
    For i = 1 To n
        .Range("BS3").Offset(i, 0) = .Range("BS3")
    Next i
    End With
    ActiveWindow.SmallScroll down:=-200
 
    Workbooks(str).Close SaveChanges:=False

Voici ce que j'ai tapé.

Est ce correct?
car j ai la meme erreur au niveau du .Range

Code:

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
Sub fill()
Dim str As String
 
'Open the directory "C:\Users\...\Desktop" to be able to find the correct report
With Application.FileDialog(msoFileDialogFilePicker)
    .InitialFileName = "C:\Users\...\Desktop"
    .Show
    'and select the report on which you click
    If .SelectedItems.count > 0 Then Workbooks.Open (.SelectedItems(1))
End With
 
str = ActiveWorkbook.Name
 
'Pour répondre à te besoin que je n'ai pas saisi
n = 50
 
With Workbooks("syndications CR+LCL consolidé 2013  base avec macro 2.xls").Worksheets("Syndications intragroupe")
    'Tu te places en BS3
    'puis tu réalises ta fonction VLookup
    'Premier arg : ta range BS3 avec un offset de 69 colonnes - tu peux également placer en "dur" la range souhaitée soit B3 si je calcul bien
    'Deuxième arg : avec ton autre classeur, dans l'onglet ODS, sur la bonne Range
    'Troisième arg : ta colonne - 50
    If WorksheetFunction.CountIf(Workbooks(str).Worksheets("ODS").Range("C4:C29701"), .Range("BS3").Offset(0, -69)) > 0 Then
        .Range("BS3") = WorksheetFunction.VLookup(.Range("BS3").Offset(0, -69), Workbooks(str).Worksheets("ODS").Range("C2:AZ29701"), 50, False)
    Else
        .Range("BS3") = "Non trouvé"
    End If
 
    'Et là, le "FillDown" comme je ferais avec n = le nombre de fois que tu veux recopier ta cellule
    For i = 1 To n
        .Range("BS3").Offset(i, 0) = .Range("BS3")
    Next i
End With
 
'Et tu fermes ton classeur sans sauvegarder
Workbooks(str).Close SaveChanges:=False
 
End Sub

21/01/2013, 15h45
bentor22
Bonjour Kimy et bravo car ca a compilé.

Toutefois, je pense m etre mal exprimé:
Code:

1 2 3 4 5 'Et là, le "FillDown" comme je ferais avec n = le nombre de fois que tu veux recopier ta cellule For i = 1 To n .Range("BS3").Offset(i, 0) = .Range("BS3") Next i End With
je ne veux pas recopier la cellule mais je veux par exemple:
en BS3 avoir le recherchev ..... correspondant au B3
en BS4 avoir le recherchev ..... correspondant au B4
ect jusqu a la fin.
Car la il me recopie la valeur du BS3 sur l ensemble de la colonne BS

Voila :

Code:

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
Sub fill()
Dim str As String
 
'Open the directory "C:\Users\...\Desktop" to be able to find the correct report
With Application.FileDialog(msoFileDialogFilePicker)
    .InitialFileName = "C:\Users\...\Desktop"
    .Show
    'and select the report on which you click
    If .SelectedItems.count > 0 Then Workbooks.Open (.SelectedItems(1))
End With
 
str = ActiveWorkbook.Name
 
 
With Workbooks("syndications CR+LCL consolidé 2013  base avec macro 2.xls").Worksheets("Syndications intragroupe")
    For i = 0 To .Columns(.Range("BS3").Column).Find("*", , , , xlByColumns, xlPrevious).Row - 1
        If WorksheetFunction.CountIf(Workbooks(str).Worksheets("ODS").Range("C4:C29701"), .Range("BS3").Offset(i, -69)) > 0 Then
            .Range("BS3").Offset(i, 0) = WorksheetFunction.VLookup(.Range("BS3").Offset(i, -69), Workbooks(str).Worksheets("ODS").Range("C2:AZ29701"), 50, False)
        Else
            .Range("BS3").Offset(i, 0) = "Non trouvé"
        End If
    Next i
End With
 
'Et tu fermes ton classeur sans sauvegarder
Workbooks(str).Close SaveChanges:=False
 
End Sub

21/01/2013, 16h18
bentor22

Pffffffffffffffffff!!!!!!
Un tueuuuuuuuur.

t es le plus fort!
je t envois un message en privé dans 10 min

Bonjour Kimy, j aurai une reponse si cela ne te dérange pas:
Code:

1 2 3 4 5 6 7 8 9 10 11 str = ActiveWorkbook.Name With Workbooks("syndications CR+LCL consolidé 2013 base Macro off.xls").Worksheets("Syndications intragroupe") For i = 0 To .Columns(.Range("BT3").Column).Find("*", , , , xlByColumns, xlPrevious).Row - 1 If WorksheetFunction.CountIf(Workbooks(str).Worksheets("ODS").Range("C4:C29701"), .Range("BT3").Offset(i, -70)) > 0 Then .Range("BT3").Offset(i, 0) = WorksheetFunction.VLookup(.Range("BT3").Offset(i, -70), Workbooks(str).Worksheets("ODS").Range("C2:BC29701"), 53, False) Else .Range("BS3").Offset(i, 0) = "Non trouvé" End If Next i End With
Dans la partie de code souligné, je voudrais que la macro me renvoie le vlookup (pour l instant c est bon).
J aimerai egalement qu il me divise le resultat par la cellule BS3.
j ai voulu faire

Code:

.Range("BT3").Offset(i, 0) = WorksheetFunction.VLookup(.Range("BT3").Offset(i, -70), Workbooks(str).Worksheets("ODS").Range("C2:BC29701"), 53, False)/.Range(BS3)

ce qui n a pas marché!!

Toutefois en dans la cellule BT4 il me divisera le vlookup par BS4 ect......

Thanks d avance

Tente :

Code:

WorksheetFunction.VLookup(.Range("BT3").Offset(i, -70), Workbooks(str).Worksheets("ODS").Range("C2:BC29701"), 53, False).Value/.Range(BS3).Offset(i, 0).Value

Sinon, tu ajoutes une dimension en haut :

Code:

Dim reslt As Single

Et tu sauvegardes ton VLookup dans cette variable que tu divises et que tu places ensuite dans la cellule de destination :
Code:

1 2 3 4 5 6 If WorksheetFunction.CountIf(Workbooks(str).Worksheets("ODS").Range("C4:C29701"), .Range("BT3").Offset(i, -70)) > 0 Then reslt = WorksheetFunction.VLookup(.Range("BT3").Offset(i, -70), Workbooks(str).Worksheets("ODS").Range("C2:BC29701"), 53, False) .Range("BT3").Offset(i, 0) = reslt / .Range(BS3).Offset(i, 0).Value Else .Range("BS3").Offset(i, 0) = "Non trouvé" End If