En VBA, utiliser la valeur d'une varibla pour créer une plage nommée

[luck8282]

Bonsoir à tous,

J'ai créer une "base de donnée" excel (avec nom du produit, référence, N° de lot...) qui permets de faire une certain nombre de calculs grâce à des plages nommées variable dynamique.
Les plages nommées sont faites à partir des références produits (pour chaque référence identique = une plage). Mais là ou le bas Blaise c'est lors de la création de nouvelles références car les utilisateurs trouvent trop compliqué la création d'une nouvelle plage... . Je suis donc en train de faire une macro pour automatiser tous ça.

Étant début en VBA, j'ai quelques problèmes avec la syntaxe(ci joint mon code en cours de création).
Là où ça coince, c'est pour intégrer les valeurs des variable dans la formule.
Après une imputBox:
D correspond au nom de la plage nommée
C correspond à la valeur à rechercher dans le fichier (Excel).
Indépendamment les variables me renvoies les bonnes valeurs mais je m'arrive pas à les intégrer dans la formule; problème de syntaxe probablement.

Si vous avez la solution je suis preneur.

Merci d'avance.

Sub creaction_nouvelle_reference()

Dim A As Long
Dim B As String
Dim C As String
Dim D As String

A = InputBox(Title:="Bonjour", Prompt:="Veuillez Saisir la référence du produit.")
B = Right(A, 5)
C = "605-" & B
D = "_" & C

ActiveWorkbook.Names.Add Name:="D", RefersToR1C1:= _
"=OFFSET('Prod Vitro ASM'!R1C2,MATCH(C,'Prod Vitro ASM'!R1C2:R9998C2,0)-1,0,COUNTIF('Prod Vitro ASM'!R1C2:R9998C2,C),1)"
End Sub

 

[soenda]

Re : En VBA, utiliser la valeur d'une varibla pour créer une plage nommée

Bonjour le fil, luck8282

D étant une variable, il faut écrire ...Add Name:= D, ... sans les guillemets

A plus

 

[luck8282]

Re : En VBA, utiliser la valeur d'une varibla pour créer une plage nommée

Bonjour le fil, luck8282

D étant une variable, il faut écrire ...Add Name:= D, ... sans les guillemets

A plus

Merci pour ta réponse; ça marche (en moitié). En effet j'ai maintenant une erreur 1004: le nom entré n'est pas valide.
La variable D ne renvoie bien une valeur du style "_605-XXXXX" comme il me faut pour créer un nouveau nom mais ça marche pas au niveau de la Formula
D'autre part la plage ainsi nommée fait une recherche "mach" dans la colonne E:E alors qu'il fait faire la recherche en B:B

Pour plus de clarté voici la vrai formule (pour un modèle type):
=DECALER('Prod Vitro ASM'!$B$1;EQUIV("605-XXXXX";'Prod Vitro ASM'!$B$1:$B$9997;0)-1;0;NB.SI('Prod Vitro ASM'!$B$1:$B$9997;"605-XXXXX");1)

Et la formule obtenue actuellement par Macro:
=DECALER('Prod Vitro ASM'!$B$1;EQUIV('Prod Vitro ASM'!E:E;'Prod Vitro ASM'!$B$1:$B$9998;0)-1;0;NB.SI('Prod Vitro ASM'!$B$1:$B$9998;'Prod Vitro ASM'!E:E);1)

 

[Pierrot93]

Re : En VBA, utiliser la valeur d'une varibla pour créer une plage nommée

Bonjour Luck, Soenda

A priori, le nom d'une plage nommée ne peut pas contenir de trait d'union(-)...

bonne journée
@+

 

[luck8282]

Re : En VBA, utiliser la valeur d'une varibla pour créer une plage nommée

Bonjour Luck, Soenda

A priori, le nom d'une plage nommée ne peut pas contenir de trait d'union(-)...

bonne journée
@+

Merci pierrot,

En effet c'est une grossier erreur de ma part.
Maintenant dans la plage crée, j'ai C et non pas la valeur de la variable C. Comment faire pour avoir la valeur de la variable C dans la formule.

Merci

 

[Pierrot93]

Re : En VBA, utiliser la valeur d'une varibla pour créer une plage nommée

Re,

comment as-tu modifié ton code ?

bonne soirée
@+

 

[JNP]

Re : En VBA, utiliser la valeur d'une varibla pour créer une plage nommée

Bonsoir le fil ,
Si j'ai compris la question

ActiveWorkbook.Names.Add Name:=D, RefersToR1C1:= _
"=OFFSET('Prod Vitro ASM'!R1C2,MATCH(" & C & ",'Prod Vitro ASM'!R1C2:R9998C2,0)" _
& "-1,0,COUNTIF('Prod Vitro ASM'!R1C2:R9998C2," & C & "),1)"

Bonne soirée

 

[luck8282]

Re : En VBA, utiliser la valeur d'une varibla pour créer une plage nommée

Bonsoir le fil ,
Si j'ai compris la question

ActiveWorkbook.Names.Add Name:=D, RefersToR1C1:= _
"=OFFSET('Prod Vitro ASM'!R1C2,MATCH(" & C & ",'Prod Vitro ASM'!R1C2:R9998C2,0)" _
& "-1,0,COUNTIF('Prod Vitro ASM'!R1C2:R9998C2," & C & "),1)"

Bonne soirée

Merci JNP,
Ca marche presque parfaitement.
En effet le nom de plage est bon, la formule ainsi crée est bonne sauf que la référence apparaît (dans la formule) sous la forme de 605-XXXXX alors qu'il ne la faudrait sous la forme de "605-XXXXX" (car la formule EQUIV nécessite un texte sous la forme "TEXTE").
J'ai créé une variable supplémentaire pour avoir la référence sous la forme 605-XXXXX" mais je n'arrive pas à faire la même chose pour le côté gauche.

Voici nom code quelque peu modifié par apport au sujet de départ.

Sub creaction_nouvelle_reference()
Dim A As Long
Dim B As String
Dim C As String
Dim E As String
Dim F As String
Dim G As String

A = InputBox(Title:="Bonjour", Prompt:="Veuillez Saisir la reférence du produit.")
B = Right(A, 5)
C = "_605_" & B
E = "605-" & B
F = E & """"

ActiveWorkbook.Names.Add Name:=C, RefersToR1C1:= _
"=OFFSET('Prod Vitro ASM'!R1C2,MATCH(" & F & ",'Prod Vitro ASM'!R1C2:R9998C2,0)" _
& "-1,0,COUNTIF('Prod Vitro ASM'!R1C2:R9998C2," & F & "),1)"

End Sub

 

[luck8282]

Re : En VBA, utiliser la valeur d'une varibla pour créer une plage nommée

C'est bon j'ai trouvé.
C'est vraiment pas esthétique, ça transpire le débutant mais ça marche.
merci à tous les contributeurs de ce site.

Voici le code pour information.

j'aurais cependant peut être d'autres questions par la suite.

Bonne soirée.

Sub creaction_nouvelle_reference()

Dim A As Long
Dim B As String
Dim C As String
Dim E As String
Dim F As String

Dim H As String
Dim I As String

A = InputBox(Title:="Bonjour", Prompt:="Veuillez Saisir la reférence du produit.")
B = Right(A, 5)
C = "_605_" & B
E = "605-" & B
F = E & """"

H = Right(F, 1)
I = H & F

ActiveWorkbook.Names.Add Name:=C, RefersToR1C1:= _
"=OFFSET('Prod Vitro ASM'!R1C2,MATCH(" & I & ",'Prod Vitro ASM'!R1C2:R9998C2,0)" _
& "-1,0,COUNTIF('Prod Vitro ASM'!R1C2:R9998C2," & I & "),1)"


End Sub

 

[JNP]

Re : En VBA, utiliser la valeur d'une varibla pour créer une plage nommée

Re ,

C'est vraiment pas esthétique, ça transpire le débutant mais ça marche.

Pas forcément ...
Sinon, tu aurais pu écrire

ActiveWorkbook.Names.Add Name:=C, RefersToR1C1:= _
"=OFFSET('Prod Vitro ASM'!R1C2,MATCH(""" & I & """,'Prod Vitro ASM'!R1C2:R9998C2,0)" _
& "-1,0,COUNTIF('Prod Vitro ASM'!R1C2:R9998C2,""" & I & """),1)"

Mais ce n'était pas plus esthétique ...
Bonne nuit