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

dionys0s

XLDnaute Impliqué
Bonjour tout le monde ^^

voici mon soucis
Cette formule
Code:
=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...

Code:
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 ^_^
 

Pièces jointes

  • TCD (corrigé).xlsm
    133.2 KB · Affichages: 69
G

Guest

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:
Code:
 =SOMME(Onglet01_01[Salaire])
pour avoir le total des Salaires.

Pour utiliser une colonne en VBA:
Code:
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

XLDnaute Impliqué
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

XLDnaute Impliqué
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
 
G

Guest

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.

Code:
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+