Gérer des plages via des noms

[Odette]

Bonjour,

J'ai nommé MONTANT la plage $A2:$A100.
Mon problème est que si, pour une raison quelconque, je supprime une ligne complète (par exemple la ligne 4), la plage de MONTANT devient $A2:$A99, ce qui me pose problème dans ma formule faisant référence à MONTANT.

Comment obliger MONTANT à rester $A2:$A100, même après suppression (ou insertion) d'une ligne?

Merci de vos réponses.

 

[@+Thierry]

Bonsoir Odette

Je n'utilse pas trop (à tord) les plages nommées..... Mais je pense que tu devrais t'orienter vers une solution en VBA comme ça :

Sub ReNomme ()
ActiveWorkbook.Names.Add _
Name:="MONTANT", _
RefersTo:="=Sheet1!$A$2:$A$100"
End Sub

Maintenant il faut voir d'où la lancer, si tu mets ce code dans une macro évènementielle, il faut voir, Worksheet_Change est souvent sollicité, et sur SelectionChange aussi..... Peut Etre un évènement plus soft comme Worksheet_Activate...
Enfin il y a des moyens... à toi de voir

Bonne soirée et Bienvenue sur XLD, Odette
@+Thierry

 

[Jean-Marie]

Bonsoir Odette, Thierry et le forum

Pas besoin de VBA dans ce coup

Dans le référence de ta plage, lors de la définition du nom MONTANT, tu saisis =indirect("$A2:$A100").

@+Jean-Marie

 

[@+Thierry]

Bien Vu Jean Marie !!

c'est vrai qu'en plus j'avais déjà vu ce truc dans les déclarations/définitions de nom.......

c'est vrai que ce fameux "Décaler" il vous sauve toujours !!!! avant de faire ALT + F11 !!! lol

et c'est vrai qu'aussi à force de tout manipuler par VBA on oublie même qu'Excel à des outils prévus !! lol

Merci pour Odette c'est certainement plus facile et plus radical....

Moi du coup j'ai re-potassé comment gérer des nom sur des plages.... faudra que je dise à VBA de mettre Decaler devant dorénavant !!!

Bonne Soirée

@+Thierry

 

[@+Thierry]

j'ai fait un lapsus !! indirect !! oups


décaler c'est à cause du cauchemard de Moa cette nuit !! lol (voir le fil => Rien Que Pour Vous Donner Mal A La Tête


@+Thierry

 

[Odette]

Bonjour Jean-Marie, Bonjour Thierry, et tout le forum!

Merci beaucoup pour vos réponses précieuses.
J'ai donc défni mes plages avec la fonction INDIRECT, et ô génial, pas de problèmes lorsque je supprime une ligne. Mais d'autres soucis sont apparus:

-- La fonction F5 (atteindre) ne présente plus les plages nommées en INDIRECT.

-- MONTANT a été défini par: =INDIRECT(Chrono!$I2:$I15000).
Tant que le curseur se trouve sur la deuxième ligne du tableau, pas de souci, la définition ne bouge pas. Mais dès que le curseur se trouve sur la ligne 10 par exemple et que je visualise, via Insérer/Nom/Définir, la définition de MONTANT, celle-ci a été décalée à I10:I15008 (donc toujours le même nombre de lignes, mais décalé à partir du curseur).

-- De plus, la formule que j'avais écrite se solde par #REF!.

Peut-être devrais-je préciser les choses:
-- Je travaille en Excel 97
-- J'ai un fichier Chrono (A1 à M15000) contenant les données, fichier multi-utilisateur.
-- J'ai un fichier Stats (mono-utilisateur) comportant des tableaux automatiques, avec formules matricielles du genre:
{=SOMME(SI(MOIS(Chrono_2002.xls!Reception)=1;SI(ANNEE(Chrono_2002.xls!Reception)=2002;Chrono_2002.xls!Montant)))}

Bref, rien ne va plus! HELP ME!!!!!!

Merci beaucoup.

 

[Jean-Marie]

Bonjour Odette,

Je n'avais pas remarquer ce problème.

Pour le premier problème, définit un nom exemple PLAGE, avec comme définition ="I2:I15000"

Tu redéfinis MONTANT avec la référence =indirect(Plage).

Maintenant ta plage ne bouchera plus.

Pour le deuxième problème, d'atteindre F5, pour l'instant je n'ai tourvé que dans le champ tape chrono puis OK, le problème tu vas ajouter (à chaque fois) dans la liste proposée par atteindre la cellule sélectionnée. Dans quelle but ??

@+Jean-Marie

 

[@+Thierry]

Bonjour Odette, Jean-Marie et le Forum,


Oui Odette, comme tu le supposais toi même dans ton Email il est préférable de s'adresser directement dans le forum que dans les BAL des intervenants. C'est une petite erreur des NewBies souvent dans les Forums. Tu auras tout l'avantage que d'autres connaissent une réponse mieux appropriée à ta question.

Pour la fonction "Indirect" j'ai trouvé l'inverse pour avoir un nom de plage dynamique, car j'ai fait un tour dans les archives du MPFE... Pour un autre cas de figure ils utilisent "Décaler" couplé avec "NBVal" et ça donne ceci dans la définition du Nom : (ce n'est pas ce que tu veux, mais c'est juste pour voir si l'on perd aussi "Atteindre")

=DECALER(Feuil1!$A$1;;;NBVAL(Feuil1!$A:$A);1)

Donc j'en conclus que définir un nom de cette manière annule toute possibilité d'utiliser "Atteindre" (Goto) ou simplement la petit liste "Zone Nom" (en haut à droite de la barre de formule).

Par conséquent j'en reviens à la Macro que je proposais hier qui, elle, renomme exactement comme si tu le fais à la main et te permettra la recherche de la plage Nommée par F5, Goto, Liste "Zone Nom"...

Sub Renomme()
ActiveWorkbook.Names.Add _
Name:="MONTANT", _
RefersTo:="=Chrono!$I2:$I15000"
End Sub

Ensuite le problème reste à "capter" l'évènement d'insertion de ligne (ou de supression) et là je butte, car l'action "insertion seule" n'agit pas comme un évènement de la feuille "Change", ni "Calculate", ni "SelectionChange".... Donc il faut le lancer d'une manière où l'on est certain que le User activera cette macro.

Vu que tu travailles en Multi-UTilisateurs, donc de toute manière il faudra bien que ton User sauve son changement pour qu'il soit effective sur les autres classeurs en lien... Donc l'évènement BeforeSave dans le Private Module de ThisWorkBook peut convenir comme ceci :

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Renomme
End Sub

On peut aussi la lancer dans l'évènement BeforeClose, Open etc... Ou encore dans le Private Module de la feuille Chrono elle-même sur des évènements tels que Worksheet_SelectionChange, car je suppose que le User fera bien une sélection de cellule après avoir inséré une ligne ... (?)


NB La macro "renomme" étant placée dans un module standard....

Voilà Odette tu as cette possibilité VBA, qui peut venir à ton secours !!

Et donc heureux de savoir que tu nous mets dans tes favoris !!

Bon Dimanche
@+Thierry

 

[pat5]

Bonjour tout le monde

Salut @+Thierry
En forme ? Tu manquais ce matin.
Très bonne explication que je vais potassée;
Bon week-end à tous

Salut ;o)

 

[@+Thierry]

Bonjour Pat5

et vouis je dormais un peu ......; Grasse matinée du Dimanche !! )

Bon Dimanche à Toi (tiens, je me fais gruiller du Figatell"u" pour mon déjeuner !! héhéhé)

@+Thierry

 

[Jean-Marie]

Bonjour Thierry

Lors d'une suppression ou l'ajout d'une ligne, la feuille est recalculée.

Il suffit de mettre la macro de déclaration du nom dans l'événement calcul de la feuille.

@+Jean-Marie

 

[@+Thierry]

Bonjour Jean-Marie

J'ai fait l'essai aussi avec Calculate (of course) et contrairement à ce que je pensais aussi, c'est idem, on dirait que l'ajout ou la supression de ligne n'affecte aucun évènement.

essaies çà sur une feuille vierge :
Private Sub Worksheet_Calculate()
MsgBox "Toto"
End Sub

Ou alors il faudrait faire une somme sur la zone alors pour déclencher cet évènement...

Bon Dimanche
@+Thierry

 

[Odette]

Merci à tous! La solution de Thierry est la bonne avec la macro Renomme() et un lancement automatique lors de l'enregistrement (la petite modification à effectuer était de nommer la plage =Chrono!$I$2:$I$15000 (donc avec $ devant le numéro de ligne). Par précautino, je vais également lancer la macro Renomme() via Autpen.

Jean-Marie, je n'ai pas compris l'essai que tu proposes sur ta dernière intervention.

Me voilà sauvée, et comme je suis une avide de trucs et astuces, m'en voudrez-vous si je pose une autre question? (j'ajoute donc un nouveau sujet).

 

[@+Thierry]

Oups oui,


En fait pour les test je travaillais sur une mini zone comme ceci
RefersTo:="=FEuil1!$A$2:$A$15"

Puis arrivé ici dans le post j'ai fait un copié collé de tes références sans faire gaffe aux Valeur Absolue de Row...

Mais tu as corrigé de toi-même
@+Thierry