VBA : Générer les noms des plages dynamiques automatiquement

[dionys0s]

Bonjour tout le monde ^^

voici mon soucis
Cette formule

=DECALER('NomFeuil'!$A$1;1;0;NBVAL('NomFeuil'!$A:$A)-1;1)

rentrée dans la case 'fait référence à" du gestionnaire de noms (onglet formule) permet de nommer des plages dynamiques. C'est à dire que l'ajout d'un item en bas de la liste mettra la plage à jour. Super pratique à manier en VBA et dans les formules compliquées, pour optimiser la mémoire etc...

Donc là j'ai un fichier avec plein de tableaux, de colonnes à nommer, et je souhaiterais automatiser le nommage des colonnes : Onglet01_01 (colonne 1) ; Onglet01_02 (colonne 2) etc...

Sub Ajouter_Noms()

Dim Largeur As Byte
Dim Feuil As Byte
Dim Col As Byte
For Feuil = 1 To ThisWorkbook.Worksheets.Count
    Sheets(Feuil).Activate
    Largeur = InputBox("Quelle est la largeur du tableau ?", "Nombre de colonnes")
    For Col = 1 To Largeur
        If Col < 10 Then
        ActiveWorkbook.Names.Add Name:="Onglet0" & Feuil & "_0" & Col, RefersToR1C1:= _
        "=OFFSET(" & ActiveSheet.Name & "!R1C1,1," & Col - 1 & ",COUNTA(" & ActiveSheet.Name & "!C1)-1,1)"
        Else
        ActiveWorkbook.Names.Add Name:="Onglet" & Feuil & "_" & Col, RefersToR1C1:= _
        "=OFFSET(" & ActiveSheet.Name & "!R1C1,1," & Col - 1 & ",COUNTA(" & ActiveSheet.Name & "!C1)-1,1)"
        End If
    Next Col
Next Feuil

End Sub

En théorie ce code devrait fonctionner. Je ne comprends pas d'où peut venir l'erreur. J'ai fait la formule grâce à l'enregistreur, puis modifiée comme il se doit... Comprends pas.

Si l'un de vous a une idée c'est top. J'espère avoir été clair.
En pièce jointe mon fichier.

Thanks a lot ^_^

 

[Guest]

Re : VBA : Générer les noms des plages dynamiques automatiquement

Bonjour

Puisque tu es sous 2007, tu peux utiliser les tableaux structuré et utiliser leurs références dans les formules et ou vba.

Par exemple pour ton premier onglet:
cliquer dans une cellule du tableau. Onglet de ruban "Insertion"/tableau. Un nouvel onglet de ruban se crée nommé: Outils de tableau dans le sous onglet "Création" -> nom du tableau: Onglet01_01

Dans une cellule tu peux taper la formule:

 =SOMME(Onglet01_01[Salaire])

pour avoir le total des Salaires.

Pour utiliser une colonne en VBA:

Dim MaColonne as Range
Set MaColonne = Range("Onglet01_01[Salaire]")

Pour plus de détails sur les tableaux et références structurées voir la faq.

A+

 

[dionys0s]

Re : VBA : Générer les noms des plages dynamiques automatiquement

Bonjour Hasco

Merci beaucoup pour cette réponse qui m'ouvre tout plein d'horizons.

Ceci dit, j'aimerais y arriver avec ma méthode, et pouvoir tout renommer selon une trame fixée dans le code (cf premier post) en ne renseignant que les largeur de tableau (inputbox)

Any idea someone ?

D'avance merci

 

[dionys0s]

Re : VBA : Générer les noms des plages dynamiques automatiquement

Bonjour Hasco

Merci beaucoup pour cette réponse qui m'ouvre tout plein d'horizons.

Ceci dit, j'aimerais y arriver avec ma méthode, et pouvoir tout renommer selon une trame fixée dans le code (cf premier post) en ne renseignant que les largeur de tableau (inputbox)

Any idea someone ?

D'avance merci

 

[Guest]

Re : VBA : Générer les noms des plages dynamiques automatiquement

bonjour,

Pour le nommage des colonnes: le nom de la première colonne calculera le nombre de lignes du tableau ensuite les autres seront simplement un décalage de la première.

Sub Ajouter_Noms()
    Dim Feuil As Integer
    Dim Largeur As Variant
    Dim Col As Long
    Dim Formule1 As String, BaseNom As String
    For Feuil = 1 To ThisWorkbook.Worksheets.Count
        With Sheets(Feuil)
            .Activate
            Largeur = InputBox("Quelle est la largeur du tableau de la feuille", "Création des noms: largeur du tableau", 0)
            If IsNumeric(Largeur) Then
                BaseNom = "onglet" & Format(Feuil, "00") & "_"
                Application.Names.Add BaseNom & "01", Replace("=OFFSET('NomFeuille'!$A$1,1,0,COUNTA('NomFeuille'!$A:$A)-1,1)", "NomFeuille", .Name)
                For Col = 2 To CLng(Int(Largeur))
                    Application.Names.Add BaseNom & Format(Col, "00"), "=OFFSET(" & BaseNom & "01,0," & Col - 1 & ")"
                Next Col
            End If
        End With
    Next Feuil
End Sub

A+

 

[dionys0s]

Re : VBA : Générer les noms des plages dynamiques automatiquement

Superbe. Merci beaucoup Hasco. Tu sais ce qui plantait dans mon code ?

 

[Guest]

Re : VBA : Générer les noms des plages dynamiques automatiquement

Re,

Je te dirait d'aller voir le fil de ce post https://www.excel-downloads.com/threads/nommer-un-onglet-en-fonction-du-contenu-dune-cellule.177312/
Afin que tu puisses, par toi-même voir ce qui ne va pas dans ta formule après l'avoir mise dans une variable string.

A+