Définition d'une plage nommée variable Résolu/Fermé

Question

Bonjour le forum !

J'ai quelques difficultés à mettre en place une fonction qui me permettrait d'alléger un fichier.
Plutôt que d'utiliser des formules faisant appel toutes à une même plage définie en références absolues, je souhaiterai utiliser ces formules une zone nommée.

Je voudrais créer cette zone nommée par macro, car elle est de longueur variable (une première macro commence par importer en feuille SOURCE un certain nombre de données).

J'ai essayé pas mal de trucs, mais en raison de la présence de formules en fin de liste, ma zone nommée est définie au delà de la dernière ligne remplie ...

Sub DEFPLAGE()Dim r As Range   'Nomme la plage de 'SOURCE!ColonneB contenant des valeurs'With Sheets("SOURCE")Set r = Sheets("SOURCE").Range("B2:B" & [B300000].End(xlUp).Row)r.Name = "MAPLAGE"'End WithEnd Sub

D'autre part, lancée depuis une autre feuille, la macro ne semble pas aller "taper" dans la feuille source.

 http://www.cjoint.com/c/GDfh5FHegEQ

Puis-je vous demander de l'aide ?
Je vous remercie par avance

Configuration: Windows / Excel 2013 / Firefox 52.0

ccm81 · Accepted Answer

Bonjour à tous les deux

Pour les macros, tu peux essayer ceci

' definir une nouvelle plage
Sub DEFPLAGE()
Dim r As Range   'Nomme la plage de 'SOURCE!ColonneB contenant des valeurs
Dim lifin As Long
lifin = Sheets("SOURCE").Range("B" & Rows.Count).End(xlUp).Row
Set r = Sheets("SOURCE").Range("B2:B" & lifin)
ActiveWorkbook.Names.Add Name:="MAPLAGE", RefersTo:="=SOURCE!" & r.Address
End Sub

' modifier l'adresse d'une plage existante
Sub ModPlage()
Dim plage  As String, lifin As Long
lifin = Sheets("SOURCE").Range("B" & Rows.Count).End(xlUp).Row
plage = Sheets("SOURCE").Range("B2:B" & lifin).Address
ActiveWorkbook.Names("MAPLAGE").RefersTo = plage
End Sub

Cdlmnt

Vaucluse · Answer

Bonjour à toutes fins utiles: sans VBA, mais sous réserve que votre liste s'allonge sans laisser de blanc: pour l'ajuster au nombre de valeur de B2 à B1000 nommer avec cette formule: =DECALER(SOURCE!$B$2;;;NB.SI(SOURCE!$B$2:$B$1000;"><")) placez bien le >< dans NB.SI et non pas <> qui compterait aussi les cellules avec formules sans résultat) crdlmnt

touroul · Answer

Bonjour Vaucluse !

Je connaissais cette formule très pratique, sans avoir pensé à l'appliquer.

La définition de la plage est OK.
Par contre, lorsque j'essaie de l'utiliser pour compter, dans cette plage, le nombre de valeurs distinctes, j'obtiens : #NOM?

Voici la formule :
{=SOMME(SI(MAPLAGE<>"";1/NB.SI(MAPLAGE;MAPLAGE)))} (validée en matricielle)

Connaissez-vous l'origine de cette erreur ? Les formules matricielles supportent-elles les zones nommées ?

D'autre part, j'aime bien comprendre où sont mes erreurs, et je ne comprends pas pourquoi ma macro pose problème, si vous aviez un moment, ça me serait très profitable !

Merci beaucoup !

touroul · Answer

Effectivement, sur le fichier test, cela fonctionne parfaitement. J'ai donc poussé la recherche du problème : En fait, il s'avère que c'est la définition de la plage dans mon fichier original qui ne va pas, mais j'ai cherché non-stop. La formule : =DECALER(CumulCSV!$B$2;;;NB.SI(CumulCSV!$B$2:$B$1000;"><")) Alors que (après avoir renommé la feuille CumulCSV en SOURCE) : =DECALER(SOURCE!$B$2;;;NB.SI(SOURCE!$B$2:$B$1000;"><")) FONCTIONNE ! C'est à n'y rien comprendre : peut-être que CumulCSV est trop long, je ne sais pas ... Je vais m'engager dans cette voie (avec DECALER), cependant peux-tu jeter un oeil sur ma macro, histoire de comprendre quelles sont les erreurs de syntaxe ? Merci !!!

touroul · Answer

@ Vaucluse :
Problème résolu, mais sans comprendre pourquoi : 
J'avais déjà épuré les zones nommées, c'était propre.
J'ai fini par renommer ma feuille SOURCE en SOURCE2, puis je l'ai supprimée, puis j'en ai recréé une nommée 'CumulCSV' comme je le voulais : et là, la définition du nom par la fonction DECALER s'est faite de suite, sans problème.
Il devait y avoir un schmoll dans cette feuille qui ne plaisait pas à Excel, suis content que ce soit réglé !

@ ccm81 : Bonjour !
Merci pour la macro corrigée, c'est effectivement bien plus joli ainsi !
Et ça fonctionne après adaptation.
J'ai plus de mal à comprendre pourquoi tu as fait un ModPlage, c'est pour réinitialiser la variable, c'est ça ?
Si j'ajoute des lignes sur CumulCSV, et que je relance DEFPLAGE, la plage s'adapte bien pourtant ...

Merci à tous les 2, c'est génial !

Définition d'une plage nommée variable

5 réponses

Discussions similaires