Discussion :

[débutantvba1]

Bonjour,

je suis en train d'automatiser un plan de formation sous excel 2010.
J'ai une feuille qui contient en colonne A le nom de la formation, en colonne B la date de début de la session 1, en colonne C la date de début de la session 2, etc...

Je cherche à faire une macro qui crée automatiquement, dans le gestionnaire de noms, des plages associant, pour chaque ligne n, le contenu de la colonne A et comme zone les cellules où une date de session est renseignée.

J'ai testé deux solutions avec une boucle For...Next, qui n'ont pas fonctionné.

La première en utilisant . Select

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
Static Sub nom_plage()
 
Application.ScreenUpdating = False 'rend invisible la mise à jour de la macro
 
Dim nb_lignes_formations As Integer, nb_colonnes_formations As Integer, num_ligne As Integer, num_colonne As Integer, nom_formation As String
 
 
Sheets("données début stages").Activate 'active la feuille "données début stages"
 
nb_lignes_formations = WorksheetFunction.CountA(Range("A:A")) 'décompte nb lignes formation
nb_colonnes_formations = WorksheetFunction.CountA(Range("1:1")) 'décompte nb colonnes formation
 
 
Range(Cells(1, 1), Cells(nb_lignes_formations, nb_colonnes_formations)).Activate 'activer la zone de saisie des données formation
 
 
For num_ligne = 2 To nb_lignes_formations
 
    nom_formation = Cells(num_ligne, 1)
    Range(Cells(num_ligne, 2), Cells(num_ligne, nb_colonnes_formations)).Select
 
    ActiveWorkbook.Names.Add Name:=nom_formation, RefersTo:=Selection
 
 
Next
 
End Sub

Ca ne fonctionne pas, le déboggage me renvoie

erreur 1004: le nom entré n'est pas valide.
Raisons possibles:
-le nom ne commence pas par une lettre ou un trait de soulignement
-le nom contient un espace ou un autre caractère non valide
-le nom est en conflit avec un nom prédéfini dans excel ou le


et me surligne en jaune la ligne: ActiveWorkbook.Names.Add Name:=nom_formation, RefersTo:=Selection

Avez-vous une idée de mon erreur?


j'ai testé une autre solution en utilisant l'adresse de la zone en format R1C1.

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
Static Sub nom_plage()
 
Application.ScreenUpdating = False
 
 
Dim nb_lignes_formations As Integer, nb_colonnes_formations As Integer, num_ligne As Integer, num_colonne As Integer
Dim nom_formation As String
Dim adresse1 As String
 
Sheets("données début stages").Activate 'active la feuille "données début stages"
 
nb_lignes_formations = WorksheetFunction.CountA(Range("A:A")) 'décompte nb lignes formation
nb_colonnes_formations = WorksheetFunction.CountA(Range("1:1")) 'décompte nb colonnes formation
 
 
Range(Cells(1, 1), Cells(nb_lignes_formations, nb_colonnes_formations)).Activate
 
 
For num_ligne = 2 To nb_lignes_formations
 
    nom_formation = Cells(num_ligne, 1)
    Range(Cells(num_ligne, 2), Cells(num_ligne, nb_colonnes_formations)).Select
 
    adresse1 = Range(Cells(num_ligne, 2), Cells(num_ligne, nb_colonnes_formations)).Address(ReferenceStyle:=xlR1C1)
 
 
   MsgBox Var1 & " " & adresse1
 
    ActiveWorkbook.Names.Add Name:=nom_formation, RefersToR1C1:="=adresse1"
 
        'ActiveWorkbook.Names.Add Name:=nom_formation, RefersToR1C1:="=données début stages!R1C1:R2C2"
        'ActiveWorkbook.Names("Var1").RefersToR1C1 = "nom_formation
 
Next
 
 
End Sub

Cette solution ne fonctionne pas non plus.
La macro tourne sur la première ligne de formation en m'affichant le bon nom de formation et la bonne zone dans le Msgbox. Par contre, dans le gestionnaire de noms, le premier nom de formation est le bon mais la zone indique: fait référence : =adresse1
Le Msgbox de la seconde ligne de formation est également correct mais immédiatement après j'ai le message d'erreur suivant du déboggae :


erreur 1004: le nom entré n'est pas valide.
Raisons possibles:
-le nom ne commence pas par une lettre ou un trait de soulignement
-le nom contient un espace ou un autre caractère non valide
-le nom est en conflit avec un nom prédéfini dans excel ou le


et me surligne en jaune la ligne: ActiveWorkbook.Names.Add Name:=nom_formation, RefersToR1C1:="=adresse1"


merci pour vos lumières sur mes erreurs de code,

bonne soirée

[pgz]

Bonsoir.

Il faut préciser le nom de la feuille. Par exemple dans ton premier cas, tu peux remplacer

Code :

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

1
2
3
Range(Cells(num_ligne, 2), Cells(num_ligne, nb_colonnes_formations)).Select
 
    ActiveWorkbook.Names.Add Name:=nom_formation, RefersTo:=Selection

Par

Code :

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

1
2
3
set oRng = ThisWorkbook.Workshheets("sNomFeuil").Range(Cells(num_ligne, 2), Cells(num_ligne, nb_colonnes_formations))
 
    ThisWorkbook.Names.Add Name:=nom_formation, RefersTo:="='" & sNomFeuil & "'!" & oRng.address

Tu vois l'idée ?

Cordialement,

PGZ

[débutantvba1]

Bonjour PGZ,

merci de ta réponse; j'ai tenté de faire comme tu me l'as indiqué, cad que j'ai

J'ai déclaré une variable sNomFeuil As Worksheet, à laquelle j'ai affecté ma feuille "données début stages".
j'ai déclaré une variable oRNg As Range.

Voici le code revu

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 nom_plage()
 
Application.ScreenUpdating = False
 
 
 
Dim nb_lignes_formations As Integer, nb_colonnes_formations As Integer, num_ligne As Integer, num_colonne As Integer
Dim nom_formation As String
Dim sNomFeuil As Worksheet
Dim oRNg As Range
 
Set sNomFeuil = Sheets("données début stages")
 
 
nb_lignes_formations = WorksheetFunction.CountA(Range("A:A")) 'décompte nb lignes formation
nb_colonnes_formations = WorksheetFunction.CountA(Range("1:1")) 'décompte nb colonnes formation
 
 
 
For num_ligne = 2 To nb_lignes_formations
 
     nom_formation = Cells(num_ligne, 1)
 
    Set oRNg = ThisWorkbook.Worksheets("sNomFeuil").Range(Cells(num_ligne, 2), Cells(num_ligne, nb_colonnes_formations))
 
 
    ThisWorkbook.Names.Add Name:=nom_formation, RefersTo:="='" & sNomFeuil & "'!" & oRNg.Address
 
 
Next
 
End Sub

Le déboggage me sort
"erreur d'exécution 9.
L'indice n'appartient pas à la sélection"

Et me colore en jaune la ligne:

Code :

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

Set oRNg = ThisWorkbook.Worksheets("sNomFeuil").Range(Cells(num_ligne, 2), Cells(num_ligne, nb_colonnes_formations))

je ne comprends pas.

Je pense que l'apprentissage va être très long

bonne journée

[vinc_bilb]

Bonjour,

Il y a confusion entre un objet (worksheet ici) et son nom, et comme vous le dites, c'est de l'apprentissage .... J'ai fait plusieurs fois ce type de confusion avant que ça ne me rentre dans le neuronne

Code :

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

Dim sNomFeuil As String ' et non Worksheet

Bonne continuation

[Igloobel]

Bonjour,

je reprend ce que tu dis

Bonjour,

je suis en train d'automatiser un plan de formation sous excel 2010.
J'ai une feuille qui contient en colonne A le nom de la formation, en colonne B la date de début de la session 1, en colonne C la date de début de la session 2, etc...

Je cherche à faire une macro qui crée automatiquement, dans le gestionnaire de noms, des plages associant, pour chaque ligne n, le contenu de la colonne A et comme zone les cellules où une date de session est renseignée.

A ce stade je vois pas l'utilité de faire une macro Un simple TCD filtré suffit !

Ou alors il y a quelque chose qui m'echappe ...

Peux-tu donner un exemple

A bientôt

[pgz]

Bonjour.

Le pb est celui que t'indique vinc_bilb.
EN reprenant ton dernier code, cela devrait être

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 nom_plage()
 
 
Dim nb_lignes_formations As Integer, nb_colonnes_formations As Integer, num_ligne As Integer, num_colonne As Integer
Dim nom_formation As String
Dim oFeuil As Excel.Worksheet
Dim oRNg As Excel.Range
 
Set oFeuil = Thisworkbook.WorkSheets("données début stages")
 
 
nb_lignes_formations = WorksheetFunction.CountA(Range("A:A")) 'décompte nb lignes formation
nb_colonnes_formations = WorksheetFunction.CountA(Range("1:1")) 'décompte nb colonnes formation
 
 
 
For num_ligne = 2 To nb_lignes_formations
 
     nom_formation = Cells(num_ligne, 1)
 
    Set oRNg = oFeuil.Range(Cells(num_ligne, 2), Cells(num_ligne, nb_colonnes_formations))
 
 
    ThisWorkbook.Names.Add Name:=nom_formation, RefersTo:="='" & oFeuil.name & "'!" & oRNg.Address
 
 
Next num_ligne
Set oFeuil = Nothing
Set oRng = Nothing
End Sub

J'espère qu'il ne reste pas une erreur. En VBA tu dois t'attacher à toujours savoir quel type de variable ou d'objet tu manipules.

Cordialement,

PGZ

[débutantvba1]

Bonsoir,

pour répondre à ta question Igloobel, je suis en train de préparer une trame de plan de formation:
-qui soit utilisable plusieurs années de suite en ayant simplement à changer les données du plan de formation et de l'effectif
-qui soit aussi automatisée que possible pour pouvoir être utilisée et renseignée par le responsable de la formation qui ne sait pas faire des liens entre deux feuilles d'un même fichier excel. Du coup je veux qu'il n'ait qu'à utiliser des listes déroulantes permettant de saisir, par exemple, la date de session de formation ou "formation programmée/ formation réalisée" (d'où le gestionnaire de noms)

Du coup, j'ai fait une feuille de saisie principale qui contienne dans les premières colonnes les données personnelles des salariés (nom/ prénom/ service...), puis les colonnes de saisie des formations. Cette feuille de saisie principale doit être nourrie par des données contenues dans d'autres feuilles dans lesquelles seront saisies les données "source".
A la base je pensais le faire avec excel puisque je ne sais pas utiliser vba.
Mais en commençant à travailler sur ce fichier, j'ai buté sur la possibilité d'utiliser des indirect avec des résultats de recherchev et je n'ai pas trouvé d'autre façon de faire mon tableau, du coup je me suis dit que c'était l'occasion de commencer à apprendre vba.
C'est un peu laborieux, globalement ça ne fonctionne jamais du 1er coup . Mais quand ça marche c'est sympa.


Pour PGZ et vinc_bilb, merci de votre aide, ça fonctionne!
J'ai compris pour le type de variable mais pas pour la syntaxe utilisée dans la ligne, je vais essayer de comprendre.

Code :

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

ThisWorkbook.Names.Add Name:=nom_formation, RefersTo:="='" & oFeuil.name & "'!" & oRNg.Address

Mais je ne connaissais pas les notations oFeuil1, orNG, sNomFeuil. la lettre du début (o, s) donne-t-elle le type de la variable (object, string)?

bonne soirée!

[pgz]

Bonsoir

Pour PGZ et vinc_bilb, merci de votre aide, ça fonctionne!
J'ai compris pour le type de variable mais pas pour la syntaxe utilisée dans la ligne, je vais essayer de comprendre.
ThisWorkbook.Names.Add Name:=nom_formation, RefersTo:="='" & oFeuil.name & "'!" & oRNg.Address
Mais je ne connaissais pas les notations oFeuil1, orNG, sNomFeuil. la lettre du début (o, s) donne-t-elle le type de la variable (object, string)?

Pour t'aider, l'argument RefersTo de la méthode Add doit ressembler au final à : 'NomFeuil'!A1:C5
La ligne de code sert à construire ce genre de valeur avec le bon nom de feuille et la bonne plage.

Pour la notation des variables, en effet, de nombreux développeurs préfixent les noms utilisés pour garder en tête le type. "o" avant on objet, "s" ou "str" avant une chaîne, "dbl" devant un double, ... C'est une façon utile de travailler, pas du tout une obligation imposée par VBA.

Cordialement,

PGZ

[vinc_bilb]

Bonjour,

En complément de ce qui a été expliqué par Pierre:

ThisWorkbook.Names.Add Name:=nom_formation, RefersTo:="='" & oFeuil.name & "'!" & oRNg.Address

=> Ca revient à définir un nom sous excel pour un range, de visibilité ici = workbook

Sur le fond, je ne suis pas certain que VBA soit la solution pour ce type de besoin (LOOKUP et INDEX + MATCH devrait être pas mal du tout, peut-être conjusguée à un TCD), à voir, mais en tout cas, c'est formateur

Dans cette optique, je vous suggère:
1. De toujours utiliser la déclaration obligatoire du type de variable, même durant la phase de developpement

Code :

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

Option Explicit

2. De définir le type de vos variables et manier le type variant avec précaution (type par défaut)
3. De bien garder en tête la règle des 20%/80% : 20% du temps pour arriver au 80% du résultat (code fonctionne), puis 80% du temps pour tous les casses têtes: erreur, saisie incorrect (valeur ou type, ....)

Bonne journée!