Nommer une plage dynamique en VBA

Version imprimable

Bonjour le forum,

J'ai bien l'impression d'être fatigué, mais rien y fait, je suis en train de perdre mon temps pour une erreur qui sautera à vos yeux experts.
J'essaye de nommer une plage dynamique après la création d'une feuille avec une formule de type DECALER. Apparemment, ça ne plait pas trop à excel qui me dit que ma formule contient une erreur, J'ai beau chercher, je ne trouve pas, un idée?
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 Dim Prgm As String Dim Wb As Workbook Dim D As Integer Set Wb = ActiveWorkbook Prgm = InputBox("Quel programme souhaitez-vous créer?") Sheets.Add After:=Sheets(Sheets.Count) ActiveSheet.Name = Prgm ActiveCell.FormulaR1C1 = "Liste des activités liées à " & Prgm Wb.Names.Add Name:=Prgm, RefersTo:= _ "=DECALER('" & Prgm & "'!$A$1;1;0;NBVAL('" & Prgm & "'!$A:$A)-1)"

06/05/2013, 19h00
NVCfrm

bonjour,

essaye d'avoir la syntaxe correcte de ta formule avec l'enregistreur macro.
07/05/2013, 09h17
banane87

Bonjour NVCfrm,

C'est pourant bien ce que j'ai fait. Quand je teste l'expression obtenue par la ligne

Code:

"=DECALER('" & Prgm & "'!$A$1;1;0;NBVAL('" & Prgm & "'!$A:$A)-1)"

en faisant une Msgbox, l'expression me semble correcte. Est-ce que je fais une bonne utilisation des opérandes de concaténation?

Merci pour votre aide.

Banane

Rebonjour le forum,

Il fallait enlever le "=" dans le code de ma formule pour empêcher l'erreur!
Cependant, un problème persiste. Quand le programme nomme ma plage, la valeur est mise entre guillemets de cette manière :

Citation:

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

Comment retirer les guillemets avant Decaler et après la parenthèse?

Je vous remets le code corrigé :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
Option Explicit
 
Sub Creer_Prgm()
'
' Macro de création d'un programme
'Dim ws As Worksheet
Dim Prgm As String
Dim Wb As Workbook
Dim D As Integer
Dim SyntaxeN As String
 
 
Set Wb = ActiveWorkbook
 
 
 
Prgm = InputBox("Quel programme souhaitez-vous créer?")
 
 
    Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.Name = Prgm
    ActiveCell.FormulaR1C1 = "Liste des activités liées à " & Prgm
    Cells(2, 1).Value = "Activité-modèle"
SyntaxeN = "DECALER('" & Prgm & "'!$A$1;1;0;NBVAL('" & Prgm & "'!$A:$A)-1)"
MsgBox (SyntaxeN)
   Wb.Names.Add Name:=Prgm, RefersTo:=SyntaxeN
    Wb.Sheets("Liste des programmes en cours").Activate
    D = ActiveSheet.UsedRange.Rows.Count + ActiveSheet.UsedRange.Row - 1
    Cells(D + 1, 2) = Prgm
    Cells(D + 1, 3) = Now
    Cells(D + 1, 4) = Environ("USERNAME")
    Sheets("Accueil").Select
End Sub

Merci

07/05/2013, 10h26
SfJ5Rpw8

Bonjour,

Déjà est est ce que sans utiliser de macros tu arrive à utiliser décaler dans la référence d'un champ nommé ?
07/05/2013, 10h32
banane87

Bonjour Bbil,

oui, cela fonctionne sans problème. La formule est exactement la même. D'ailleurs, en retirant les guillemets, ça fonctionne parfaitement.

La question devient donc : "Comment retirer ces satanés guillemets?"

Merci de ton aide.

Banane
07/05/2013, 16h51
NVCfrm

bonjour,

les guillemets seront naturellement placés sans le signe "="

l'erreur doit se trouver dans ta formule.
En l'imaginant ainsi

Code:

SyntaxeN = "DECALER('" & Prgm & "'!$A$1;1;0;NBVAL('" & Prgm & "'!$A:$A)-1)"

teste directement. Dans la formule en NBVAL('" & Prgm & "'!$A:$A)-1
je doute vraiment qu'elle fonctionne.
Arguments ligne = 0,
Argument colonne = -1
Excel n'acceptera pas cette formule pour Decaler.

Donne le code obtenu par l'enregistreur.
07/05/2013, 17h20
banane87

1 pièce(s) jointe(s)
Rebonjour NVCfrm,

Voilà le code donné par l'enregistreur de macro :
Code:

1 2 ActiveWorkbook.Names("_Test").RefersToR1C1 = _ "=OFFSET(Test!R1C1,1,0,COUNTA(Test!C1)-1)"
J'ai essayé en concaténant le nom de ma feuille dedans, mais rien n'y fait!
Je te confirme que cette formule pour nommer des plages dynamiquement fonctionne à 100%.

L'exécution du code suivant donne l'image en PJ :
Code:

1 2 3 SyntaxeN = "DECALER('" & Prgm & "'!$A$1;1;0;NBVAL('" & Prgm & "'!$A:$A)-1)" MsgBox (SyntaxeN)
Autrement, je ne comprends pas ta remarque

Citation:

Dans la formule en NBVAL('" & Prgm & "'!$A:$A)-1
je doute vraiment qu'elle fonctionne.
Arguments ligne = 0,
Argument colonne = -1
Excel n'acceptera pas cette formule pour Decaler.

NBVAL() ne nécéssite pas d'argument pour fonctionner, je cherche juste toutes les cellules non vides de la colonne, ça fonctionne sans souci si tu tapes la formule à la main.

Merci grandement pour ton aide, je ne sais pas comment avancer, là...
07/05/2013, 17h45
jfontaine

Bonjour,

Je prend la conversation en cours de route, mais avec une formule en langue française, j'essaierais avec RefersToLocal

Code:

Wb.Names.Add Name:=Prgm, RefersToLocal:=SyntaxeN
07/05/2013, 17h59
banane87

1 pièce(s) jointe(s)

Bonjour jfontaine,

Merci de vous pencher sur mon problème.
Malheureusement, la solution RefersToLocal engendre le même problème...
Je vous mets le résultat que j'ai après l'éxécution de la macro, si ça peut vous donner un indice.
07/05/2013, 18h05
jfontaine

Et en alimentant la taille de la plage en utilisant VBA

Code:

SyntaxeN = Prgm & "!A1:A" & Sheets(Prgm).Range("A" & Rows.Count).End(xlUp).Row
07/05/2013, 18h14
banane87

Eh bien de la même manière, je me retrouve avec des guillemets!!
J'obtiens cecis dans la plage de définition du champ :
="toto!A1:A2"

Ce qui ne fonctionne pas, il faut retirer les guillemets!

Je dois avouer que c'est assez désespérant. J'ai essayer de passer de String à Variant pour syntaxeN mais sans aucun effet. De plus, tout l'intérêt d'utiliser ma formule est de ne pas dépendre des macros pour la mise à jour du nom...

Merci quand même pour votre aide
07/05/2013, 18h20
jfontaine

Je doit partir, mais ci joint un code que j'utilise et qui fonctionne

Code:

ThisWorkbook.Names.Add Name:="Noms", RefersTo:="=" & ShParam.Name & "!$L$2:$L$" & ShParam.Range("L" & Rows.Count).End(xlUp).Row
07/05/2013, 18h28
banane87

Ok Merci,

Je dois partir aussi.
J'essaierai ça lundi, mais il me semble que je devrais utiliser les macros pour mettre à jour ma liste...
07/05/2013, 18h33
NVCfrm
re,

Tu devrais consulter l'aide locale sur la Fonction DECALER.
Le code par lequel tu fais ta formule est en clair

Code:

=DECALER(Feuil3!A:A;0;-1)

ça ne peut pas être accepté dans une cellule Excel.
Comment peux tu décaler "A" d'une colonne vers la gauche ?

Edit:
Je penses que tu devrais essayer le langage de la macro R1C1. A voir

voici ce que l'enregistreur de macro en testant donne
Code:

1 2 ActiveWorkbook.Names.Add Name:="essai", RefersToR1C1:= _ "=OFFSET(Feuil2!C[-1],,-1)"
utilises donc la syntaxe de la macro comme je te l'ai dit à ma première réponse.
Code:

1 2 Names.Add Name:="Test", RefersToR1C1:= _ "=OFFSET("& Tafeuille & "C[-1],,-1)"
13/05/2013, 17h37
banane87

Bonjour NVCfrm,

J'espère que tu as passé un bon week-end.
Merci grandement pour ton aide, sans toi je n'y serai pas venu à bout! Effectivement, il faut utiliser la formule en mode R1C1 et en anglais! Je ne comrpends pas trop pourquoi, mais ça fonctionne nickel.
Je te recopie le code :

Code:

SyntaxeN = "=OFFSET('" & PgName & "'!R1C1,1,0,COUNTA('" & PgName & "'!C[0])-1)"

Oui, ma formule fonctionne et ne prends en compte que les cellules remplies, pas les cellules vides comme lors de la sélection d'une colonne!

Banane