Discussion :

[niniylle]

Bonjour à tous,

Je viens vers vous après de multiples tentatives infructueuses pour tenter de résoudre mon problème.
Je m'explique:
Dans un classeur "MyWB", une macro me permet d'ouvrir un classeur "EstimateFile". A l'aide d'un UserForm ("Dataselection"), j'invite l'utilisateur à sélectionner une colonne, puis une deuxième.

Ces paramètres vont me servir à faire un Index + Match pour renvoyer des valeurs dans l'onglet "DataExport" de mon classeur MyWB.
Or, lorsque j'exécute ma macro, aucune valeur n'est retournée.
Au fur et à mesure du code, j'ai mis des MsgBox pour tester les variables. Surprise, ils me renvoient les valeurs, mais mon Index Match ne marche pas.
J'ai fait de multiples tentatives (que j'ai mises en commentaires au fur et à mesure), mais je vous fais grâce de cela dans le code (ça allonge beaucoup trop).

J'ai isolé l'Index +Macth dans un autre module, en remplaçant les variables issues de l'UserForm, par leur valeur grâce à des "" . Et l'index marche.
J'ai donc un problème concernant le stockage et l'utilisation des variables issues de l'UserForm.

Voici le code de la macro en question :

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
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
Sub OpenFile2_Work()
Sheets("DataExport").Activate
 
Dim EstimateFile As String
Dim MyWB As String
Dim DerliData As Integer
MyWB = ThisWorkbook.Name
 
     With Application.FileDialog(3)
        .Title = "Please select your estimate file"
        .Show
 
        On Error Resume Next 'si annuler
        Fichier = .SelectedItems(1)
 
        If Err.Number <> 0 Then Exit Sub
        Workbooks.Open Fichier, UpdateLinks:=0 'désactive la MAJ des liaisons
 
 
    Dataselection.Show
    Dataselection.StartUpPosition = 3 'UserForm centré par rapport à l'écran
 
'Declaration des données du Userform
PartsSheet = Range(Dataselection.RefEditParts).Parent.Name
PartsCol = Dataselection.RefEditParts.Value
PriceSheet = Range(Dataselection.RefEditPrice).Parent.Name
PriceCol = Dataselection.RefEditPrice.Value
Application.ScreenUpdating = False
 
If Workbooks.Count = 2 Then
For i = 1 To 2
If Not Workbooks(i).Name = ThisWorkbook.Name Then EstimateFile = Workbooks(i).Name
Next i
End If
 
'Workbooks(MyWB).Sheets("DataExport").Activate
'MsgBox PriceSheet
'MsgBox PartsSheet
'MsgBox PartsCol
'MsgBox PriceCol
'MsgBox MyWB
'MsgBox EstimateFile
 
On Error Resume Next
'Index
DerliData = Workbooks(MyWB).Sheets("DataExport").Range("C" & Rows.Count).End(xlUp).Row
 
 
For Ligne = 2 To DerliData
Workbooks(MyWB).Sheets("DataExport").Cells(Ligne, 7) = WorksheetFunction.Index(Workbooks(EstimateFile).Sheets(PriceSheet).Rance(PriceCol).Columns, WorksheetFunction.Match(Workbooks(MyWB).Sheets("DataExport").Range(Cells(Ligne, 5), Cells(Ligne, 5)), Workbooks(EstimateFile).Sheets(PartsSheet).Range(PartsCol).Columns, 0))
Next
        On Error GoTo 0
End With
   Workbooks(MyWB).Sheets("DataExport").Activate
Application.ScreenUpdating = True
 
End Sub

Voici le code de l'index match qui fonctionne :

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
Sub IndexTest()
Sheets("DataExport").Activate
 
Dim EstimateFile As String
Dim MyWB As String
Dim DerliData As Integer
 
 
Windows("RT_Work_File_BOM Extract-v1.1.xlsm").Activate
Sheets("DataExport").Select
MyWB = ThisWorkbook.Name
'Récupère le nom du fichier ouvert
If Workbooks.Count = 2 Then
For i = 1 To 2
If Not Workbooks(i).Name = ThisWorkbook.Name Then EstimateFile = Workbooks(i).Name
Next i
End If
 
'Index
DerliData = Workbooks(MyWB).Sheets("DataExport").Range("C" & Rows.Count).End(xlUp).Row
 
Sheets("DataExport").Activate
  On Error Resume Next
 
    For Ligne = 2 To DerliData
Cells(Ligne, 7) = WorksheetFunction.Index(Workbooks(EstimateFile).Sheets("Chiffrage").Range("AC:ac"), WorksheetFunction.Match(Workbooks(MyWB).Sheets("DataExport").Range(Cells(Ligne, 5), Cells(Ligne, 5)), Workbooks(EstimateFile).Sheets("Chiffrage").Range("B:B"), 0))
Next
On Error GoTo 0
 
End Sub

Auriez-vous une idée pour résoudre mon problème svp ?

En vous remerciant par avance pour votre aide précieuse,

[zenpbb]

Bonjour,

Dans un premier temps :
1- Rajoute un On Error Goto 0 juste avant la ligne 17 de ton code
2- A quoi sert le On Error Resume Next en ligne 44 ? car sans au minimum un test sur la valeur Err.Number pour gérer une erreur que tu sais pouvoir arriver, ça revient juste à ignorer les éventuelles erreurs, ce qui n'est jamais bon ==> donc mets le en commentaire ou supprime le
3- Essaie de remplacer (au cas où la feuille active ne serait pas celle que tu crois, car ce qui peut faire fonctionner le deuxième code, c'est l'instruction Sheets("DataExport").Activate ...)

Code :

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

... WorksheetFunction.Match(Workbooks(MyWB).Sheets("DataExport").Range(Cells(Ligne, 5), Cells(Ligne, 5)) ...

par

Code :

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

... WorksheetFunction.Match(Workbooks(MyWB).Sheets("DataExport").Cells(Ligne, 5) ...

[niniylle]

Bonjour Zenpbb,

Tout d'abord merci de te pencher sur mon problème.
J'ai inséré un On Error GoTo 0 avant mon Workbook Open (ligne 17 du code), j'ai également mis le On Error Resume Next en commentaire et remplacé le

Code :

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

WorksheetFunction.Match(Workbooks(MyWB).Sheets("DataExport").Range(Cells(Ligne, 5), Cells(Ligne, 5))

par

Code :

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

WorksheetFunction.Match(Workbooks(MyWB).Sheets("DataExport").Cells(Ligne, 5)

cela m'affiche une Erreur d'exécution "438" : Propriété ou méthode non gérée par cet objet.

Je suppose que c'est parce qu'avec un Match, en indiquant un range, il faut lui renvoyer un méthode range. Je ne suis pas certaine que le problème vienne de là?
Cela marche dans mon code Index lorsque je substitue les valeurs aux réfEdit.
Mais pas lorsque j'utilise mes variables du refEdit.


Avec mon code initial, si je met en commentaire le On Error Resume Next, j'ai le même message d'erreur qui apparaît.

[zenpbb]

re,
tu as une faute de frappe : Rance au lieu de Range, ce qui peut expliquer l'erreur...

[niniylle]

re,

Bien vu. Je l'ai corrigé (et tant qu'à y être j'ai re balayé le code pour être certaine qu'il n'y en ait pas d'autres).
Pleine d'espoir, j'ai relancé, mais non, cela ne fonctionne toujours pas :'(

[zenpbb]

Bonjour,
Quel est ton code modifié qui pose problème, quelle erreur sur quelle ligne ?

[niniylle]

Bonjour Zenpbb,

Mon code modifié est celui-ci :

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
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
Sub OpenFile2_Work()
Sheets("DataExport").Activate
 
Dim EstimateFile As String
Dim MyWB As String
Dim DerliData As Integer
MyWB = ThisWorkbook.Name
 
     With Application.FileDialog(3)
        .Title = "Please select your estimate file"
        .Show
 
        On Error Resume Next 'si annuler
        Fichier = .SelectedItems(1)
 
        If Err.Number <> 0 Then Exit Sub
On Error GoTo 0
        Workbooks.Open Fichier, UpdateLinks:=0 'désactive la MAJ des liaisons
 
 
    Dataselection.Show
    Dataselection.StartUpPosition = 3 'UserForm centré par rapport à l'écran
 
'Declaration des données du Userform
PartsSheet = Range(Dataselection.RefEditParts).Parent.Name
PartsCol = Dataselection.RefEditParts.Value
PriceSheet = Range(Dataselection.RefEditPrice).Parent.Name
PriceCol = Dataselection.RefEditPrice.Value
Application.ScreenUpdating = False
 
If Workbooks.Count = 2 Then
For i = 1 To 2
If Not Workbooks(i).Name = ThisWorkbook.Name Then EstimateFile = Workbooks(i).Name
Next i
End If
 
'Workbooks(MyWB).Sheets("DataExport").Activate
'MsgBox PriceSheet
'MsgBox PartsSheet
'MsgBox PartsCol
'MsgBox PriceCol
'MsgBox MyWB
'MsgBox EstimateFile
 
'On Error Resume Next
'Index
DerliData = Workbooks(MyWB).Sheets("DataExport").Range("C" & Rows.Count).End(xlUp).Row
 
 
For Ligne = 2 To DerliData
Workbooks(MyWB).Sheets("DataExport").Cells(Ligne, 7) = WorksheetFunction.Index(Workbooks(EstimateFile).Sheets(PriceSheet).Range(PriceCol).Columns, WorksheetFunction.Match(Workbooks(MyWB).Sheets("DataExport").Range(Cells(Ligne, 5), Cells(Ligne, 5)), Workbooks(EstimateFile).Sheets(PartsSheet).Range(PartsCol).Columns, 0))
Next
        On Error GoTo 0
End With
   Workbooks(MyWB).Sheets("DataExport").Activate
Application.ScreenUpdating = True
 
End Sub

J'ai aussi essayé :

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
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
Sub OpenFile2_Work()
Sheets("DataExport").Activate
 
Dim EstimateFile As String
Dim MyWB As String
Dim DerliData As Integer
MyWB = ThisWorkbook.Name
 
     With Application.FileDialog(3)
        .Title = "Please select your estimate file"
        .Show
 
        On Error Resume Next 'si annuler
        Fichier = .SelectedItems(1)
 
        If Err.Number <> 0 Then Exit Sub
On Error GoTo 0
        Workbooks.Open Fichier, UpdateLinks:=0 'désactive la MAJ des liaisons
 
 
    Dataselection.Show
    Dataselection.StartUpPosition = 3 'UserForm centré par rapport à l'écran
 
'Declaration des données du Userform
PartsSheet = Range(Dataselection.RefEditParts).Parent.Name
PartsCol = Dataselection.RefEditParts.Value
PriceSheet = Range(Dataselection.RefEditPrice).Parent.Name
PriceCol = Dataselection.RefEditPrice.Value
Application.ScreenUpdating = False
 
If Workbooks.Count = 2 Then
For i = 1 To 2
If Not Workbooks(i).Name = ThisWorkbook.Name Then EstimateFile = Workbooks(i).Name
Next i
End If
 
'Workbooks(MyWB).Sheets("DataExport").Activate
'MsgBox PriceSheet
'MsgBox PartsSheet
'MsgBox PartsCol
'MsgBox PriceCol
'MsgBox MyWB
'MsgBox EstimateFile
 
'On Error Resume Next
'Index
DerliData = Workbooks(MyWB).Sheets("DataExport").Range("C" & Rows.Count).End(xlUp).Row
 
 
For Ligne = 2 To DerliData
Workbooks(MyWB).Sheets("DataExport").Cells(Ligne, 7) = WorksheetFunction.Index(Workbooks(EstimateFile).Sheets(PriceSheet).Range(PriceCol).Columns, WorksheetFunction.Match(Workbooks(MyWB).Sheets("DataExport").Cells(Ligne, 5), Workbooks(EstimateFile).Sheets(PartsSheet).Range(PartsCol).Columns, 0))
Next
        On Error GoTo 0
End With
   Workbooks(MyWB).Sheets("DataExport").Activate
Application.ScreenUpdating = True
 
End Sub

Comme indiqué, les deux ne fonctionnent pas et me renvoient l'erreur : Erreur d'exécution "438" : Propriété ou méthode non gérée par cet objet.

Cela doit provenir du On error Resume Next ligne 44 que j'ai mis en commentaire.
Cela ne résout malheureusement pas mon problème quant aux variables

[zenpbb]

Oui, dès que le MATCH ne renvoie rien, ça provoque une erreur.
Est-ce-que si tu remets le On Error Resume Next, tu récupères au moins quelques résultats ?

[niniylle]

Comme certaines valeurs ne sont pas contenues dans ma plage de recherche, pour éviter que cela fasse planter la macro, j'avais mis On error Resume Next.

Et bien c'est ça qui est bizarre.
J'ai testé mon Index Macth, avec un classeur en particulier. J'ai donc remplacé les variables RefEdit, par des variables connues.
Cela fonctionne très bien.

Mais dès que j'adapte le code pour prendre en compte les variables du RefEdit, cela ne fonctionne pas.


Code de l'Index Test (avec substitution des variables RefEdit, par des variables connues) :

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
Sub IndexTest()
Sheets("DataExport").Activate
 
Dim EstimateFile As String
Dim MyWB As String
Dim DerliData As Integer
 
 
Windows("RT_Work_File_BOM Extract-v1.1.xlsm").Activate
Sheets("DataExport").Select
MyWB = ThisWorkbook.Name
'Récupère le nom du fichier ouvert
If Workbooks.Count = 2 Then
For i = 1 To 2
If Not Workbooks(i).Name = ThisWorkbook.Name Then EstimateFile = Workbooks(i).Name
Next i
End If
 
'Index
DerliData = Workbooks(MyWB).Sheets("DataExport").Range("C" & Rows.Count).End(xlUp).Row
 
Sheets("DataExport").Activate
  On Error Resume Next
 
    For Ligne = 2 To DerliData
Cells(Ligne, 7) = WorksheetFunction.Index(Workbooks(EstimateFile).Sheets("Chiffrage").Range("AC:ac"), WorksheetFunction.Match(Workbooks(MyWB).Sheets("DataExport").Range(Cells(Ligne, 5), Cells(Ligne, 5)), Workbooks(EstimateFile).Sheets("Chiffrage").Range("B:B"), 0))
Next
On Error GoTo 0
 
End Sub

Code Adaptation du code précédent qui fonctionne, avec les variables issues des Ref Edit:
La macro s'exécute, mais aucun résultat n'est renvoyé.

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
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
Sub OpenFile2_Work()
Sheets("DataExport").Activate
 
Dim EstimateFile As String
Dim MyWB As String
Dim DerliData As Integer
MyWB = ThisWorkbook.Name
 
     With Application.FileDialog(3)
        .Title = "Please select your estimate file"
        .Show
 
        On Error Resume Next 'si annuler
        Fichier = .SelectedItems(1)
 
        If Err.Number <> 0 Then Exit Sub
        Workbooks.Open Fichier, UpdateLinks:=0 'désactive la MAJ des liaisons
 
 
    Dataselection.Show
    Dataselection.StartUpPosition = 3 'UserForm centré par rapport à l'écran
 
'Declaration des données du Userform
PartsSheet = Range(Dataselection.RefEditParts).Parent.Name
PartsCol = Dataselection.RefEditParts.Value
PriceSheet = Range(Dataselection.RefEditPrice).Parent.Name
PriceCol = Dataselection.RefEditPrice.Value
Application.ScreenUpdating = False
 
If Workbooks.Count = 2 Then
For i = 1 To 2
If Not Workbooks(i).Name = ThisWorkbook.Name Then EstimateFile = Workbooks(i).Name
Next i
End If
 
'Workbooks(MyWB).Sheets("DataExport").Activate
'MsgBox PriceSheet
'MsgBox PartsSheet
'MsgBox PartsCol
'MsgBox PriceCol
'MsgBox MyWB
'MsgBox EstimateFile
 
On Error Resume Next
'Index
DerliData = Workbooks(MyWB).Sheets("DataExport").Range("C" & Rows.Count).End(xlUp).Row
 
 
For Ligne = 2 To DerliData
Workbooks(MyWB).Sheets("DataExport").Cells(Ligne, 7) = WorksheetFunction.Index(Workbooks(EstimateFile).Sheets(PriceSheet).Range(PriceCol).Columns, WorksheetFunction.Match(Workbooks(MyWB).Sheets("DataExport").Range(Cells(Ligne, 5), Cells(Ligne, 5)), Workbooks(EstimateFile).Sheets(PartsSheet).Range(PartsCol).Columns, 0))
Next
        On Error GoTo 0
End With
   Workbooks(MyWB).Sheets("DataExport").Activate
Application.ScreenUpdating = True
 
End Sub

J'ai vérifié, mon UserForm s'appelle bien Dataselection, et mes RefEdit s'appellent respectivement Parts et Price.
Je ne sais vraiment plus quoi faire.

[zenpbb]

Comment fais-tu pour quitter le Userform Dataselection après avoir sélectionné tes deux plages ?
Es-tu sûre que lorsque ta macro reprend la main après l'affichage du Userform, les refEdit sont encore disponibles et bien valorisés ?

[niniylle]

Pour quitter mon UserForm, j'ai un bouton OK, qui cache l'UserForm, mais qui ne le vide pas.

Code :

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

1
2
3
4
5
6
Sub OK_Click()
Parts = Dataselection.RefEditParts.Value
Price = Dataselection.RefEditPrice.Value
 
Dataselection.Hide
End Sub

[niniylle]

A force d'acharnement, j'ai enfin trouvé d'où venait le problème.
Il ne vient pas des RefEdit. C'était le stockage du nom du fichier qui posait problème.

Pour info, voici le Code opérationnel, cela peut toujours servir :

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
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
Sub OpenFile2_WIP2()
Sheets("DataExport").Activate
 
 Dim EstimateFile As String
 Dim Parts, Price As String
 Dim MyWB As String
 Dim DerliData As Integer
MyWB = ThisWorkbook.Name
 
     With Application.FileDialog(3)
        .Title = "Please select your estimate file"
        .Show
 
 
        On Error Resume Next 'si annuler
 
        Fichier = .SelectedItems(1)
 
        If Err.Number <> 0 Then Exit Sub
 
        Workbooks.Open Fichier, UpdateLinks:=0 'désactive la MAJ des liaisons
 
'Récupère le nom du fichier ouvert 'Placé ici ESTIMATEFILE n'est pas stocké
EstimateFile = ActiveWorkbook.Name
 
    Dataselection.Show
    Dataselection.StartUpPosition = 3 'UserForm centré par rapport à l'écran
 
 'Declaration des données du Userform
PartsSheet = Range(Dataselection.RefEditParts).Parent.Name
Parts = Dataselection.RefEditParts.Value
PriceSheet = Range(Dataselection.RefEditPrice).Parent.Name
Price = Dataselection.RefEditPrice.Value
 
 
Application.ScreenUpdating = False
 
 
   On Error Resume Next
 
'Index
 DerliData = Workbooks(MyWB).Sheets("DataExport").Range("C" & Rows.Count).End(xlUp).Row
 Workbooks(MyWB).Activate
 
 
For Ligne = 2 To DerliData
Workbooks(MyWB).Sheets("DataExport").Cells(Ligne, 7) = WorksheetFunction.Index(Workbooks(EstimateFile).Sheets(PriceSheet).Range(Price).Columns, WorksheetFunction.Match(Workbooks(MyWB).Sheets("DataExport").Range(Cells(Ligne, 5), Cells(Ligne, 5)), Workbooks(EstimateFile).Sheets(PartsSheet).Range(Parts).Columns, 0))
 
  Next
 
  On Error GoTo 0
 
 
    End With
 
 'Ferme le fichier ouvert
 Workbooks(EstimateFile).Close
 
 Workbooks(MyWB).Sheets("DataExport").Activate
 Application.ScreenUpdating = True
 
End Sub