l'indice n'appartient pas à la selection erreur 9

**sof78** · 06/07/2011, 18h17

Bonjour,

J'ai une erreur 9 a l’exécution de macro dont voici le 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
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
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
Private Sub CBOK_Click()
 
 
If CBonglet = "Réa 2009" Then
histoP = "2009\histo clotures prod 2009.xls"
End If
If CBonglet = "Réa 2010" Then
histoP = "2010\histo clotures prod.xls"
End If
If CBonglet = "Réa 2011" Then
histoP = "2011\histo clotures prod.xls"
End If
If CBonglet = "Ins 2009" Then
histoP = "2009\histo instances prod 2009.xls"
End If
If CBonglet = "Ins 2010" Then
histoP = "2010\histo instances prod.xls"
End If
If CBonglet = "Ins 2011" Then
histoP = "2011\histo instances prod.xls"
End If
If CBonglet = "Réa 2009" Then
histoS = "2009\histo clotures SAV2009.xls"
End If
If CBonglet = "Réa 2010" Then
histoS = "2010\histo clotures SAV .xls"
End If
If CBonglet = "Réa 2011" Then
histoS = "2011\histo clotures SAV.xls"
End If
If CBonglet = "Ins 2009" Then
histoS = "2009\histo instances SAV2009.xls"
End If
If CBonglet = "Ins 2010" Then
histoS = "2010\histo instances SAV.xls"
End If
If CBonglet = "Ins 2011" Then
histoS = "2011\histo instances SAV.xls"
End If
If CBonglet = "Réa 2009" Then
FhistoP = "histo clotures prod 2009"
End If
If CBonglet = "Réa 2010" Then
FhistoP = "histo clotures prod"
End If
If CBonglet = "Réa 2011" Then
FhistoP = "histo clotures prod"
End If
If CBonglet = "Ins 2009" Then
FhistoP = "histo instances prod 2009"
End If
If CBonglet = "Ins 2010" Then
FhistoP = "histo instances prod"
End If
If CBonglet = "Ins 2011" Then
FhistoP = "histo instances prod"
End If
If CBonglet = "Réa 2009" Then
FhistoS = "histo clotures SAV2009"
End If
If CBonglet = "Réa 2010" Then
FhistoS = "histo clotures SAV "
End If
If CBonglet = "Réa 2011" Then
FhistoS = "histo clotures SAV"
End If
If CBonglet = "Ins 2009" Then
FhistoS = "histo instances SAV2009"
End If
If CBonglet = "Ins 2010" Then
FhistoS = "histo instances SAV"
End If
If CBonglet = "Ins 2011" Then
FhistoS = "histo instances SAV"
End If
'Ouverture du fichier historique prod
    ChDir _
        "O:\ENTITE\PSCA.vqr\ConduiteActivité.vqr\cellule supervision\Tableaux de bord\2009"
    Workbooks.Open Filename:= _
        "O:\ENTITE\PSCA.vqr\ConduiteActivité.vqr\cellule supervision\Tableaux de bord\" & histoP
    Application.CommandBars("Stop Recording").Visible = False
 
' comparaison
 
    'je me deplace d'une ligne dans l'onglet saisonalité
    For j = 2 To 390
        'je me deplace d'une colonne dans l'onglet données du fichier sources
        For i = 2 To 22
      'Je compare si la cellule R1Ci de saisonalite = R1Cn de l'onglet données du fichier sources et si la cellule RkC1 de saisonalite = RnC3 de l'onglet données du fichier sources si c'est vrai on copie  RkCn de l'onglet données du fichier sources= la cellule RjCi de saisonalite
            k = 2
            n = 6
            Do Until Workbooks("saisonnalité N S").Worksheets("CBonglet").Cells(1, i).Formula = Workbooks(FhistoP).Worksheets("Données").Cells(1, n).Formula
                n = n + 1
                Loop
 
                Do Until Workbooks("saisonnalité N S").Worksheets(CBonglet).Cells(j, 3).Value = Workbooks(FhistoP).Worksheets("Données").Cells(k, 1).Value
                k = k + 1
                Loop
                Workbooks(FhistoP).Worksheets("Données").Cells(k, n).Copy Destination:=Workbooks("saisonnalité N S").Worksheets(CBonglet).Cells(j, i)
        Next i
    Next j
 
End Sub

Elle a pour but de comparer 2 fichiers comportant les mêmes noms de colonnes et les mêmes noms de lignes mais qui ne sont pas forcement dans le même ordre.
Quand les correspondances colonne et ligne sont exactes elle copie la cellule a l'intersection de la colonne et de la ligne du fichier A dans le fichier B.
Enfin c'est ce que j'essaie de faire mais ça ne marche pas

Je suis débutante et j'y suis depuis le debut de l'après midi mais je ne vois pas ou ça bug

Je sature et mon micro va bientot passé par le fenetre

Avant l'execution de cette partie, a l'ouverture du fichier saisonnalité j'ai une combobox qui me permet de choisir l'onglet à traiter (cbonglet) et d'ouvrir le bon fichier historique (cette partie là fonctionne !!!

)
Si quelqu'un à une idée merci d'éclairer ma lanterne

**Qwazerty** · 06/07/2011, 21h14

SAlut
Il aurait été sympa de mettre en évidence la ligne qui te provoque l'erreur

A voir le code je suppose qu'ici tu as ou auras un soucis

Code :

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

Worksheets("CBonglet")

les " sont de trop puisque CBonglet est une variable, ce doit etre une faute de frappe puisque tu n'as pas fait l'erreur sur les autres lignes.

Un conseil pour simplifier les écritures.
Open retourne une variable qui pointe le fichier créé, ainsi tu peux faire

Code :

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

1
2
3
4
5
Dim FichierCB as Workbook
Dim OngletDonnee as WorkSheet
 
set Fichiercb = Workbooks.Open(Filename:= _
"O:\ENTITE\PSCA.vqr\ConduiteActivité.vqr\cellule supervision\Tableaux de bord\" & histoP)

Dans la suite de ton code tu pourras directement faire référence à ton fichier

Code :

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

Set OngletDonnee = FichierCB.Worksheets("Données")

Pour éviter les boucles, tu as des fonctions qui te permettent de faire une recherche, Find par exemple, regarde dans l'aide. Elle te permettra de rechercher sur la ligne des entêtes de ton 2eme fichier les entêtes du 1er. Find te retourneras la cellule qui conrespond, il te suffiras de lire la propriété column de cette cellule.
Idem avec la colonne d’entêté et cette fois tu regarderas le propriété Row de la cellule retournée.
++
Qwaz

**sof78** · 07/07/2011, 07h45

Bonjour QWAZ,

Effectivement, dans le stress de mon premier post, j'ai oublié de mettre le ligne qui pose PB
C'est bien celle que tu as identifié

Code :

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

1
2
 Do Until Workbooks("saisonnalité N S").Worksheets(CBonglet).Cells(1, i), _
.Formula = Workbooks(FhistoP).Worksheets("Données").Cells(1, n).Formula

J'ai fais la modif sur les "" mais ça ne fonctionne toujours pas.
Je vais essayé de trouver du temps cet aprem pour reprendre ton post et creuse la piste du find
Je te remercie de ta réponse

Bonne journée
Sophie

**Qwazerty** · 07/07/2011, 08h36

Salut
Tu devrais déclarer toutes tes variable en début de code pour ne pas en oublier, rajoute "Option Explicit" en début de ton module.
Comment est défini CBOnglet? Je crois que tu fait une mauvaise utilisation à moins que CBonglet ne soit une variable déclarée dans un module.

Si c'est le nom inscrit sur ton onglet, il faut mettre les "" partout.

Autre chose pour facilité la relecture/modification du 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
22
23
Private Sub CBOK_Click()
'Toujours declarer toutes tes variables
Dim histoP As String, histoS As String
Dim TheCell As Range
Dim FichierCB As Workbook
Dim OngletDonnee As Worksheet
 
 
'Pour remplacer ta serie de If, tu peux soit utiliser des ElseIf
'soit une structure Select Case
Select Case CBOnglet
    Case "Réa 2009"
        histoP = "2009\histo clotures prod 2009.xls"
 
    Case "Réa 2010"
        'Tu peux mettre plusieurs lignes au besoin
        histoP = "2010\histo clotures prod.xls"
        histoS = "2010\histo clotures prod.xls"
 
    Case Else
        'Aucun fihcier correspondant
        MsgBox "Il n'existe pas de fichier correspndant"
End Select

Pour ta boucle de recherche, tu peux utiliser For each ... Next. Si tu as besoin d'aide essai de mettre un fichier avec l'organisation des colonnes et une ou 2 lignes de données.

++
Qwaz

**sof78** · 07/07/2011, 22h47

Bonsoir Quaz,

J'ai fait les modif indiquées,

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
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
Option Explicit
 
Dim histoP As String
Dim histoS As String
Dim FhistoP As String
Dim FhistoS As String
Dim FichierCB As Workbook
Dim OngletDonnee As Worksheet
Dim jour
Dim n
Dim k
Dim i
Dim j
Dim ui
 
Private Sub CBfermer_Click()
 
onglettraite.Hide
 
End Sub
 
Private Sub CBOK_Click()
 
Select Case CBonglet
    Case "Réa 2009"
    histoP = "2009\histo clotures prod 2009.xls"
    histoS = "2009\histo clotures SAV2009.xls"
    FhistoP = "histo clotures prod 2009"
    FhistoS = "histo clotures SAV2009"
 
    Case "Réa 2010"
    histoP = "2010\histo clotures prod.xls"
    histoS = "2010\histo clotures SAV.xls"
    FhistoP = "histo clotures prod"
    FhistoS = "histo clotures SAV"
 
    Case "Réa 2011"
    histoP = "2011\histo clotures prod.xls"
    histoS = "2011\histo clotures SAV.xls"
    FhistoP = "histo clotures prod"
    FhistoS = "histo clotures SAV"
 
    Case "Ins 2009"
    histoP = "2009\histo instances prod 2009.xls"
    histoS = "2009\histo instances SAV2009.xls"
    FhistoP = "histo instances prod 2009"
    FhistoS = "histo instances SAV2009"
 
    Case "Ins 2010"
    histoP = "2010\histo instances prod.xls"
    histoS = "2010\histo instances SAV.xls"
    FhistoP = "histo instances prod"
    FhistoS = "histo instances SAV"
 
    Case "Ins 2011"
    histoP = "2011\histo instances prod.xls"
    histoS = "2011\histo instances SAV.xls"
    FhistoP = "histo instances prod"
    FhistoS = "histo instances SAV"
 
End Select
 
 
 
 
'Ouverture du fichier historique prod
    Set FichierCB = Workbooks.Open(Filename:= _
    "O:\ENTITE\PSCA.vqr\ConduiteActivité.vqr\ _
cellule supervision\Tableaux de bord\" & histoP)
    Application.CommandBars("Stop Recording").Visible = False
 
   Set OngletDonnee = FichierCB.Worksheets("Données")
 
 
' comparaison
 
For Each ui In [F1:AA1]
  For j = 1 To 38
     If ui = Workbooks("saisonnalité N S").Worksheets(CBonglet) _
.Cells(1, j).Formula Then
       For Each jour In [A2:A370]
         For i = 2 To 370
           If jour = Workbooks("saisonnalité N S").Worksheets(CBonglet) _
.Cells(i, 1).Formula Then
                        OngletDonnee.Cells(jour, ui).Copy Destination:= _
                        Workbooks("saisonnalité N S").Worksheets(CBonglet) _
.Cells(i, j)
           End If
          Next i
         Next
        End If
       Next j
    Next
 
End Sub

mais j'ai toujours la même erreur cette fois c'est ici

Code :

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

1
2
If ui = Workbooks("saisonnalité N S").Worksheets(CBonglet) _
.Cells(1, j).Formula Then

J'ai aussi un autre pb c'est que je ne vois pas comment je repere ma colonne et ma ligne pour le copier

Je te joins mes fichiers
Le fichier qui contient les macro est saisonnalité
Au demarage tu as un bout de code dans this workbooks
Puis le reste est dans onglettraite
J'avoue, c'est aussi bien rangé que la chambre de mes ados

Bonne soirée
Sophie

**Qwazerty** · 07/07/2011, 23h18

Salut
OK, CBOnglet c'est un ComboBox, il faut que tu utilise la propriété value de celui ci

Code :

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

1
2
If ui = Workbooks("saisonnalité N S").Worksheets(CBonglet.value) _
.Cells(1, j).Formula Then

++
Qwaz

Salut
Alors

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
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
Private Sub CBOK_Click()
'Attention les declaration de variables doivent se faire ici si elles ne sont utilisé quand dans cette Sub
Dim histoP As String
Dim histoS As String
Dim FhistoP As String
Dim FhistoS As String
Dim NomEntete As String
Dim FichierCB As Workbook
Dim OngletDonnee As Worksheet, OngletCB As Worksheet
'Un conseil met des noms de variables explicites, ça te facilitera les modif
Dim n As Integer
Dim k As Integer
Dim i As Integer
Dim j As Integer
Dim Ui As Range, Jour As Range 'Toujours au moins une majuscule dans les variables, on les repere plus facilement dans le code, ainsi que les erreur de frappe
Dim TheCell As Range, TheCellDate As Range
Dim NumColSource As Integer, NumRowSource As Long
Dim NumColDestination As Integer, NumRowDestination As Long
 
Select Case CBonglet.value
    Case "Réa 2009"
    histoP = "2009\histo clotures prod 2009.xls"
    histoS = "2009\histo clotures SAV2009.xls"
    FhistoP = "histo clotures prod 2009"
    FhistoS = "histo clotures SAV2009"
 
    Case "Réa 2010"
    histoP = "2010\histo clotures prod.xls"
    histoS = "2010\histo clotures SAV.xls"
    FhistoP = "histo clotures prod"
    FhistoS = "histo clotures SAV"
 
    Case "Réa 2011"
    histoP = "2011\histo clotures prod.xls"
    histoS = "2011\histo clotures SAV.xls"
    FhistoP = "histo clotures prod"
    FhistoS = "histo clotures SAV"
 
    Case "Ins 2009"
    histoP = "2009\histo instances prod 2009.xls"
    histoS = "2009\histo instances SAV2009.xls"
    FhistoP = "histo instances prod 2009"
    FhistoS = "histo instances SAV2009"
 
    Case "Ins 2010"
    histoP = "2010\histo instances prod.xls"
    histoS = "2010\histo instances SAV.xls"
    FhistoP = "histo instances prod"
    FhistoS = "histo instances SAV"
 
    Case "Ins 2011"
    histoP = "2011\histo instances prod.xls"
    histoS = "2011\histo instances SAV.xls"
    FhistoP = "histo instances prod"
    FhistoS = "histo instances SAV"
 
End Select
 
 
 
 
'Ouverture du fichier historique prod
    Set FichierCB = Workbooks.Open(Filename:= _
    "O:\ENTITE\PSCA.vqr\ConduiteActivité.vqr\cellule supervision\Tableaux de bord\" & histoP)
    Application.CommandBars("Stop Recording").Visible = False
 
    Set OngletDonnee = FichierCB.Worksheets("Données")
    Set OngletCB = Workbooks("saisonnalité N S").Worksheets(CBonglet) 'Classeur + Feuille
 
' comparaison
 
    For Each Ui In OngletDonnee.[F1:AA1] 'precise toujours Classeur+Feuille
        'On supprime le 1er caracter de la cellule pour avoir le nom d'entete que l'on va rechercher sur l'autre feuille
        '_2NA -> 2NA
        NomEntete = Mid(Ui, 2)
        'Ici on utilise find pour trouver l'entete dans l'autre fichier
        Set TheCell = OngletCB.Rows(1).Find(NomEntete, , xlValues, xlWhole, xlByRows)
        'On verifie si l'entete a été trouvée
        If Not TheCell Is Nothing Then
            'L'entete existe
            'On memorise le numero de la colonne correspondante
            NumColDestination = TheCell.Column
            NumColSource = Ui.Column
            'Ensuite tu fais pareil avec la date
            'Dans la partie apres le In, je me place sur la derniere ligne du tableau (rows.count) et je remonte jusqu'a la derniere cellule non vide (End(xlUp)
            'On a donc une plage qui va de C4 jusqu'a la derniere cellule de la colonne C
            For Each Jour In OngletDonnee.Range("C4", OngletDonnee.Cells(OngletDonnee.Rows.Count, "C").End(xlUp))
                'Je ne sais pas trop si le format date ne va pas géner, à voir
                Set TheCellDate = OngletCB.Columns("A").Find(Jour, , , xlWhole, xlByColumns)
                'On verifie si la date à été trouvé
                If Not TheCellDate Is Nothing Then
                    'On a une correspondance
                    'On memorise le numero de la ligne
                    NumRowDestination = TheCellDate.Row
                    NumRowSource = Jour.Row
 
                    'On place la valeur Source dans la cellule destination
                    OngletCB.Cells(NumRowDestination, NumColDestination) = OngletDonnee.Cells(NumRowSource, NumColSource)
                End If
            Next
        End If
 
        'On remet a "zero" les variable range
        Set TheCell = Nothing
        Set TheCellDate = Nothing
    Next
 
End Sub

Je n'ai pas testé, regarde ce que cela donne.
J'ai commenté abondamment, essai de comprendre comment agit le code, tu peux exécuter le code en mode pas à pas. Pour cela place un point d’arrêt (touche F9) au début de la Sub CBOk. Tu cliques sur le bouton, tu auras une flèche jaune sur le point d'arrêt, pour te déplacer appuie sur F8. Tu peux voir le contenu des variable en passant ta souris au dessus ou en utilisant les espions (clique droit sur une variable - Ajouter un espion).

++
Qwaz

**sof78** · 08/07/2011, 16h08

Merci Quaz,

Je ne pourrai pas tester avant lundi mais je tiens vraiment à te remercie pour ton aide.
J'espere que ça ne te prend pas trop de temps de m'aider car la je culpabilise

Tu est surement plus rapide que moi car entre la recherche dans F1 pour les syntaxes, les tests en pas à pas et l'ecriture ça me prend des heures et en general, au final ça, ne marche pas.
Des fois, je me dis que j'aurai plus vite fait de faire des copier/coller
Je te tiens au courant mais de toute façon nos echanges n'ont permis de progresser, c'est génial

Bon week end
Sophie

**Qwazerty** · 08/07/2011, 17h53

Salut

Envoyé par sof78

entre la recherche dans F1 pour les syntaxes, les tests en pas à pas et l'ecriture ça me prend des heures et en general, au final ça, ne marche pas.

Ha ben ça c'est le lot quotidien de ceux qui commence et qui y mettent un peu de coeur

Pour la culpabilité, tous ceux qui participe au forum donne le temps qu'il estime pouvoir donner... ok des fois un peu plus ^^.

++
Bon weekend à toi aussi
Qwaz

**sof78** · 11/07/2011, 14h32

Bonjour Qwaz,

Tjrs la même erreur, OngletCB et OngletDonnee restent sans valeur.
J'ai mis des espions, tu trouveras ce que ça donne en pj.
Je vais essayer de remplacer cette ligne

Code :

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

Set OngletDonnee = FichierCB.Worksheets("Données")

Par

Code :

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

Set OngletDonnee=Workbooks("histo clotures prod 2009").Worksheets("Données")

et

Code :

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

Set OngletCB = Workbooks("saisonnalité N S").Worksheets(CBonglet)

Par

Code :

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

Set OngletCB = Workbooks("saisonnalité N S").Worksheets("Réa 2009")

Je te tiens au courant

Sophie

**Qwazerty** · 11/07/2011, 17h12

SAlut

C'est ma faute j'ai oublié de le corriger celui la.
Essai

Code :

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

Set OngletCB = Workbooks("saisonnalité N S").Worksheets(CBonglet.value)

Par contre pour OngletDonnee, il ne semble pas y avoir de problème, la variable contient bien une Feuille.

++
Qwaz

**sof78** · 15/07/2011, 16h49

Bonjour Qwaz,
Tjrs pareil, je continue de chercher, j'aurai plus de temps car l'heure des vacances à sonné

mais par contre je n'aurai plus d'internet
On ne peut pas tout avoir

A +
Sophie

**Qwazerty** · 15/07/2011, 19h56

Salut
Voila le fichier modifié et fonctionnel.

Je t'ai mis des choses en commentaires.

Je t'ai également fait 2 propositions de modification au niveau de ta liste déroulante. Dans la 1ère liste (CBOngletBis) la liste se rempli au chargement de la feuille en fonction des onglets présents dans le fichier et le choix d'une valeur est obligatoire. Dans l'autre j'ai remplacé Macro!A1:A7 par une étiquette dynamique, celle ci se redimensionne en fonction des nouvelles saisie dans l'onglet Macro.
A mon avis CBOngletBis est la meilleur option

, à toi de voir, tu peux panacher les différentes solutions

Passe de bonnes vacances loin du Net

++
Qwaz

**sof78** · 10/08/2011, 11h19

bonjour,

Super, ça fonctionne

Je te remercie pour ta precieuse aide.
De mon cote j'etais passé par une copie de l'onglet donnée dans le fichier de travail et j'avais repris ma boucle for next car je me perdais dans la defintion des variables dans ton code, je n'avais plus une erreur 9 mais une erreur 13.
Je ne comprend pas la difference etre la declaration d'une variable "classique" et l'utilisation de la fonction set.

Je te souhaite de bonne vacance si tu ne les a pas encore prises, moi la reprise se fait tout doucement.

Milles fois merci

Sophie

l'indice n'appartient pas à la selection erreur 9

Macros et VBA Excel

Discussions similaires

Partager

Partager