Définition d'une plage nommée variable

Résolu/Fermé

touroul Messages postés 465 Date d'inscription mardi 5 octobre 2010 Statut Membre Dernière intervention 29 janvier 2024 - 5 avril 2017 à 10:25
touroul Messages postés 465 Date d'inscription mardi 5 octobre 2010 Statut Membre Dernière intervention 29 janvier 2024 - 5 avril 2017 à 15:27

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 With
End 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

A voir également:

Nommer une plage de cellule variable vba
Aller à la ligne dans une cellule excel - Guide
Frédéric cherche à faire le buzz sur les réseaux sociaux. il a ajouté une image d’ours polaire sur une image de plage. retrouvez l'image originale de la plage. que cache l'ours polaire ? - Forum Loisirs / Divertissements
Excel compter cellule couleur sans vba - Guide
Affecter la valeur d'une cellule à une variable vba ✓ - Forum Bureautique
Si cellule contient texte alors ✓ - Forum Excel

5 réponses

Réponse 1 / 5

ccm81 Messages postés 10851 Date d'inscription lundi 18 octobre 2010 Statut Membre Dernière intervention 16 avril 2024 2 404
5 avril 2017 à 14:04

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

Réponse 2 / 5

Vaucluse Messages postés 26496 Date d'inscription lundi 23 juillet 2007 Statut Contributeur Dernière intervention 1 avril 2022 6 394
5 avril 2017 à 10:37

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

Réponse 3 / 5

touroul Messages postés 465 Date d'inscription mardi 5 octobre 2010 Statut Membre Dernière intervention 29 janvier 2024 15
5 avril 2017 à 10:56

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 !

Vaucluse Messages postés 26496 Date d'inscription lundi 23 juillet 2007 Statut Contributeur Dernière intervention 1 avril 2022 6 394
Modifié le 5 avril 2017 à 11:45

je ne trouve pas de problème de genre, voir ici en H14
https://mon-partage.fr/f/WUigmGfl/
comment fais tu?
(on est bien d'accord, les accolades ne se rentrent pas au clavier, elles viennent toutes seules!)
crdlmnt

ps: pour la macro, désolé, je en suis pas assez costaud en VBA

Réponse 4 / 5

touroul Messages postés 465 Date d'inscription mardi 5 octobre 2010 Statut Membre Dernière intervention 29 janvier 2024 15
Modifié le 5 avril 2017 à 13:00

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 !!!

Vaucluse Messages postés 26496 Date d'inscription lundi 23 juillet 2007 Statut Contributeur Dernière intervention 1 avril 2022 6 394
5 avril 2017 à 14:12

Ben pour la macro, non, comme ajouté en ps dans mon précédent message, je ne suis pas compétent.
pour le nom de feuille, la longueur n'a pas d'importance. Voir dans ce dernier, modèle, j'ai modifié le nom.
https://mon-partage.fr/f/kQgZQeGl/
Regarde plutôt si en ayant peut être fait une copie tu n'as pas deux plages nommées du même nom dans le fichier.

Autrement je ne vois pas, renvoie moi ce qui ne va pas si tu veux que je jette un œil!
crdlmnt

Vous n’avez pas trouvé la réponse que vous recherchez ?

Posez votre question

Réponse 5 / 5

touroul Messages postés 465 Date d'inscription mardi 5 octobre 2010 Statut Membre Dernière intervention 29 janvier 2024 15
5 avril 2017 à 15:27

@ 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 !

Discussions similaires

Retrouver l'image originale après avoir fait des modifications ?

programme qui n'affiche pas le mot secret

VBA : Range avec références variables..

VBA Excel : sélection d'une plage variable

Excel VBA : Récupérer la valeur d'une cellule

Discussions similaires

Newsletters