XL 2013 Modifier noms de plage par lots

Kostrovit · 17 Décembre 2023

Bonjour,
J'ai essayé plusieurs choses trouvées sur Internet mais qui ne fonctionnent pas dans ma situation ou pour ma version. Voici donc la situation.
Dans un projet de planning d'équipe, j'ai eu recours à la création de séries de noms de cellule ou de plage pour gérer des listes d'agents présents ou des lignes d'horaire dans le but de créer des listes déroulantes dynamiques.
Cela donne des séries comme :
Agents : P_Mar_Mat1,P_Mart_Mat2,etc. jusqu'à 37 (2 séries par demi-journées pour chaque jour)
horaires : H_Mar1,H_Mar2, etc. jusqu'à 13, pour chaque jour (la formule fait que lorsqu'on choisit un agent sur un horaire, il disparaît de la liste déroulante pour les autres postes du même horaire, reconnaissance éternelle à feu M. Boisgontier)
Il y avait peut-être une méthode plus simple mais j'ai fait avec mes compétences limitées. J'en profite pour remercier tous les experts de ce forum à qui j'ai emprunté des astuces pour les adapter à mon projet, qui fonctionne depuis 3 ans maintenant. J'envisage de vous le soumettre un jour pour savoir s'il y aurait d'autres façons de concevoir les choses, mais je ne sais pas si ça se fait.

J'en viens à mon besoin. Le projet fonctionne avec les noms que j'ai créés au départ, mais au moment où j'effectue quelques améliorations, je me rends compte que j'ai mis le numéro de chaque série à la fin alors qu'il se rapporte souvent au préfixe. Pour rendre mon projet plus élégant et lisible, j'aimerais donc pouvoir modifier comme suit :
P_Mar_Mat1 en P1_Mar_Mat (P correspond à l'agent présent)
P_Mart_Mat2 en P2_Mar_Mat
H_Mar1 en H1_Mar
H_Mar2 en H2_Mar

Je précise que ces noms ont tous une étendue classeur. Le sujet n'est pas seulement esthétique. Pour créer mes listes déroulantes, j'ai une liste par ligne horaire pour chaque jour. Quand je modifie la formule, les recopies de formules entrainent des corrections manuelles nombreuses sur les noms qui ne suivent pas l'incrémentation. Je suis donc en train d'utiliser la fonction indirect pour reproduire plus rapidement mes modifications de formule sur les 36 lignes suivantes. Je préfère donc changer mes noms avant cette opération.

Voici enfin ma question : existe-t-il une méthode et une fonction qui permettent de modifier ces noms par lots ? C'est à dire qu'il faut d'abord les sélectionner par leur partie identique et ensuite opérer la modification expliquée plus haut.

En espérant avoir été assez clair et en vous remerciant par avance de vos lumières.
Bien à vous
Kostrovit

Kostrovit · 18 Décembre 2023

Bonsoir,
J'ai finalement trouvé ailleurs sur internet (site excelcorpo) un code que j'ai bien pu adapter à mon cas. Le voici pour celles ou ceux que cela aiderait :

VB:

Sub ModifierNomPlageP2Apm()
Dim zoneNom As Object
Dim VglNom As String
Dim numero As String

For Each zoneNom In ThisWorkbook.Names
    VglNom = zoneNom.Name
    numero = Right(VglNom, 2)
    
    If InStr(VglNom, "P_Mer_Apm") > 0 Then
        VglNom = WorksheetFunction.Substitute(VglNom, "P_Mer_Apm" & numero, "P" & numero & "_Mer_Apm")
        zoneNom.Name = VglNom
    End If
Next zoneNom

End Sub

Attention ! Dans mon cas, quand il y a des numéros avant 10, il faut modifier la valeur du second argument dans la fonction Right().
La fonction InStr() était vraiment...

Phil69970 · 17 Décembre 2023

Bonjour @Kostrovit

Je te propose ce code à mettre dans un module et à appliquer une seule fois bien sur

VB:

Sub Transforme()
Dim i As Byte, Nm As Object
i = 1
For Each Nm In ActiveWorkbook.Names
    'ActiveSheet.Cells(i, 2).Value = Nm.Name 'Nomn avant pour controle
    Nm.Name = Left(Nm.Name, Len(Nm.Name) - 1)
    Nm.Name = Left(Nm.Name, 1) & 1 & Right(Nm.Name, Len(Nm.Name) - 1)
    'ActiveSheet.Cells(i, 1).Value = Nm.Name  'Nomn apres pour controle
    'i = i + 1
Next
End Sub

Tu peux tester sur une feuille vierge en activant les 3 lignes qui sont déactivées

Merci de ton retour

Phil69970 · 17 Décembre 2023

Re

Et en plus condensé

VB:

Sub Transforme()
Dim Nm As Object
For Each Nm In ActiveWorkbook.Names
    Nm.Name = Left(Left(Nm.Name, 1) & 1 & Right(Nm.Name, Len(Nm.Name) - 1), Len(Left(Nm.Name, 1) & 1 & Right(Nm.Name, Len(Nm.Name) - 1)) - 1)
Next
End Sub

Merci de ton retour

Kostrovit · 17 Décembre 2023

Bonjour,
Merci de votre réponse. J'ai fait le test. Ca fonctionne en partie. En fait, je ne veux pas changer l'intégralité des noms de mon projet. D'autres séries sont correctement nommées. Je mets en pièce jointe la liste de tous les noms utilisés.
Ce sont uniquement les séries avec préfixe P_ ou H_, ou A_ que je veux modifier en faisant passer le numéro qui se trouve à la fin, juste après le préfixe. Je n'ai pas vu la fin de la macro car il y a eu un dépassement de capacité. Donc des 1 sont bien apparus après chaque préfixe mais les numéros finaux n'ont pas disparu.

Je sens bien que mon truc peut devenir compliqué car pour d'autres séries :
Celle avec préfixe P_H : le numéro doit se déplacer après le H
Celle avec d'autres préfixes c'est un déplacement des trois premières lettres juste avant le suffixe qui serait utile.

Mais il me vient une idée quand je vois le fichier que je vous transmets. Dans cette feuille excel qui liste l'ensemble des noms, je pourrais mettre dans la colonne C les noms que je souhaite substituer aux noms originaux situés en colonne A. La macro à construire viserait ainsi à dire à excel : remplace le nom dans A1 par celui en C1. Quand je ne veux pas changer le nom, je mets exactement le même qu'en A1. Ce serait plus rapide pour moi je pense de faire ça dans un tableau excel que dans la fenêtre de gestion des noms. Et la macro ne serait pas ainsi plus simple ?

Qu'en pensez-vous ?

Phil69970 · 17 Décembre 2023

@Kostrovit

Remarques :

1) Ma macro fonctionne correctement par rapport à l'énoncé du post #1, a savoir tu n'as pas précisé qu'il y avait d'autres noms dans le gestionnaire de noms.

2) Quand je vois que tu as 1250 noms dans le gestionnaire de noms cela fait beaucoup non !

3) Avec le code de mon post # 2 tu as tous ce qu'il faut pour faire ce que tu décris une colonne A avec le nom actuel et colonne B avec le nom que tu voudrais dans une boucle

Kostrovit · 17 Décembre 2023

Bonsoir,
Oui, il y a sans doute trop de noms, au cours de ma mise à jour ce nombre devrait diminuer. Avec un peu plus d'expertise en VBA, il y a sans doute moyen de faire autrement. Mais je fais avec ce que j'ai et tant que ça fonctionne sans trop de lenteur.
J'ai essayé un code en m'inspirant de ton poste #2, mais ça coince et je ne sais pas pourquoi. J'ai essayé des variantes sans succès.
Je n'ai modifié dans la colonne B que les lignes 94 à 102 par rapport à la liste de noms d'origine, pour voir.

VB:

Sub Transforme()
Dim l As Byte, Nm As Object
l = 1
  For Each Nm In ActiveWorkbook.Names
       ActiveSheet.Cells(l, 1).Value = Nm.Name 'Nomn avant pour contrôle
       Nm.Name = ActiveSheet.Cells(l, 2).Value
       ActiveSheet.Cells(l, 3).Value = Nm.Name 'Nomn après pour  contrôle
       l = l + 1
  Next

End Sub

J'obtiens un message d'erreur "La méthode name de l'objet name a échoué".
Je mets la feuille qui contient le résultat.
Bien cordialement

Kostrovit · 18 Décembre 2023

Bonsoir,
J'ai finalement trouvé ailleurs sur internet (site excelcorpo) un code que j'ai bien pu adapter à mon cas. Le voici pour celles ou ceux que cela aiderait :

VB:

Sub ModifierNomPlageP2Apm()
Dim zoneNom As Object
Dim VglNom As String
Dim numero As String

For Each zoneNom In ThisWorkbook.Names
    VglNom = zoneNom.Name
    numero = Right(VglNom, 2)
    
    If InStr(VglNom, "P_Mer_Apm") > 0 Then
        VglNom = WorksheetFunction.Substitute(VglNom, "P_Mer_Apm" & numero, "P" & numero & "_Mer_Apm")
        zoneNom.Name = VglNom
    End If
Next zoneNom

End Sub

Attention ! Dans mon cas, quand il y a des numéros avant 10, il faut modifier la valeur du second argument dans la fonction Right().
La fonction InStr() était vraiment indispensable pour sélectionner des noms quand le gestionnaire de noms contient des séries différentes. En adaptant les concaténations dans l'expression "texte", ça marche bien.
Merci

XL 2013 Modifier noms de plage par lots

Kostrovit

XLDnaute Nouveau

Kostrovit

Phil69970

XLDnaute Barbatruc

Phil69970

XLDnaute Barbatruc

Kostrovit

XLDnaute Nouveau

Pièces jointes

Phil69970

XLDnaute Barbatruc

Kostrovit

XLDnaute Nouveau

Pièces jointes

Kostrovit

XLDnaute Nouveau

Discussions similaires