Problème pour définir automatiquement un nom sur une cellule

Le 28/11/2016 à 14:16

Bonjour,

Je souhaiterai pouvoir définir automatiquement à l'exécution d'une macro, le nom d'une cellule.

Mon problème vient lorsque que je souhaite définir la référence de la cellule sur laquelle je souhaite définir un nom.

Vous trouverez ci-joint le fichier Excel sur lequel je travaille en version light.

Lors de l'exécution de ma macro j'obtiens le message d'erreur suivant (cf photo) : Erreur 1004 : Désolé nous avons trouvé une erreur dans cette formule(etc...)

capture

Voici le code que j'ai réalisé avec selon moi en commentaire l'endroit où se situe l'erreur :

Sub nommer_cellule()

'on compte le nombre de ligne non vide
'on fait une boucle toutes les 18 lignes

With ThisWorkbook.ActiveSheet
Dim DernLigne As Long
Dim Nom_brut, Nom_encours, Nom_ok As String

DernLigne = Range("I" & Rows.Count).End(xlUp).Row

For i = 1 To DernLigne

    Nom_brut = Cells(i, 2).Value
    Nom_encours = Replace(Nom_brut, " ", "_")
    Nom_encours = Replace(Nom_encours, "'", "_")
    Nom_encours = Replace(Nom_encours, "(", "")
    Nom_encours = Replace(Nom_encours, ")", "")
    Nom_encours = Replace(Nom_encours, ",", "")
    Nom_onglet = ActiveSheet.Name
    Adresse = Cells(i, 1).Address

 ' ERREUR SE SITUE SUR LA PROCHIANE LIGNE DE CODE
    ActiveWorkbook.Names.Add Name:=Nom_encours, RefersToR1C1:= _
        "='" & Nom_onglet & "'!" & Adresse
    ActiveWorkbook.Names(Nom_encours).Comment = ""

    ' doit resembler à ça    ='Feuille A'!$A$1:$A$18 ou
    '='Feuille A'!$A$1

    'On incrémente le i de 18
    i = i + 18
    'On arrete la boucle si l'écart entre le i et la dernière ligne est inférieur à 18
        If DernLigne - i < 18 Then Exit For
Next
End With

End SuB

Merci d'avance pour votre aide,

Batoine

16creation-de-nom-automatiques-v1.xlsm (42.37 Ko)
Le 28/11/2016 à 15:17

Bonjour,

Le problème pourrait se situer au niveau du signe "="

Au lieu de

ActiveWorkbook.Names.Add Name:=Nom_encours, RefersToR1C1:= _

"='" & Nom_onglet & "'!" & Adresse

Essaie

ActiveWorkbook.Names.Add Name:=Nom_encours, RefersToR1C1:= _

Nom_onglet & "'!" & Adresse

Le 28/11/2016 à 15:28

Bonjour,

Tu définis une adresse en style A1 et tu utilises RefersToR1C1 !!!

C'est RefersTo qui convient.

D'autre part les variables doivent être typées individuellement. Ta ligne de déclaration de 3 variables String n'en type qu'une. Les deux autres ne sont pas typées, donc de type Variant.

Et la variable typée n'est par ailleurs pas utilisée dans le code. Tu en utilises une autre, non déclarée...

Cordialement.

Le 28/11/2016 à 15:32

Bonjour,

Tu as écris RefersToR1C1 ( références Row et Column) et tu donnes une adresse $B$1

Remplace par RefersTo

Il y a aussi ActiveWorkbook.Names(Nom_encours).Comment = "" que tu devrais désactiver pour tes tests.

Il y a aussi : Adresse = Cells(i, 1).Address ... cela donne A1 .... Adresse = Cells(i, 2).Address ... donne B1

Est-ce bien B1 que tu veux référencer?

Gelinotte

Le 28/11/2016 à 16:26

Bonjour à tous,

Merci pour vos réponses. J'ai bien pris en compte vos remarques :

  • Le remplacement du RefersToR1C1 par RefersTo
    La suppression du "=" &
    Le définition des variables string

Cependant un problème persiste. Lorsque j'essaie de définir un lien vers les noms précédemment définis aucun n'est reconnu (cf photo)

capture3

J'ai donc essayé de vérifier la syntaxe des références des noms créer dans ma macro en créant une étiquette de nom manuellement. Effectivement la syntaxe n'est pas la même. voici un aperçu des différences :

capture2

Les différences sont :

la suppression des "

L'ajout d'une apostrophe en première position

Une différence sur l'étendue de la référence. Elle prend toute l'étendue des cellules fusionnées et non que la première cellule

Voici le nouveau code et une copie du nouveau projet :

Sub nommer_cellule()

'on compte le nombre de ligne non vide
'on fait une boucle toutes les 18 lignes

With ThisWorkbook.ActiveSheet
Dim DernLigne As Long
Dim Nom_brut As String
Dim Nom_encours As String

DernLigne = Range("I" & Rows.Count).End(xlUp).Row

For i = 1 To DernLigne

    Nom_brut = Cells(i, 2).Value
    Nom_encours = Replace(Nom_brut, " ", "_")
    Nom_encours = Replace(Nom_encours, "'", "_")
    Nom_encours = Replace(Nom_encours, "(", "")
    Nom_encours = Replace(Nom_encours, ")", "")
    Nom_encours = Replace(Nom_encours, ",", "")
    Nom_onglet = ActiveSheet.Name
    Adresse = Cells(i, 1).Address

 ' ERREUR SE SITUE SUR LA PROCHIANE LIGNE DE CODE
    ActiveWorkbook.Names.Add Name:=Nom_encours, RefersTo:= _
        Nom_onglet & "'!" & Adresse
'    ActiveWorkbook.Names(Nom_encours).Comment = ""

    ' doit resembler à ça    ='Feuille A'!$A$1:$A$18 ou
    '='Feuille A'!$A$1

    'On incrémente le i de 18
    i = i + 18
    'On arrete la boucle si l'écart entre le i et la dernière ligne est inférieur à 18
        If DernLigne - i < 18 Then Exit For
Next
End With

End Sub

Merci d'avance pour voter aide encore une fois,

Batoine

14creation-de-nom-automatiques-v2.xlsm (43.80 Ko)
Le 28/11/2016 à 20:45

Bonjour,

Il restait 2 coquilles :

1- Adresse = Cells(i, 1).Address donne A1 ... il faut écrire Adresse = Cells(i, 2).Address qui donne B1

2- Dans ton fichier exemple, de B1 et à toutes les 19 lignes, ce sont les noms. Sauf que les noms répètent ... l'adressage s'adapte donc.

J'ai corrigé dans le fichier joint.

À tester ....

Gelinotte

17batoine-creation-de-nom-automatiques-v3.xlsm (41.62 Ko)
Le 28/11/2016 à 21:57

Bonsoir,

Je n'ai pas tout suivi, mais l'argument RefersTo inclut le signe = et la première apostophe (appelée dans ce cas guillemet simple)

"='" & Nom_onglet & "'!" & Adresse

Le 29/11/2016 à 10:32

Bonjour gelinotte, MFerrand,

Merci pour vos réponse.

@Gelinotte :

Le choix de la colonne 1 n'était pas une coquille, mais un choix volontaire. J'ai tout de même modifié pour faciliter la chose et écarter une source d'erreur potentielle.

Je n'ai pas compris ta deuxième remarque

@MFerrand :

Merci pour l'info, ceci avait déjà été corrigé dans la V2

Je crois finalement comprendre d'où vient le problème grâce à vos réponses. Le problème vient des apostrophes qui sont automatiquement rajoutées autour de ce que j'ai nommé Adresse2. Pourtant je ne souhaite pas les voir apparaitre.

J'ai aussi rajouté une apostrophe au début.

Voici une autre photo qui illustre bien la différence de prise en compte de la référence du nom en fonction des apostrophes ou non.

capture4

Voici une nouvelle fois mon code et mon fichier en V4.

Sub nommer_cellule()

'on compte le nombre de ligne non vide
'on fait une boucle toutes les 18 lignes

With ThisWorkbook.ActiveSheet
Dim DernLigne As Long
Dim Nom_brut As String
Dim Nom_encours As String

DernLigne = Range("I" & Rows.Count).End(xlUp).Row

For i = 1 To DernLigne

    Nom_brut = Cells(i, 2).Value
    Nom_encours = Replace(Nom_brut, " ", "_")
    Nom_encours = Replace(Nom_encours, "'", "_")
    Nom_encours = Replace(Nom_encours, "(", "")
    Nom_encours = Replace(Nom_encours, ")", "")
    Nom_encours = Replace(Nom_encours, ",", "")
    Nom_onglet = ActiveSheet.Name
    Adresse = Cells(i, 2).Address  '''' ici le 2 représente la 2e colonne soit ( B )

    Adresse2 = "'" & Nom_onglet & "'!" & Adresse

 ' ERREUR SE SITUE SUR LA PROCHIANE LIGNE DE CODE
    ActiveWorkbook.Names.Add Name:=Nom_encours, RefersTo:= _
        Adresse2
'    ActiveWorkbook.Names(Nom_encours).Comment = ""

    ' doit resembler à ça    ='Feuille A'!$A$1:$A$18 ou
    '='Feuille A'!$A$1

    'On incrémente le i de 18
    i = i + 18
    'On arrete la boucle si l'écart entre le i et la dernière ligne est inférieur à 18
        If DernLigne - i < 18 Then Exit For
Next
End With

End Sub

Bilan toujours ces satanés " !

Merci pour vos nouvelles remarques, on y est presque

Batoine

30batoine-creation-de-nom-automatiques-v4.xlsm (42.19 Ko)
Le 29/11/2016 à 13:38

Bonjour,

Je suis confus, mille excuses ....

Je n'ai jamais fait F5 pour valider que ça fonctionnait.

Je me contentais de regarder si le nom était créé.

MFerrand a tout à fait raison, il manque le début ...

Test ceci ...

ActiveWorkbook.Names.Add Name:=Nom_encours, RefersTo:= _
        "='" & Nom_onglet & "'!" & Adresse

Gelinotte

Le 29/11/2016 à 14:04

Re bonjour,

Effectivement, c'était ça !

Merci beaucoup et bonne journée !

Batoine

Rechercher des sujets similaires à "probleme definir automatiquement nom"