Microsoft 365 Macro évenementielle et test de résultat

[cedricodelavega]

Bonjour,
Je viens de me créer un fichier de cash flow, a peu près automatisé selon des critères modifiables et ajustables.
Afin de peaufiner cet outil, je voudrai arriver à lancer une macro événementielle :
- Dès que la cellule B22 ou D22, ou D27 est modifiée, je voudrai lancer une procédure de test sur les cellules K34 à AE34. (Cette ligne correspond à un prix de vente hypothétique (qui est précalculé dans la ligne 35)), et ce prix de vente, génère une VAN située en I38.
Je voudrai que la procédure affiche le prix de vente cellule par cellule, jusqu'à trouver la meilleure VAN possible.

Grosso modo, la procédure s'arrêterai une fois que le test de la cellule précédente (de la ligne 34) donne une meilleur VAN (i38) que le test de la cellule suivante (située sur la ligne 34). (Je ne sais pas si c'est très clair?)
J'ai eu beau essayer d'enregistrer une macro manuelle, et de la coller à une macro évènementielle, mais je manque cruellement de grammaire en langage VBA.
Je vous remercie d'avance pour vos lumières !!

 

[sylvanu]

Bonjour Cedric,
Voici un code Worksheet_Change qui s'active quand on change la valeur de B22, D22 ou D27.
Pour l'instant elle n'affiche qu'une MsgBox avec adresse et donnée.
Vous remplacez la msgbox par le code qui vous intéresse.

Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    ValA = Target.Address
    If ValA = "$B$22" Or ValA = "$D$22" Or ValA = "$D$27" Then
   
        ' Votre macro de traitement
        MsgBox "Vous venez de modifier la cellule " & Target.Address & _
        Chr(10) & Chr(13) & " ( Valeur : " & Target.Value & " )"
       
       
    End If
End Sub

 

[cedricodelavega]

Bonsoir Sylvanu,
Merci pour le code de macro évènementielle.
J'essaie de bidouiller avec un "do while", et j'essaie de contourner le pb en créant une macro qui donne quelque chose comme :

Do
Range("L34").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=R[1]C"
End Sub

Mais je ne sais pas comment comparer le résultat occasionné en I38, à l'action précédente :

Do
Range("K34").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=R[1]C"
End Sub

je n'arrive pas à écrire l'action que la macro devrait faire : à savoir :
Exécuter l'opération décrite précédement sur les cellule M34, N34, O34 etc... jusqu'à ce que (until...) le résultat en I38 soit supérieur à l'action précédente...

je tâtonne quoi...

 

[sylvanu]

Vraiment désolé. Vos explications sont particulièrement obscures.
Sur la ligne 34 je n'ai pas de formule excepté quelque part =L35
Je comprends que vous voulez cherchez un optimum, mais sur quelle variable ? En jouant sur quel paramètre?
essayer d'expliquer, non pas en cellule, mais en facteurs.

 

[cedricodelavega]

Ah d'accord. Sorry.
Les prix de vente hypothétiques sont pré calculés en ligne 35. (qui est en blanc)
Afin de comptabiliser le prix de vente dans ma VAN (I38), je fais apparaitre le prix de vente en L34. Pour cela, je fais L34=L35. (et je teste cela sur chaque cellule de la ligne 34 pour savoir quelle est le meilleur moment pour vendre)

Ainsi, je souhaiterai que la procédure teste l'opération L34=L35 (cette opération comptabilise le prix de vente) et regarde le résultat de la VAN en I38.
Si le résultat de I38 (effectué par l'opération L34=L35) est supérieur au résultat de I38 (effectué par l'opération K34=K35), on continue la procédure sur les cellules suivantes jusqu'à ce que la VAN soit inférieure à celle du teste précédent. Ainsi, je saurai quelle est l'année ou je dois vendre en retirant la meilleure VAN possible.

Est ce plus clair ?
Quoi qu'il en soit, merci beaucoup pour votre aide

 

[sylvanu]

Pas tout compris.
Mais pourquoi ne pas faire toutes les simulations de 2020 à 2040, puis chercher l'optimum ?

 

[cedricodelavega]

Pas tout compris.
Mais pourquoi ne pas faire toutes les simulations de 2020 à 2040, puis chercher l'optimum ?

Si il y a une macro qui fait ca, je prends
Sachant que mes critères variables sont les 3 cellules de taux afin de déterminer quel est le meilleur moment pour vendre...

Sinon, a chaque modification d'un des trois taux, je dois retester chaque année. j'essayais d'automatister la procédure test en quelques sortes...

 

[sylvanu]

Il faut déjà remplir tout le tableau avec les données, je ne sais pas d'où elles viennent.

 

[cedricodelavega]

Il faut déjà remplir tout le tableau avec les données, je ne sais pas d'où elles viennent.

Ce sont des données "automatisées" en fonction de critères comme la surface, le prix/m², le montant des charges etc..... et aussi issue du tableau d'amortissement en bas a gauche, lui aussi automtisé en fonction de la durée du crédit...
C'est pour cela que je voulais comparer les resultat de la VAN pour chaque opération décrite plus haut, pour ne conserver (ou afficher automatiquement le prix de vente dans la cellule qui pemrmet la meilleure VAN sur l'echelle de temps du tableau

 

[sylvanu]

J'ai toujours pas tout compris.
Donc là je part du principe que toutes les années sont remplies, et je cherche l'optimum.
optimum que je colle en I38.

Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    ValA = Target.Address
    If ValA = "$B$22" Or ValA = "$D$22" Or ValA = "$D$27" Then
        Set Plage = Range("K34:AE34")
        For i = 2 To 21
            If Plage.Cells(1, i).Value > Plage.Cells(1, i + 1).Value Then
                Range("I38")=Plage.Cells(1, i).Value
                Exit Sub
            End If
        Next i
    End If
End Sub

Mais si c'est ça que vous voulez faire, il y a plus simple.
Vous remplissez toutes les colonnes et en I38 vous mettez :

=INDEX(K34:AE34;EQUIV(MAX(K34:AE34);K34:AE34;0))

cela sera raffraichit à chaque fois que vous changerez un taux.

 

[cedricodelavega]

Bonsoir Sylvanu,
Merci infiniment pour votre aide, mais, ce n'est pas vraiment ca que je cherche à faire.

Disons que, lorsque je fais L34=L35, la cellule L34 affiche le prix de vente supposé. Ce prix de vente supposé génère une VAN (Valeur actualisé nette) dans la cellule I38.
Lorsque je fais M34=M35, la cellule M34 affiche le prix supposé de l'année suivante à la cellule L34. Ce prix de vente génère une vente VAN différente de l'opération L34=L35.
De ce fait, je voudrai tester le prix de vente de chaque année situé sur la ligne 34, qui me permet d'obtenir la meilleure VAN en I38.
est ce plus clair ?

 

[sylvanu]

Bonjour Cedric,
Une proposition:
Sur la ligne 34 remplacer les formules =M35 par =SI(M33=1;M35;0) en d'autre termes le calcul ne s'effectue que si M33=1.
Puis intégrer cette macro dans le module Worsheet_Change :

Sub CalculVAN()
Dim ArrayVAN()
Set FlagVAN = Range("K33:AE33")
For i = 1 To 21                         ' Pour toutes les années
    FlagVAN.ClearContents               ' Effacer tous les flag
    FlagVAN.Cells(i, 1) = 1             ' Positionner un flag sur une année
    Calculate                           ' et faire le calcul
    ArrayVAN(i) = Range("I38")          ' mémoriser la valeur VAN obtenue
Next i
FlagVAN.ClearContents                   ' Effacer tous les flag
MaxVAN = ArrayVAN(1)
For i = 2 To 21
    If ArrayVAN(i) > MaxVAN Then        ' Chercher le max VAN dans le tableau
        MaxVAN = ArrayVAN(i)
    Else
        MsgBox ("Valeur max de VAN obtenue pour " & Range("K8:AE8") & _
        Chr(10) & Chr(13) & " . Valeur : " & MaxVAN)
        Exit Sub
    End If
End Sub

En clair, je met un 1 dans une cellule ligne 33, je calcule et je mémorise la valeur de VAN.
Je fais ça pour toutes les colonnes K à AE.
A la fin j'ai un tableau de toutes les valeurs VAN possibles. Et je cherche dans ce tableau quelle est la valeur VAN maximale.
( à noter que je considère que la valeur VAN est croissante puis décroissante et je retourne le premier max trouvé )

 

[sylvanu]

Re Bonjour,
En PJ un essai sans macro.
Si tout le tableau est rempli, et que pour chaque année on calcul le VAN alors tout peut être fait par formules sans macro pour cette partie.
Non seulement c'est plus simple, mais plus rapide.
Est ce une solution ?

 

[cedricodelavega]

Bonjour Sylvanu,
Je suis en train de tester vos deux méthodes (merci beaucoup !!! )
Je reviens d'ici qques minutes pour vous dire

 

[cedricodelavega]

Rebonjour,
Pour la macro, j'ai bien remplacé la formule en ligne 34, et intégré la macro dans le module, mais j'ai un message d'eereur qui apparait tout le temps. Je dois surment faire une boulette qqpart...

Concernant la méthode sans macro, c'est tout à fait l'idée, mais il semble que quand je le fasse cellule par cellule, je ne tombe pas sur les même résultats.
Je vérifie les formules de calcul...