recopier une formule sur une colonne et sur plusieurs feuilles en VBA

[riegel]

Bonjour à tous,

J’utilise un classeur qui comporte plus de 120 feuilles, chaque feuille étant identique en colonnes, seul le nombre de ligne varie. Je rencontre deux problèmes majeurs : en VBA, écrire une formule qui se répète sur toute une colonne au fur et à mesure que les lignes se remplissent et dupliquer la formule trouvée sur chaque feuille.

Je souhaiterais en colonne E sur chaque feuille qu’il y ait la formule suivante en macro à partir de la cellule E4 : =SI(OU(B4<> « » ; C4<> «») ;E3+B4-C4 ; «»).
La formule en tant que telle en VBA ne devrait pas trop me poser de problème, par contre, je ne sais pas comment faire pour que la formule se répète sur toute la colonne uniquement quand B4 ou C4 ne sont pas vides.
Par ailleurs, je n’ai aucune idée de la manière de faire dupliquer cette formule sur chaque feuille.
En E3 (à dupliquer sur chaque feuille), la formule serait : =SI(OU(B4<> « » ; C4<> «») ; B4-C4 ; «»).

J’espère que ma demande est compréhensible. D’avance, je vous remercie pour le temps pris pour me répondre.

Riegel

 

[Gorfael]

Re : recopier une formule sur une colonne et sur plusieurs feuilles en VBA

Salut riegel et le forum
Comme toujours, pas sûr d'avoir tout compris

je ne sais pas comment faire pour que la formule se répète sur toute la colonne uniquement quand B4 ou C4 ne sont pas vides.

Ce que je lis : quelque soit la ligne en cours, il faut une valeur en ligne 4 soit sur B soit sur C.

Ce que je suppose, c'est que pour une ligne, si la colonne B ou/et la colonne C ont une valeur, en E, tu mets ta formule ="Ey+Bx-Cx (avec y=x-1)
Si c'est dans ce style, une macro change devrait faire l'affaire. Pour répondre à ta seconde question, j'emploierai "Workbook_SheetChange" du module ThisWorkBook : celle-ci se déclenche quelque soit la feuille du classeur, avec Sh, variable contenant la feuille concernée, et Target, variable contenant les cellules modifiées.
Ce qui donne un code dans ce style :

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim Cel As Range, Plage As Range, x
'Validité -------------------------
Set Cel = IIf(Cells(Rows.Count, "C").End(xlUp).Row < Cells(Rows.Count, "B").End(xlUp).Row, _
             Range("C" & Cells(Rows.Count, "B").End(xlUp).Row), Cells(Rows.Count, "C").End(xlUp))
Set Plage = Intersect(Target, Range([B2], Cel))
'Plage = cellules modifiées de B1 à C & plus gand numéro dernière ligne non-vide de B ou C
If Plage Is Nothing Then Exit Sub
'Si ça ne concerne pas les colonnes B ou C, sortir
'Traitement -----------------------
For Each Cel In Plage
'Pour chaque cellule de plage
    If Cells(Cel.Row, "B") & Cells(Cel.Row, "C") = 0 Then
    ' B+C de la ligne de cel = 0 (ça veut dire que B et C sont vides), alors
        Cells(Cel.Row, "E").ClearContents
        'On efface le contenu de E
    Else
    'Sinon
        Cells(Cel.Row, "E").FormulaLocal = "=" & Cells(Cel.Row - 1, "E").Address(0, 0) & _
            "+" & Cells(Cel.Row, "B").Address(0, 0) & "-" & Cells(Cel.Row, "C").Address(0, 0)
        'Formule en français de E = "= E(ligne précédente) + B - C"
    End If
    'Fin test
Next Cel
'cellule suivante
End Sub

Attention : c'est une macro automatique => elle s'exécute que tu le veuilles ou non. Si, par exemple tu veux intercaler un titre (ou une valeur quelconque en A, sans valeur en B ou en C), ça repartira à 0 pour E.
Le test

If Cells(Cel.Row, "B") & Cells(Cel.Row, "C") = 0 Then

n'est pas forcément le plus adapté. Tu pourrais le remplacer par un simple test de A<>"", car je présume que tu auras toujours un titre en A, pour justifier B et C. Mais sans connaissance réelle du problème...
A+

 

[riegel]

Re : recopier une formule sur une colonne et sur plusieurs feuilles en VBA

Merci beaucoup Gorfael pour ta réponse.

Je pense effectivement n'avoir pas été suffisamment clair, et surtout à te lire il existe une piste plus simple. Je cherche effectivement à appliquer la formule "Ey+Bx-Cx (avec y=x-1) en E mais si la colonne A n'est pas vide convient mieux finalement. L'idée est que dès que je saisis une date en A, la formule en E s'applique.

J'ai utilisé ton code, malheureusement cela ne fonctionne pas. Comme je n'y comprend pas grand chose, je suis incapable d'y remédier. Je joins un fichier (je n'avais pas pensé à le zipper la première fois !) pour plus de clarté, j'espère.

Encore merci pour ta réponse.

 

[Gorfael]

Re : recopier une formule sur une colonne et sur plusieurs feuilles en VBA

Salut riegel et le forum

J'ai utilisé ton code

Non ! Si tu l'avais utilisé, il aurait fonctionné : je l'ai testé avant !
Tu as adapté un code que tu ne comprenais pas, ce qui en fait une daube.

'Validité -------------------------
'Set Cel = If(Cells(Rows.Count, "C").End(xlUp).Row < Cells(Rows.Count, "B").End(xlUp).Row,_
'Range("C" & Cells(Rows.Count, "B").End(xlUp).Row), Cells(Rows.Count, "C").End(xlUp))

Vérifie avec mon poste précédent, et hormis que tu l'as transformé en commentaire, ce n'est pas le même : Je n'utilise pas If, qui déclenche forcément une erreur, la syntaxe étant fausse, mais Iif.
Quand on te donne un code, tu fais un copier/coller sur le module : si ça ne fonctionne pas, alors là, tu peux envisager de l'adapter, si tu le comprends. Sinon, tu précises les données qui ne te permettent pas une adaptation facile.

Set Cel = Iif(condition,vrai, faux)
Le set permet de fixer la variable Cel en tant que plage (range), plutôt que de valeur.
Le Iif est une fonction Si, comme sur la feuille de calcul.
Après l'instruction, Cel sera la cellule de C qui est soit sur la ligne de la dernière valeur non-vide de B, soit la dernière cellule non-vide de C.

Si tu regardes l'instruction suivante, tu vois que l'on définit Plage (instruction set) comme étant les cellules modifiée (Target) appartenant (intersect) à la plage de B2 à Cel => si Cel n'est pas fixée par une instruction set, elle est à nothing (rien, pas définie) et doit sans doute déclencher une erreur soit Excel, soit de fonctionnement.

'If Plage Is Nothing Then Exit Sub

Là encore, une mise en commentaire inopportune : si tu modifies une cellule de D, Plage reste à nothing => ça ne concerne pas la macro, il n'y a pas de traitement à faire, et on sort.

Sans commentaire explicatif, en s'appuyant sur A non-vide, la macro devient :

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim Cel As Range, Plage As Range

Set Plage = Intersect(Target, Range([A3], Cells(Rows.Count, "A").End(xlUp)))
If Plage Is Nothing Then Exit Sub

For Each Cel In Plage
    If Cells(Cel.Row, "B") & Cells(Cel.Row, "C") = 0 Then
        Cells(Cel.Row, "E").ClearContents
    Else
        Cells(Cel.Row, "E").FormulaLocal = "=" & Cells(Cel.Row - 1, "E").Address(0, 0) & _
            "+" & Cells(Cel.Row, "B").Address(0, 0) & "-" & Cells(Cel.Row, "C").Address(0, 0)
    End If
Next Cel
End Sub

Nota : elle fonctionne très bien chez moi
Je ne commence qu'à la troisième ligne, puisque la première sert au titre et que la seconde sert à définir le montant de départ, soit directement en E, soit en B ou C, mais c'est plus simple de la faire à la main que par macro, qui rajoute du code inutile.

J'utilise une boucle, parce que tu peux être amené à faire du copier/coller de plusieurs cellules.

Pour être clair : quand on donne un code, utilise-le tel quel, sur un fichier d'essai (qui peut être une copie de celui de travail), et tu le testes.

S'il ne répond pas à tes besoins, et que tu le comprends, tu l'adaptes.

Si tu ne le comprends pas, tu demandes des explications supplémentaires, sur ce que tu n'as pas compris, en joignant tes modifications, ou comme tu l'as fait, un fichier d'essais.
Et tu demandes des précisions tant que tu ne l'as pas complètement compris, instruction par instruction.

Le but n'est pas d'avoir un code, mais de comprendre comment le faire.
A+

 

[riegel]

Re : recopier une formule sur une colonne et sur plusieurs feuilles en VBA

Gorfael,

Ton agacement me laisse pantois. Avant de créer une discussion sur ce forum, je passe toujours plusieurs heures afin de m'assurer que je ne puisse pas trouver la solution à mes problèmes par moi-même ce qui m'a permis de constater que parfois des contributeurs se trompent dans leur code et le corrigent. Quand j'ai utilisé ton code, j'ai eu un message d'erreur de syntaxe. C'est pourquoi j'ai corrigé l'instruction Iif (que je ne connaissais pas), ensuite j'ai mis en commentaire chaque ligne une à une pour comprendre ce qui se passait. Bien sûr compte tenu de mon faible niveau en Vba, j'ai très vite dénaturé le code et n'ai plus rien compris.
Saches que je ne me contente pas d'obtenir du code, mais bien de le comprendre pour pouvoir avancer sur d'autres projets. Je suis désolé si je t'ai donné l'impression que tel était le cas, mais ce n'est absolument pas mon but.
Je te renouvelle mes remerciements puisque tu as répondu à mes attentes que ce soit la première fois ou la deuxième (qui me satisfait plus).

Cordialement,

Riegel.