Macro pour définir automatiquement les noms des plages sur plusieurs colonnes

[zibouf]

Bonjour à tous,

Après avoir parcouru mon ami google et les forums excel download je ne trouve malheureusement pas de solution à mon problème.

Je souhaiterai, par le biais d'une macro, automatiser la tâche "définir nom de plage" pour plusieurs colonnes.
Je m'explique, sur une bdd (cf fichier joint), comportant plusieurs colonnes (dans mon fichiers définitif, près de 80 ), je dois définir le nom de TOUTES les colonnes via la fonction:
=DECALER('BDD clients'!$B$1;1;0;NBVAL('BDD clients'!$A:$A)-1;1).

Travail très long et très laborieux qui inévitablement comportera très probablement des erreurs de saisies de ma part...


Ma question est donc simple (à poser), mais à résoudre, j'avoue que je sèche...

Clairement, je souhaite créer une macro qui automatisera cette tâche de "définir un nom" sur toutes mes colonnes, cette macro devant utiliser la fonction "décaler" comme indiquée plus haut... car le nombre de lignes de ma bdd augmentera au fur et à mesure, raison pour laquelle mes plages doivent comptabiliser toutes les lignes des colonnes nommées)

Point important, cette macro doit également se nommer automatiquement en reprenant l'entête correspondante...

En bref, une macro qui fait tout... lol

J'ai bien trouver sur le forum, une macro s'approchant de celà,

Sub déf_nom()
Dim Var As Range, i As Integer, NomZone As String
On Error Resume Next
Set Var = Application.InputBox("Sélectionner votre zone: (Ex. A1:B10) ", _
"Sélection de zone ", Default:="$A$1", Type:=8)
On Error GoTo 0
If Not Var Is Nothing Then
MsgBox Var.Address
Var.Select
Else
Exit Sub
End If
NomZone = InputBox("Entrer le Nom de la plage", "Nommer la plage :")
If NomZone <> "" Then
ActiveWorkbook.Names.Add Name:=NomZone, RefersTo:="='" & ActiveSheet.Name & "'!" & Var.Address
If Var.Columns.Count = 1 Then Exit Sub
For i = 1 To Var.Columns.Count
ActiveWorkbook.Names.Add Name:=NomZone & "Col" & i, RefersTo:="='" & ActiveSheet.Name & "'!" & Var.Columns(i).Address
Next
Else
Exit Sub
End If
End Sub

Malheureusement elle ne prend pas en compte la fameuse fonction "decaler", que je souhaite, et de plus n'automatise pas non plus le nom de la plage avec l'entête correspondante...

J'avoue être tout à fait novice dans la programmation de macros...


Espérant avoir été clair.

Merci beaucoup pour votre aide.

Cordialement.

 

[Dranreb]

Re : Macro pour définir automatiquement les noms des plages sur plusieurs colonnes

Bonsoir
Cette procédure marche pas mal :

Sub NomsDynamiques()
Dim Cel As Range, Nom As String, Réf As String
For Each Cel In ActiveSheet.Rows(1).Cells
   Nom = Replace(Cel.Value, " ", "_")
   If Nom <> "" Then
      Réf = "=OFFSET(" & Cel.Address(True, True, xlA1, True) & ",1,0,COUNTA(" & ActiveSheet.Columns(1).Address(True, True, xlA1, True) & ")-1,1)"
      On Error Resume Next
      ActiveWorkbook.Names.Add Nom, Réf
      If Err Then MsgBox "Impossible de nommer """ & Nom & """ la réf:" & vbLf & Réf, vbCritical, "Noms dynamiques"
      On Error GoTo 0
      End If
   Next Cel
End Sub

Cordialement.

 

[zibouf]

Re : Macro pour définir automatiquement les noms des plages sur plusieurs colonnes

Bonsoir et merci pour votre réponse rapide.

Que vous dire sinon un grand merci. La macro fonctionne impeccablement.J'avais juste un problème dans les titres de mes colonnes. En effet, la macro ne prend pas en compte les apostrophes! Il m'a suffit de renommer mes entêtes sans apostrophes et le tour est joué.

Merci encore

Cordialement

 

[zibouf]

Re : Macro pour définir automatiquement les noms des plages sur plusieurs colonnes

Re bonsoir dranreb,

juste une dernière chose !
Est il possible que cette même macro rajoute le nom de la feuille en plus, lorsqu'elle nomme la plage?
Je m'explique, j'ai plusieurs feuilles (1,2,3 etc) et les entêtes sont identiques pour toutes les feuilles, hors si j'applique la macro a chaque feuille, elle remplace les définitions de plages des feuilles précédentes car les entêtes sont identiques. J'ai donc penser à ce que la macro rajoute en plus a chaque fois le nom de la feuille en cours, lorsqu'elle définie les noms des plages via les entêtes correspondantes.

Ainsi pour une entête "NOM", la macro définie la plage en "NOM_Feuil1" si Feuille 1 ou "NOM_Feuil2" si Feuille 2...
Que dois-je changer dans la macro, car je suis sur que ce n'est pas grand chose à faire, mais après plusieurs essais, je me résigne...

Merci encore pour votre aide très précieuse...

Cordialement.

 

[Dranreb]

Re : Macro pour définir automatiquement les noms des plages sur plusieurs colonnes

Remplacez ActiveWorkbook par ActiveSheet, ça devrait aller: le nom devrait être ajouté au niveau de la feuille ce qui revient à l'ajouter au classeur muni du préfixe de la feuille.
Remarque: la syntaxe pour s'y référer dans une formule depuis une autre feuille sera: Feuil1!Nom
Pour des formules dans la même feuille le préfixe est inutile.
Cordialement.

 

[zibouf]

Re : Macro pour définir automatiquement les noms des plages sur plusieurs colonnes

Merci de votre réponse,

j'ai bien remplacé work par active comme vous me l'avez suggéré, mais aucun changement. Le nom de plage reste identique et n'intègre apparemment pas le nom de la feuille courante.

Cordialement.

 

[Dranreb]

Re : Macro pour définir automatiquement les noms des plages sur plusieurs colonnes

J'ai fait l'essai ça marche comme je l'entendais avec cette procédure:

Sub NomsDynamiques()
Dim Cel As Range, Nom As String, Réf As String
For Each Cel In ActiveSheet.Rows(1).Cells
   Nom = Replace(Cel.Value, " ", "_")
   If Nom <> "" Then
      Réf = "=OFFSET(" & Cel.Address(True, True, xlA1, True) & ",1,0,COUNTA(" & ActiveSheet.Columns(1).Address(True, True, xlA1, True) & ")-1,1)"
      On Error Resume Next
      ActiveSheet.Names.Add Nom, Réf
      If Err Then MsgBox "Impossible de nommer """ & Nom & """ la réf:" & vbLf & Réf, vbCritical, "Noms dynamiques"
      On Error GoTo 0
      End If
   Next Cel
End Sub

Le nom est bien ajouté au niveau de la feuille, où il y est connu sans changement spécial, d'accord. Il n'est du coup aussi reconnu dans le reste du classeur qu'à condition de le faire précéder du nom de la feuille suivi d'un "!". Et comme ils sont différenciés au niveau du classeur par ce préfixe de feuille, il peut y avoir dès lors plusieurs fois le même nom dans des feuilles différentes.
Remarque: Excel lui même utilise beaucoup les noms définis seulement au niveau des feuilles: ce sont pour toutes les feuilles le même nom pour la zone d'impression, les lignes à répéter sur chaque page et la plage couverte par un filtre automatique.
On pourrait toutefois se référer dans une formule à de tels élément d'une autre feuille à condition de la préciser devant avec "!"
À +

 

[zibouf]

Re : Macro pour définir automatiquement les noms des plages sur plusieurs colonnes

Bonjour,

Merci encore pour toutes ces explications.

Je devrai pouvoir m'en sortir désormais...et me débrouiller du reste.

Bonne continuation

Sincèrement

 

[Guest]

Re : Macro pour définir automatiquement les noms des plages sur plusieurs colonnes

Bonjour,

Puisque tu es sous 2007 tu peux utiliser les "Tableaux excel" onglet du rubban "insertion-tableau" en ayant sélectionner une cellule du tableau au préalable, sur le même onglet tu peux le nommer 'Datas'. Ensuite tu pourras utiliser les "références structurées"

Par formules: =NB.SI(Datas[Civilité Client];"Monsieur")

Dans VBA: = Range("Datas[Civilité Client]").Address

Voir dans la faq les wiki de Michel XLD sur les tableaux et références structurées.

A+