Discussion :

[Ieriamus]

Bonjour à tous,

Après quelques jours de recherches infructueuses afin de trouver une solution à mon problème, je me décide à vous le proposer:

Dans un classeur avec une trentaine de feuilles, chaque feuille possède une plage nommée d'une vingtaine de cellules (souvent plus).
Seulement, je dois supprimer deux/trois cellules pour chaque plage pour chaque feuille et, comme vous le savez, le gestionnaire des noms d'excell est SUPER pratique pour le faire
Donc, mon idée était de créer une macro afin de:
1/ créer la plage de cellule nommée dans une feuille vierge. par exemple: =Vierge!$AC$12;Vierge!$AC$18;Vierge!$AC$24;Vierge!$AC$30.
2/ remplacer le mot "Vierge" par le nom de la feuille active: activesheet.name avec une variable en string (c'est bizarre d'écrire ça comme ça )

et donc j'ai créé ceci pour tester avant de l'inclure dans une plage nommée:

Code :

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

1
2
3
4
5
6
7
8
9
Option Explicit
Dim cible As Variant
Dim nom As String
Sub gestion_nom()
On Error Resume Next
cible = ThisWorkbook.Sheets("Feuil1").Range("A8") 'où j'avais collé une ref du gestionnaire de nom =Vierge!$G$2:$X$3;Vierge!$C$10;Vierge!$C$12;Vierge!$D$10
nom = ActiveSheet.Name
cible.Replace what:="Vierge", replacement:=nom
End Sub

hé bien même ça, il ne le prend pas ...

J'ai testé via l'enregistreur de macro :

Code :

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

1
2
3
4
5
6
7
    ActiveWorkbook.Worksheets("Vierge").Names("CompChiffre").RefersToR1C1 = _
        "=Vierge!R9C11:R11C11,Vierge!R9C14:R11C14,Vierge!R9C17:R11C17,Vierge!R9C26:R10C26,Vierge!R9C29,Vierge!R9C31,Vierge!R9C34,Vierge!R15C11:R17C11,Vierge!R15C14:R17C14,Vierge!R15C17:R17C17,Vierge!R15C26:R16C26,Vierge!R15C29,Vierge!R15C31,Vierge!R15C34,Vierge!R21C11:R23C11,Vierge!R21C14:R23C14,Vierge!R21C17:R23C17,Vierge!R21C26:R22C26,Vierge!R21C29,Vierge!R21C31,Vierge!R21" & _
        "C34,Vierge!R27C11:R29C11,Vierge!R27C14:R29C14,Vierge!R27C17:R29C17,Vierge!R27C26:R28C26,Vierge!R27C29,Vierge!R27C31,Vierge!R27C34,Vierge!R33C11:R35C11,Vierge!R33C14:R35C14,Vierge!R33C17:R35C17,Vierge!R33C26:R34C26,Vierge!R33C29,Vierge!R33C31,Vierge!R33C34,Vierge!R39C11:R41C11,Vierge!R39C14:R41C14,Vierge!R39C17:R41C17,Vierge!R39C26:R40C26,Vierge!R39C29,Vierge!R39C31," & _
        "Vierge!R39C34,Vierge!R62C11:R64C11,Vierge!R62C14:R64C14,Vierge!R62C17:R64C17,Vierge!R62C26:R63C26,Vierge!R62C29,Vierge!R70C11:R72C11,Vierge!R70C14:R72C14,Vierge!R70C17:R72C17,Vierge!R70C26:R71C26,Vierge!R70C29,Vierge!R96C11:R98C11,Vierge!R96C14:R98C14,Vierge!R96C17:R98C17,Vierge!R96C26:R97C26,Vierge!R96C29,Vierge!R104C11:R106C11,Vierge!R104C14:R106C14,Vierge!R104C17" & _
        ":R106C17,Vierge!R104C26:R105C26,Vierge!R104C29,Vierge!R50C23,Vierge!R50C26,Vierge!R54C23,Vierge!R54C26,Vierge!R50C26,Vierge!R50C23,Vierge!R54C23,Vierge!R54C26" & _
        ""
    Application.CutCopyMode = False

Bref, je ne sais plus si je dois faire du "remplacer" ou créer une nouvelle plage nommée...

Bref de bref, je suis largué. Ce qui me paraissait simple se transforme en inaccessible.

Help

[Philippe Tulliez]

Bonjour,
Petites remarques

• Les noms font partie de la collection Names
• Utiliser l'instruction On Error Resume Next est une très mauvaise idée car dans votre cas, c'est un cache misère

Exemple d'une procédure qui crée un nom (pTauxTva) avec comme portée le classeur où se trouve le code VBA et comme référence , la cellule C3 de la feuille dont le CodeName est shtParameter du même classeur.

Code :

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

1
2
3
4
5
6
7
8
Sub t()
  Dim Cell As Range
  Dim Add As String
  Set Cell = shtParameter.Range("C3")
  Add = "=" & Cell.Address(External:=True)
  ThisWorkbook.Names.Add Name:="pTauxTva", RefersTo:=Add
  Set Cell = Nothing
End Sub

Dans ce code, je ne vérifie

Pour savoir ce qu'est le CodeName d'une feuille et quel est l'avantage de l'utiliser, lire ce billet VBA Excel - Comment s’affranchir de la modification du nom de la feuille en utilisant son CodeName

[zenpbb]

Bonjour,
Je ne suis pas sûr d'avoir bien compris le contexte, mais si tu définis ta plage nommée donnée en exemple comme ceci :

Code :

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

=!$AC$12;!$AC$18;!$AC$24;!$AC$30

(les points d'exclamation sont importants), le nom de la feuille deviendra relatif. Ça simplifie peut-être ton problème...

[Ieriamus]

Philippe Tulliez et zenpbb déjà merci de m'avoir répondu,

J'ai lu votre publication sur l'utilisation du codename d'une feuille. Je ne pense pas que cela me servira car les noms des feuilles ne seront pas modifiés.

Il est vrai que ma demande nécessite plus de détails.

Imaginons mon classeur Blanche-Neige et les feuilles Atchoum, simplet, prof, dormeur, ...etc
Chaque feuille doit posséder une plage nommée "Mineur" et comprends une trentaine de cellules. Donc dans le gestionnaire de noms, j'ai MINEUR=AtchoumA1; Atchoum A2; ... ; Atchoum A30 sur la feuille Atchoum et pareil sur les autres feuilles.
Seulement, je dois enlever disons 2 cellules à ces plages nommées MINEUR pour chaque feuille. Et c'est là que le gestionnaire de noms n'est pas pratique.
Donc, mon idée était de copier à partir du gestionnaire de noms la ligne et la coller dans une cellule sur une feuille vierge. Supprimer les deux cellules et créer une macro qui remplace (ou supprime-crée) la plage nommée Mineur par ce qui se trouve dans ma cellule sur la feuille vierge. MINEUR=ViergeA1; Vierge A2; ...
Ensuite, faire de m^me pour toutes les feuilles en remplaçant d'abord le terme Vierge par le nom de la feuille active: Atchoum, Dormeur et les autres...

si je reprends ma macro qui me servait à tester la fonction remplace,

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
Option Explicit
Dim cible As range
Dim nom As String
dim add as string
 
Sub gestion_nom()
 
cible = ThisWorkbook.Sheets("Vierge").Range("A1") 'où j'avais collé une ref du gestionnaire de nom : Vierge!$G$2:$X$3;Vierge!$C$10;Vierge!$C$12;Vierge!$D$10
nom = ActiveSheet.Names
cible.Replace what:="Vierge", replacement:=nom
 
Add = "=" & cible
ThisWorkbook.Names.Add Name:="MINEUR", RefersTo:=add
 
End Sub

[Philippe Tulliez]

Bonjour,
Dans ce cas évidemment il s'agit de nom à portée feuille.

Voici un exemple d'une procédure générique qui crée un nom de portée feuille en faisant référence aux cellules dont les adresses sont passées en argument.
J'ai bien entendu effectué le test par rapport à mon exemple mais je ne suis pas convaincu que le nombre de références que vous indiquez dans votre demande passera car si mes souvenirs sont bons, il y a une limite du nombre de caractères à passer en référence

Code de la procédure

Code :

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

1
2
3
4
5
6
7
8
9
10
11
Sub AddName(oWorksheet As Worksheet, rngAddress As String, rngName As String)
  Dim tbl() As String
  Dim NewAddress As String
  Dim a As Integer
  tbl = Split(rngAddress, ",")
  For a = 0 To UBound(tbl)
    NewAddress = NewAddress & oWorksheet.Name & "!" & tbl(a) & ","
  Next
  NewAddress = "=" & Left(NewAddress, Len(NewAddress) - 1)
  oWorksheet.Names.Add Name:=rngName, RefersTo:=NewAddress
End Sub

Exemple d'une procédure qui l'invoque

Code :

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

1
2
3
Sub TestAddName()
  AddName ThisWorkbook.Worksheets("Feuil4"), "$A$2,$E$2:$H$5,$G$10:$J$15", "Mineur"
End Sub

J'ai lu votre publication sur l'utilisation du codename d'une feuille. Je ne pense pas que cela me servira car les noms des feuilles ne seront pas modifiés.

Si vous en êtes convaincu, j'en suis heureux pour vous

Seulement, je dois enlever disons 2 cellules à ces plages nommées MINEUR pour chaque feuille. Et c'est là que le gestionnaire de noms n'est pas pratique.

Le gestionnaire des noms est très pratique mais peut-être pas de la manière dont vous l'utilisez.