Macro définir nom d'une liste à longueur variable

Bonjour à tous,

J'aimerais, grâce à une macro (intégrer dans une autre macro plus grande), pouvoir définir le nom d'une liste afin de la copier, la formule devant se trouver
J'aimerais réaliser une macro (intégrer dans une plus grande macro) réalisant ceci :
- donner un nom à une liste de données, sachant que la longueur de la liste est variable
- copier cette liste
- la coller dans une cellule sous format d'un menu déroulant

J'ai ce 1er code afin de donner le nom à ma plage de données: (où Sites est le nom de la liste)

Code:

---

1 2	Sheets("first").Select Sites = Range([C30], [C65365].End(xlUp))

---

Et ensuite ce code, qui devrait me permettre de définir la cellule C5 comme menu déroulant contenant la liste "Sites" :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14

ActiveSheet.Range("C5").Select With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=Sites" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With

---

Mais ça ne fonctionne pas et j'ai le message d'erreur suivant : "erreur définie par l'application objet" et les deux lignes ci-dessous surlignées :

Code:

---

1 2	.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=Sites"

---

J'ai également essayé en intégrant la plage de données à définir directement dans la seconde partie du code, suite à "Formula1=" mais cela ne fonctionne pas non plus, ou en tout cas je n'ai pas la bonne formule.

Je n'ai pas l'impression que ce que je veux faire est si compliqué mais je n'y arrive pas...

Merci beaucoup pour votre aide et bonne après-midi !

Bonjour Sofy16,

Le problème vient du fait que tu essayes d'insérer une formule Formula1:="=Sites" qui n'est pas connu d'Excel.
Pour lui "Sites" ne veut rien dire.

Il faut donc que tu fasses en sorte qu'Excel sache ce que cela signifie, grâce, par exemple, au gestionnaire de nom.
Toujours par enregistreur de macro, on trouve :

Code:

---

1 2	ActiveWorkbook.Names.Add Name:="Sites", RefersToR1C1:="=Feuil1!R8C7:R13C7" ActiveWorkbook.Names("Sites").Comment = ""

---

Je te laisse insérer les bonnes valeurs ici : RefersToR1C1:="=Feuil1!R8C7:R13C7".

Cependant, tu peux faire directement :

Code:

---

1 2	.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=Feuil1!$A$1:$A$10"

---

en plaçant la même chose dans Formula1:=.
En espérant que ces explications résolvent ton problème ! :ccool:

Cordialement,
Kimy

Merci pour cette réponse,
Cependant, serait-il possible de ne sélectionner que les cellules non vide de ma plage de cellules ?
Car avec cette formule, je sélectionne ma plage de façon large et je peux donc avoir dans ma liste déroulante de nombreuses données vides...