Nommage de x plages dynamiques en automatique (vba)

[Olya]

Bonjour

Merci de bien vouloir me proposer votre solution ...voici mon petit souci:

je souhaites nommer toutes mes colonnes (nombre variable) / chacune avec le nom se trouvant dans la ligne d'enête.. le nombre de lignes pour toutes les colonnes et variable aussi. et d'habitude en manuel j'utilise la formule suivante qui tient compte des lignes vides à l'interieur de la plage elle même:

=DECALER(Feuil1!$B$15;;;NBVAL(Feuil1!$B$15:$B$65536)+NB.VIDE(DECALER(Feuil1!$B$15;;;NBVAL(Feuil1!$B$15:$B$65536))))

comment peut-on boucler sur la ligne d'êntête, récuperer les noms et adapter la formule pour chacune des colonnes?
ainsi une macro fera le nommage ( en se servant des noms récuperés sur la ligne d'entête) de manière automatique et dynamique

Merci pour le temps passé..

 

[Guest]

Re : Nommage de x plages dynamiques en automatique (vba)

bonjour,

voir fichier joint. Ai remplacer la fonction NB.VIDE + DECALER qui ne peut fonctionner correctement (colonne bobo)

Sub NommerColonnes(Entêtes As Range)
    'pour renvoyer dernière ligne:
    '1 - Formule avec matricielle MAX(SI.....
    Const BaseFormule As String = "=OFFSET([EMAIL="&!@,,,MAX(IF(&!@:#<>"""",ROW"]&!@,,,MAX(IF(&!@:#<>"""",ROW[/EMAIL](&!@:#))))"
    '2 - Formule avec equiv pour données Chaine de caractère dans colonne
    'Const BaseFormule As String = "=OFFSET([EMAIL="&!@,,,MATCH(""ùùùùùù"",&!@:#,1"]&!@,,,MATCH(""ùùùùùù"",&!@:#,1[/EMAIL]))"
    '3 - Formule avec equiv pour données numériques dans colonnes
    'Const BaseFormule As String = "=OFFSET([EMAIL="&!@,,,MATCH(9^9,&!@:#,1"]&!@,,,MATCH(9^9,&!@:#,1[/EMAIL]))"
    
    'Elements qui seront remplacés dans la formule nommée:
    ' & = Nom de la feuille
    ' @ = Adresse de la première cellule à prendre en considération
    ' # = Adresse de la dernière cellule à prendre en considération
    
    Dim c As Range, adr1 As String, adr2 As String
    If Entêtes.Rows.Count > 1 Or Entêtes.Areas.Count > 1 Then
        MsgBox "Le paramètre 'Entêtes' doit correspondre à une seule ligne de cellules contigues", vbExclamation, "Nommer colonnes"
        Exit Sub
    End If
    For Each c In Entêtes
        If Not IsEmpty(c) Then
            With c.Parent
                adr1 = c(2).Address
                adr2 = .Cells(Rows.Count, c.Column).Address
                Application.Names.Add Replace(c, " ", "_"), Replace(Replace(Replace(BaseFormule, "#", adr2), "@", adr1), "&", .Name)
            End With
        End If
    Next
End Sub

A+

 

[Dranreb]

Re : Nommage de x plages dynamiques en automatique (vba)

Bonjour.
Soit à créer ces nom à l'activation d'une autre feuille, et si j'ai bien compris, dans le module de la feuille :

Option Explicit

Private Sub Worksheet_Deactivate()
Dim Col As Range
On Error Resume Next
For Each Col In PlgUti(Me.[B15]).Columns
   Col.Name = Col.Rows(0).Value: Next Col
End Sub
'

Function PlgUti(ByVal PlageDép As Range, Optional ByVal PlagExam As Range = Nothing) As Range
Rem. Cherche la partie utilisée d'une plage, c'est à dire celle qui s'étend jusqu'à la dernière cellule renseignée de plus qu'une chaîne vide.
'    PlageDép: Plage de départ. Seule la 1ère cellule spécifiée est prise en compte pour la déterminer, et suffit donc pour une utilisation VBA.
'            Pour utilisation en formules, il est néanmoins nécessaire de spécifier la plage depuis celle ci jusqu'au reste des colonnes entières.
'    PlageExam: Plus grande plage susceptible de contenir la plage cherchée.
'            Facultatif: UsedRange assumé par défaut.
Dim LMax As Long, CMax As Long, NbL As Long, NbC As Long
On Error GoTo RienTrouvé
If PlagExam Is Nothing Then Set PlagExam = PlageDép.Worksheet.UsedRange
LMax = PlagExam.Find("*", PlagExam.Cells(1, 1), xlValues, xlWhole, xlByRows, xlPrevious).Row
CMax = PlagExam.Find("*", PlagExam.Cells(1, 1), xlValues, xlWhole, xlByColumns, xlPrevious).Column
On Error GoTo 0
NbL = LMax - PlageDép.Row + 1: If NbL < 1 Then GoTo CEstToutVide
NbC = CMax - PlageDép.Column + 1: If NbC < 1 Then GoTo CEstToutVide
Set PlgUti = PlageDép.Resize(NbL, NbC)
Exit Function
RienTrouvé: Resume CEstToutVide
CEstToutVide: Set PlgUti = Nothing
End Function

 

[Olya]

Re : Nommage de x plages dynamiques en automatique (vba)

Bonjour Hasco, Dranreb

Merci à vous deux vos macros sont sublimes et je suis certaine que ça rendra service à plusieurs utilisateurs… voici mon retour pour chacune des deux macros qui sont par ailleurs bien commentées :

Hasco :
Je ne connaissais pas le nommage avec Max et ligne comme tu le fais.. Je pense que je vais l’adopter ( qu’en penses-tu ?). Ca c’est un bon remède contre les cellules vides.
Concernant ma formule, j’ai vu aussi que tu as rajouté la fonction lignes au début, du coup, elle semble fonctionner aussi mais je ne sais pas ce qu’elle vaut (qu’en penses tu ? ou sont ses limites ?)
Je trouvais ta macro franchement super car elle gère les espaces vides lorsque le nom de la colonne est composé de 2 mots et même en début de mots (Bravo).

Cependant j’ai quelques questions si tu as le temps de me répondre bien sûr,
1) Après nommage par macro, si on modifie un nom dans la ligne d’entête (en rajoutant un (s) par exemple. et si on relance la macro une seconde fois on se retrouve avec deux noms et du coup si le nom était utilisé dans une formule .j’ai l’erreur Réf. comment peut –on donc, faire un raz de ce qui a été créé par la macro ( et uniquement ça) et repartir sur une liste propre tout en veillant à que les formules fonctionnent. (un peu tordu mon explication peut-être)

2) Les variable #,@,& il ne faut pas les déclarer ? sinon comment ?

Dranreb : ta macro est tout aussi intéressante et fonctionne un peu différemment… cependant, est-il possible de la mettre sur un événement change (en fonction de la dernière ligne utilisé toutes colonnes confondues ?)
Aussi, j’ai cru remarqué qu’elle ne gère pas les espaces vides en début et au milieu des noms composés. Ainsi ( metier avec un espace devant le (m) n’est pas pris en compte). De même une colonne nommée (ville rose) ne sera carrément pas nommée.
Comment peut-on améliorer l’ensemble en tenant compte de ce que je disais à Hasco également, c'est-à-dire en cas de modifications des noms et incidences sur les formules utilisants les noms prédefinis).

Encore Merci à vous deux

A vos lire .

 

[Dranreb]

Re : Nommage de x plages dynamiques en automatique (vba)

Des noms valides ne doivent pas comporter d'espace. Pour augmenter les chances que le nom puisse être accepté, mettez :

Col.Name = Replace(Trim$(Col.Rows(0).Value), " ", "_")

Je ne vois pas d'inconvénient à mettre ça dans une Worksheet_Change, mais ça n'a d’intérêt que si les noms sont aussi utilisés dans la feuille elle même.

 

[Guest]

Re : Nommage de x plages dynamiques en automatique (vba)

Bonjour Olya
Hello Bernard

Concernant ma formule, j’ai vu aussi que tu as rajouté la fonction lignes au début, du coup, elle semble fonctionner aussi mais je ne sais pas ce qu’elle vaut (qu’en penses tu ? ou sont ses limites ?)

Tu parles de la formule en L2 (j'ai eu du mal à savoir de quoi tu parlais)arf....
Ce n'était qu'un test de contrôle

Les variable #,@,& il ne faut pas les déclarer ? sinon comment ?

Ce ne sont pas des variables au sens propre, ce sont simplement des caractères à remplacer par le nom de la feuille, l'adresse de la cellule de départ, et l'adresse de la cellule d'arrivée.

Quant à l'aspect dynamique de tout cela, je ne me lancerai pas là dedans ici, non pas que ce soit impossible à priori, mais plutôt que c'est le genre de chose qui termine en usine à gaz.

Déjà que je trouve un peu lourd le MAX(SI(Feuil1!$F$15:$F$65536<>"";LIGNE(Feuil1!$F$15:$F$65536)) dans la formule! C' est susceptible de produire à l'usage des ralentissements dans les calculs. Remplacer le 65536 par 1000 serait déjà mieux.

C'est pour cela que j'ai laissé les modèle avec EQUIV en commentaire.

Mais ne te décourage pas et essaie de penser ton projet différement.
A+

 

[Olya]

Re : Nommage de x plages dynamiques en automatique (vba)

Bonjour Hasco, Dranreb


Merci à vous deux.. vos solutions après divers tests.. font bien mon affaire..
Merci pour vos explications.

au plaisir