différencier contenu d'une cellule et formule

**lil404** · 02/06/2009, 10h56

Bonjour tout le monde
Me voici encore à faire appel à vous parce qu'une fois de plus... je bloque

.

Je ne sais plus très bien s'il s'agit d'un problème techinque, ou purement logique, alors je vous expose mon problème :

J'ai créé dans l'évennement "activate" d'une de mes feuilles Excel une fonction qui insère dans chaque cellule une formule disant en gros "= à la précédente" (c'est à dire, pour D4, ça sera "=D3", pour E5 ça sera "=E4", etc). A la première activation, mes cellules prennent les bonnes valeurs, c'est parfait.

Mais voila le problème : mes cellules se changent à l'aide d'une liste déroulante. En choisissant une valeur, j'efface donc la formule qui y était.
Je ne suis pas sûre d'être très claire, alors je vous montre un exemple :

J'entre dans E4 la valeur "aaa".
La formule marche, E5 et les suivantes prennent bien à leur tour la valeur "aaa".
Mais E4 n'est maintenant plus égale à "=E3", mais à "aaa".
Aussi, si je change ensuite E3, pour y mettre la valeur "bbb", E4 et les suivantes n'en seront pas modifiées.

J'aimerai savoir s'il existe un moyen de changer le contenu d'une cellule, sans modifier sa formule.
Le gros problème étant qu'il faudrait que la valeur "aaa" soit encore présente à la deuxième activation (donc, que la valeur prime sur la formule)...

Bref tout ça me semble bien compliqué, et m'embrouille complètement.

Si quelqu'un avait la gentillesse de m'aider à y voir plus clair...

**DeaD78** · 02/06/2009, 11h10

Bonjour,

Il faut utiliser

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

ActiveCell.FormulaR1C1

D'après un petit exemple fait à l'enregistreur de macros :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
Range("B5").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]"
    Range("C5").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]"
    Range("D5").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]"

En gros, ici, tu as : B5 = A5, C5 = B5 et D5 = C5. Si tu rentres "10" dans A5, alors B5, C5 et D5 prendront alors la valeur "10". De plus, si en A5, tu remplaces "10" par "15", les autres cellules prendront la valeur "15" et ainsi de suite.

Bref, il faut passer par des FormulaR1C1 et non des Value.

Bonne journée !

EDIT : Cela fonctionne même si je mets une liste de choix (via Données/Validation/Autoriser Liste et je rentre des valeurs en dur). Bref, si je change la valeur de A5 par la liste, les autres cellules prennent la valeur choisie. Si je mets aussi une liste en B5 avec d'autres valeurs, si je change la valeur en A5, B5 et les autres cellules prendront la valeur de A5. Cependant, si je change seulement la valeur de B5 par la liste précédente, seulement C5 et D5 prendront la valeur de B5 et A5 gardera sa valeur d'origine. Bref, c'est ce qu'il te faut. Bonne chance !

**lil404** · 02/06/2009, 11h25

Merci pour cette réponse rapide

J'ai testé cette solution, mais malheureusement l'entrée d'une valeur dans une cellule efface encore la formule qui s'y trouvait =/

Cette valeur ci, je ne l'entre pas à l'aide d'un "value", mais dynamiquement, en choisissant parmi la liste déroulante...

Edit :

En attendant, je me suis trouvée une solution provisoire.
Je laisse ce problème si la valeur est remplie, et j'ajoute à ma liste une valeur "rien" (pas très propre, mais ça résoud au moins le problème), auquel cas la cellule reprendra sa formule initiale...
C'est toujours mieux que de ne rien faire du tout...

En attendant, si quelqu'un a une solution moins brouillon que la mienne, ça m'intéresse toujours.

**lil404** · 02/06/2009, 11h58

Je n'avais pas vu l'édition de ton message

Tout ce que tu dis est juste, ça modifie toujours bien les cellules suivantes, sans toucher aux précédentes. Mais mon problème persiste :

Si j'entre la valeur "aaa" en E4, E5 et les suivantes prendront la valeur "aaa".
Si je change E4, les suivantes vont changer également à nouveau.
En revanche, si après ces manipulations, je mets la valeur "ccc" dans E3, les autres cellules ne bougeront pas... Car dans E4, ce n'est plus écrit "=E3", mais bien "aaa"... =/

**DeaD78** · 02/06/2009, 12h11

Ok, je viens de comprendre le problème.

Petite question : lorsque tu rentres tes valeurs "aaa" et "ccc", tu le fais en sélectionnant la cellule puis tu tapes la valeur souhaitée puis tu fais "ENTREE". C'est logique, ça efface la formule précédente qui existait dans la cellule.

Mais n'as-tu pas parlé de liste ? Ne pourrais-tu pas mettre une liste sur chacune de tes cellules. Ainsi, lorsque tu veux ajouter "aaa" ou "bbb", tu ne le fais pas directement dans la cellule en remplaçant la formule mais tu ajoutes la valeur souhaitée comme donnée de la liste et ainsi tu n'as plus de problème. Tu pourrais donc sélectionner ton "aaa" ou "bbb" dans la liste sans avoir à changer la formule (de plus, la cellule serait lockée pour liste).

Qu'en penses-tu ?

**Gorfael** · 02/06/2009, 12h58

Salut lil404 et le forum
tu veux quoi exactement ?
Qu'une colonne réagisse en fonction de la dernière cellule modifiée ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo erreur
Dim Cel As Range
Application.EnableEvents = False
'on évite les boucles du aux changements
If Target.Cells.Count > 1 Then Exit Sub
'si il y a plus d'une cellule modifiée, on sort
If Target.Address = Cells(65536, Target.Column).End(xlUp).Address Then Exit Sub
'si la cellule modifiée est la dernière de la colonne, on sort
For Each Cel In Range(Target.Offset(1, 0), Cells(65536, Target.Column).End(xlUp))
    If Left(Cel.Formula, 1) <> "=" Then _
        Cel.FormulaR1C1 = "=R[-1]C"
Next Cel
erreur:
Application.EnableEvents = True
If Err <> 0 Then MsgBox Err.Description, , "Erreur Excel n°" & Err.Number
End Sub
Sub test()
Application.EnableEvents = True
End Sub

Si tu modifies une valeur dans une colonne, les suivantes la recopieront, jusqu'à la dernière non-vide de la colonne.

Mais je suppose que c'est un essai pour finaliser un macro plus complexe. Serait pas mal d'expliquer ce que tu veux vraiment faire. Une macro qui fonctionne sur une boucle aussi simple ne sera peut-être pas exploitable sur un système plus complexe.

Mais c'est un fonctionnement "débile" pour la cellule. Une cellule reçoit :
- soit une fonction
- soit une entrée manuelle
et pas les deux
Si tu veux qu'elle accepte les deux, il faut que ton entrée manuelle se fasse ailleurs et qu'elle soit "dominante" sur le reste de la formule
=si(F4<>"";F4;E3)
avec ce genre de formule, la colonne E recopiera la ligne précédente, sauf s'il y a une valeur en F
A+

**lil404** · 02/06/2009, 14h07

Ok, je viens de comprendre le problème.

Petite question : lorsque tu rentres tes valeurs "aaa" et "ccc", tu le fais en sélectionnant la cellule puis tu tapes la valeur souhaitée puis tu fais "ENTREE". C'est logique, ça efface la formule précédente qui existait dans la cellule.

Mais n'as-tu pas parlé de liste ? Ne pourrais-tu pas mettre une liste sur chacune de tes cellules. Ainsi, lorsque tu veux ajouter "aaa" ou "bbb", tu ne le fais pas directement dans la cellule en remplaçant la formule mais tu ajoutes la valeur souhaitée comme donnée de la liste et ainsi tu n'as plus de problème. Tu pourrais donc sélectionner ton "aaa" ou "bbb" dans la liste sans avoir à changer la formule (de plus, la cellule serait lockée pour liste).

Qu'en penses-tu ?

En réalité, c'est déjà le cas. Quand je parle "d'entrer une valeur", je veux dire par là sélectionner une valeur de la liste. Mais tout comme une entrée manuelle, ça efface la formule.

Mais je suppose que c'est un essai pour finaliser un macro plus complexe. Serait pas mal d'expliquer ce que tu veux vraiment faire. Une macro qui fonctionne sur une boucle aussi simple ne sera peut-être pas exploitable sur un système plus complexe.

Non, c'est vraiment ça, que je veux faire. Si tu veux plus de détails, chaque colonne représente un jour de la semaine, chaque ligne un employé, et chaque cellule un lieu (les cellules étant en réalité des listes de lieux). Je souhaite automatiser la saisie de lieu, de façon à ce que, pour un employé donné, en entrant un lieu le lundi, il soit valable toute la semaine (donc sur toute la ligne). De même pour chaque jour, qui remplit chaque fois automatiquement les suivants (afin qu'il soit quand même possible d'avoir plusieurs lieux différents par semaine et employé). Jusque là, pas de problème. Mais si l'utilisateur veut rectifier son choix, les formules ne fonctionnent plus. En effet, la cellule qu'il a modifiée en choisissant un lieu "perd" sa formule, pour prendre la valeur de la liste, ce qui n'est pas le cas des suivantes. Si la cellule en question est un lundi, pas de problème, mais si c'est un autre jour, un jeudi par exemple, alors en modifiant le mercredi, le jeudi ne se modifiera plus automatiquement.
J'essaie d'expliquer au plus clair, mais c'est un peu compliqué

L'idée du "si" me paraissait pas mal, mais Excel refuse cette formule, car elle fait référence à elle même..

**Krovax** · 02/06/2009, 14h37

Pour la formule du si tu met dans F3

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
=si(F4<>"";F4;E3)

Pas dans F4 et E3
Tu met tes liste ligne4 et les résultats sont ligne 3
La ligne 4 serait une ligne nouveau lieu par exemple, mais ce n'est pas pratique
Regarde le code de Gorfael, il devrait résoudre ton problème

Mais j'ai un peu l'impression que ca revient a sortir la grosse artillerie alors que l'utilisateur peu simplement faire copier la valeur de la cellule en cliquant dans le coin inférieure droit. Ou alors faire un Ctrl+Z en cas d'erreur. Mais bon je suppose que l'on part du principe que l'utilisateur doit être assisté, alors on va essayer de t'aider.

Edit je vien de voir que Gorfael travail sur les colonnes et toi sur les lignes (enfin d'après ce que j'ai compris tu veux que la colonne F ligne 3 soit égale a E ligne 3)

essaye cette version très très largement inspiré de celle de Gorfael

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo erreur
Dim Cel As Range
 
If Not Intersect(Target, Range("A4:F7")) Is Nothing Then 'remplace "A4:F7" par ton tableau
Application.EnableEvents = False
'on évite les boucles du aux changements
If Target.Cells.Count > 1 Then Exit Sub
'si il y a plus d'une cellule modifiée, on sort
If Target.Column = Target.CurrentRegion.End(xlToRight).Column Then Exit Sub
'si la cellule modifiée est la dernière de la colonne, on sort
 
For Each Cel In Range(Target.Offset(0, 1), Cells(Target.Row, 256).End(xlToLeft))
    If Left(Cel.Formula, 1) <> "=" Then _
        Cel.FormulaR1C1 = "=RC[-1]"
Next Cel
End If
erreur:
Application.EnableEvents = True
If Err <> 0 Then MsgBox Err.Description, , "Erreur Excel n°" & Err.Number
On Error GoTo 0
End Sub

**lil404** · 02/06/2009, 15h32

Mais j'ai un peu l'impression que ca revient a sortir la grosse artillerie alors que l'utilisateur peu simplement faire copier la valeur de la cellule en cliquant dans le coin inférieure droit. Ou alors faire un Ctrl+Z en cas d'erreur.

Ben c'est aussi ce que j'ai pensé, sur le coup, mais l'utilisateur en question n'avait pas l'air d'accord, lui ^^" ..

Edit je vien de voir que Gorfael travail sur les colonnes et toi sur les lignes (enfin d'après ce que j'ai compris tu veux que la colonne F ligne 3 soit égale a E ligne 3)

Oui, effectivement, il y a eu une légère méprise entre les lignes et les colonnes. Tu as bien compris ce que je voulais faire.

Waw, ce code marche, en effet !
Mais là j'avoue que je ne serai pas contre une petite explication ^^'

Je n'ai pas réussi à trouver ce que signifiait "target", par exemple...
Ensuite, j'ai vu que target.offset(0, 1) déplaçait la sélection d'une colonne vers la droite ? pour quoi faire ?
Que signifie "Cells(Target.Row, 256).End(xlToLeft)" ?
Et puis quand on vérifie si la cellule commence par un "=", comment se fait-il que ça ne change pas immédiatement, quand on choisit une valeur dans une liste ? (le contenu de la cellule ne commence alors plus par un "=", et pourtant elle garde sa valeur...)

Edit :
Ce code fonctionne quand je le mets dans l'évennement change de la feuille, mais pas si j'en fais une fonction, contenue dans un module (c'est ce que j'ai fait dans tout mon code, afin que tout soit accessible depuis toutes les feuilles). (Il me marque alors Object requis). Pourquoi

? (ok, ça, vous pouvez peut-être pas y répondre, c'est peut-être une erreur bien à moi ^^' . Mais dans le doute...)

**Krovax** · 02/06/2009, 16h02

La fonction

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Private Sub Worksheet_Change(ByVal Target As Range)

se déclanche a chaque fois que l'on modifie l'onglet en question (la ou est le code c'est pour cela quiul ne fau pas le déplacé) Target correspond a la zone modifié

Avec pas mal de commentaire, jespère que cela sera plus clair

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
 
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo erreur  
' en cas d'erreure on va a la gestion d'erreur
 
Dim Cel As Range
 
If Not Intersect(Target, Range("A4:F7")) Is Nothing Then 
'on vérifie que l'intersection entre la zone modifié et le tableau n'est pas nul, donc que tu as bien modifié le tableau
 
Application.EnableEvents = False
'on désactive les évènement pour que la macro ne s'apelle pas en boucle puisquelle va modifier le tableau et donc appeler la macro qui s'exécute en cas de modification du tableau, ca tourne en boucle et fait perdre du temps
'on évite les boucles du aux changements
If Target.Cells.Count > 1 Then Exit Sub
'si il y a plus d'une cellule modifiée, on sort
 
If Target.Column = Target.CurrentRegion.End(xlToRight).Column Then Exit Sub
'si la cellule modifiée est la dernière de la colonne, on sort
'target.colum=numéro de la colonne modifié
'Target.CurrentRegion.End(xlToRight).Column=dernière collone de la région
 
For Each Cel In Range(Target.Offset(0, 1), Cells(Target.Row, 256).End(xlToLeft))
' Cells(Target.Row, 256).End(xlToLeft) signifie la dernière cellule vers la droite de la ligne modifié
'ce qui donne pour chaque cellule de la cellule a droite de celle modifé a la dernière de la ligne
    If Left(Cel.Formula, 1) <> "=" Then _
        Cel.FormulaR1C1 = "=RC[-1]"
Next Cel
End If
erreur:
Application.EnableEvents = True
'on remet les évènement
If Err <> 0 Then MsgBox Err.Description, , "Erreur Excel n°" & Err.Number
On Error GoTo 0
End Sub

Un peu de lecture qui pourras t'aider a comprendre
Dernière ligne et la dernière colonne renseignées d'une feuille
(la moitié des formule un peu "bizard sont ici"

pour savoir comment fonctionne Worksheetchange regarde le tuto
Les évènements dans la feuille de calcul Excel

**lil404** · 03/06/2009, 08h47

Ces quelques commentaires aident pas mal, en effet.
J'ai presque tout compris, encore juste une petite chose : que représente la "région" ?
Dans l'aide d'office, pour le mot clé "end", il est indiqué ceci :

Cet exemple montre comment sélectionner la cellule située en haut de la colonne B dans la région contenant la cellule B4
Range("B4").End(xlUp).Select

Mais qu'est-ce que la "région", dans notre cas ?? Comment se fait-il qu'un End(xlToLeft) renvoie la cellule la plus à droite ???

(et pourquoi dans un cas on utilise "Formula" et dans l'autre "FormulaR1C1" ?)

Et enfin, dernière chose, je comprends bien qu'il faille laisser le code dans l'évennement Change, mais je ne l'ai pas déplacé, je l'ai juste mis dans une fonction, qui, elle, est bien appelée dans Change. Pourtant, ça ne fonctionne pas ...

**Krovax** · 03/06/2009, 08h56

Si tu remplie toute les case de A à C que tu ne met rien dans D et que tu rempli de E à G
Tu a une premièe région de A a C donc si tu lance la commande a partir de A, ou C elle te renvoi C (ou plutot 3)
SI tu lance de E, F ou G elle te renvoie G (soit 7)

Pour les formule je ne les utilise pas bcp mais voila ce que j'en sais
"Formula" te permet de donner une formule avec des adresse absolut "=A1+A2"
La cellule est égale au contenue de A1plus celui de A2 ou que soit ta cellule

"FormulaR1C1" permet de donner les formule de manière relative "=R1C2+R-1C3"
Le contenue est égale a la cellule une ligne plus bas deux colonne a droite plus celle une ligne plus haut trois colonne a droite. Le résultat va donc dépendre de la position de ta formule

Voila j'espère que c'est suffisamment clair

**lil404** · 03/06/2009, 09h05

Pour les formula, ça l'est, merci =)

Pour la région également, d'ailleurs

Mais... pourquoi un ToLEFT sur la région composée de A, B, et C, renvoie la valeur à DROITE ? Il devrait plutot renvoyer A, non ???
Et puis, dans notre cas, ( Cells(Target.Row, 256) ), on prend la dernière cellule de la ligne modifiée, si j'ai bien suivi.. Mais tu sembles dire que la region est l'ensemble des cellules consécutives remplies, or, la dernière de la ligne n'est pas remplie. Il se passe quoi, dans ce cas ?

Mes excuses si je suis longue à comprendre ...
.

**lil404** · 03/06/2009, 09h20

Ah, j'ai compris pourquoi en passant par fonction, il me disait "Objet requis". Je n'avais pas pensé à passer le Target

Mais quand j'essaie, il me marque toujours objet requis. Voici mon code :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
Private Sub Worksheet_Change(ByVal Target As Range)
 
Modif (Target)
 
End Sub

Dans les arguments de "Modif", j'ai recopié exactement la même chose, "ByVal Target As Range". Pourquoi ne l'accepte-t-il pas ?

**LeForestier** · 03/06/2009, 09h42

Bonjour,
Si tu met des parenthèses...

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
Call Modif(Target)
ou bien
    Modif Target

A+

**lil404** · 03/06/2009, 10h10

Oooooh !
C'était si bête !!!
Merci

.

**ouskel'n'or** · 03/06/2009, 11h10

Juste parce que je n'ai vu cette indication nulle part : Place une formule en A2 où tu mets juste "= A1" (sans les guillemets) et teste

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
Sub test()
    MsgBox Cells(2, 1).Formula
End Sub

A toutes fins utiles

**lil404** · 03/06/2009, 11h24

.
Ca marque bien la formule entrée.
C'est ce que tu voulais vérifier ?

.

**ouskel'n'or** · 03/06/2009, 11h32

Non, je revenais à ta question de départ : Si tu testes

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
Dim ok as boolean
Ok = Instr(Cells(2, 1).Formula,"=") = 1 'ok = true si le premier caractère de la cellule est "="
If ok then msgbox "C'est une formule, non une valeur"

Tu peux tester ainsi s'il s'agit d'une formule ou d'une valeur... si j'ai bien compris ton problème...
Ensuite, sachant de quoi il s'agit, tu peux jouer avec le contenu de ta cellule (!)

**lil404** · 03/06/2009, 15h21

Mais si j'ai (enfin) bien compris le code que l'on m'a donné, c'est déjà le cas, quand on fait :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
    If Left(Cel.Formula, 1) <> "=" Then _
        Cel.FormulaR1C1 = "=RC[-1]"

Non ?

.

différencier contenu d'une cellule et formule

Macros et VBA Excel

Discussions similaires

Partager

Partager