Discussion :

[bentor22]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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.

[Kimy_Ire]

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

.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 ?

[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.

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

[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

[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?

[Kimy_Ire]

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 !

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

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 ?

[bentor22]

Salut Kimy,

voici le code que j ai tapé:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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.

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.

[Kimy_Ire]

I-

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 !

III-
Tiens moi au courant !

[bentor22]

Bonjour Kimy et merci pour ta réponse.

Voici mon code:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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

[Kimy_Ire]

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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.

[bentor22]

Bonjour,

avec les modification, j ai

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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.

[Kimy_Ire]

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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...

[bentor22]

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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

[Kimy_Ire]

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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

[bentor22]

Bonjour Kimy et bravo car ca a compilé.

Toutefois, je pense m etre mal exprimé:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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

[Kimy_Ire]

Voila :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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

[bentor22]

Pffffffffffffffffff!!!!!!
Un tueuuuuuuuur.

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

[bentor22]

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

.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

[Kimy_Ire]

Tente :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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