Macro calcul de la valeur manquante

[iStarOSX]

Bonjour a tous.

Voila j'ai un petit tableau qui comporte une cellule "valeur total" qui porte bien son nom puisqu'elle comporte la valeur total de mon calcul.

Ensuite j'ai 2 colonnes, la première qui comporte les "%" et la seconde la valeur qui correspond au pourcentage.

Je cherche a faire une macro qui, lorsque l'on remplis un pourcentage, calcul la valeur et rempli la colonne valeur, et inversement si l'on saisi directement la valeur, le pourcentage soit calculé et rempli.

J'ai fait se bout de code, mais il tourne en boucle est fait planter Excel.

Private Sub Worksheet_Change(ByVal Target As Range)

'=========================================================================================================================================================
'POURCENTAGE:

If Application.Intersect(Target, Range("G18")) Is Nothing Then
Sheets("Sélection").Range("K18").ClearContents
Sheets("Sélection").Range("K18").FormulaR1C1 = "=R7C18*RC[-4]/100"
End If

If Not Application.Intersect(Target, Range("G19")) Is Nothing Then
Sheets("Sélection").Range("K19").ClearContents
Sheets("Sélection").Range("K19").FormulaR1C1 = "=R7C18*RC[-4]/100"
Application.ScreenUpdating = True
End If

If Not Application.Intersect(Target, Range("G20")) Is Nothing Then
Sheets("Sélection").Range("K20").ClearContents
Sheets("Sélection").Range("K20").FormulaR1C1 = "=R7C18*RC[-4]/100"
End If

If Not Application.Intersect(Target, Range("G21")) Is Nothing Then
Sheets("Sélection").Range("K21").ClearContents
Sheets("Sélection").Range("K21").FormulaR1C1 = "=R7C18*RC[-4]/100"
End If



'=========================================================================================================================================================
'valeur:
If Application.Intersect(Target, Range("K18")) Is Nothing Then
Sheets("Sélection").Range("G18").ClearContents
Sheets("Sélection").Range("G18").FormulaR1C1 = "=100/R7C18*RC[4]"
End If

If Not Application.Intersect(Target, Range("K19")) Is Nothing Then
Sheets("Sélection").Range("G20").ClearContents
Sheets("Sélection").Range("G19").FormulaR1C1 = "=100/R7C18*RC[4]"
Application.ScreenUpdating = True
End If

If Not Application.Intersect(Target, Range("K20")) Is Nothing Then
Sheets("Sélection").Range("G20").ClearContents
Sheets("Sélection").Range("G20").FormulaR1C1 = "=100/R7C18*RC[4]"
End If

If Not Application.Intersect(Target, Range("K21")) Is Nothing Then
Sheets("Sélection").Range("G21").ClearContents
Sheets("Sélection").Range("G21").FormulaR1C1 = "=100/R7C18*RC[4]"
End If


End Sub

Je joint un fichier exemple, mais ATTENTION ma macro fait planté excel.

Si quelqu'un peut me dire se qui ne va pas dans mon code, je vous en remercie d'avance.

 

[chasseur44]

Re : Macro calcul de la valeur manquante

Salut tu peux t'inspirer du doc joint ! en espérant que cela te convienne !

 

[iStarOSX]

Re : Macro calcul de la valeur manquante

Bonjour chasseur44 et merci beaucoup pour ton aide.

J'ai bien réussi a faire la macro que je cherchais a faire en partant de ton code (j'ai juste modifié les plage et le nom de ma cellule "total".

Par contre je ne comprend pas comment elle fonctionne et a quel moment elle calcul les pourcentages.

Si tu pouvais me donner quelque explications, merci.

 

[frangy]

Re : Macro calcul de la valeur manquante

Bonjour,

Ton code pose 2 problèmes :
1) En utilisant l’évènement Worksheet_Change, la macro est exécutée à chaque modification d’une cellule cible (G18 à G21 et K18 à K21). Mais comme une autre cellule cible est modifiée par la macro elle-même, celle-ci est relancée... tu obtiens une boucle à l’infini.
2) Le pourcentage en G18 est calculé en faisant référence à la valeur en K18.
La valeur en K18 est calculée en faisant référence au pourcentage en G18.
Quand une formule fait référence à elle-même indirectement par le biais d'une autre formule y faisant référence, cela s’appelle une référence circulaire.

Le premier problème est résolu en désactivant l’événement en début de procédure.
Application.EnableEvents=False
Et en le réactivant en fin de procédure
Application.EnableEvents=True.

Le deuxième problème est résolu en évitant l’utilisation de formules.
C’est alors la procédure qui est chargée d’effectuer les calculs.

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    If Target.Count = 1 Then
        If Not Application.Intersect(Target, Union(Range("G18:G21"), Range("K18:K21"))) Is Nothing Then
            'Si la modification est sur le pourcentage (colonne G), on calcule la valeur (colonne K)
            If Target.Column = 7 Then
                Target.Offset(0, 4) = Range("R7") * Target.Value / 100
            'Sinon, la modification est sur la valeur (colonne K) et on calcule le pourcentage (colonne G)
            Else
                Target.Offset(0, -4) = 100 / Range("R7") * Target.Value
            End If
        End If
    End If
    Application.EnableEvents = True
End Sub

Cordialement.

 

[iStarOSX]

Re : Macro calcul de la valeur manquante

Bonjour frangy et merci pour ton aide.

Le code que tu m'a passé fonctionne très bien, si ce n'est que lorsque l'on saisie plusieurs cellules en même temps, celui si ne prend en compte que la première cellule et les erreur ne sont pas gérées.

Dans le code de chasseur44, il y a

On Error Resume Next 'sécurité
Application.EnableEvents = False 'désactive l'action des évènements
For Each Target In Target 'si plusieurs valeurs sont entrées simultanément

'et en fin d'instruction :

Next
Application.EnableEvents = True
End Sub

J'ai essayé de compiler le code de chasseur44 avec le tien, mais cela ne fonctionne pas.

J'ai testé ceci :

Private Sub Worksheet_Change(ByVal Target As Range)

Dim plage As Range
Set plage = Union(Range("G27:G32"), Range("K27:K32"))
Set Target = Intersect(Target, plage)
If Target Is Nothing Then Exit Sub
On Error Resume Next 'sécurité
Application.EnableEvents = False 'désactive l'action des évènements
For Each Target In Target 'si plusieurs valeurs sont entrées simultanément
  If Target.Column = plage.Column Then 'colonne G
     Application.EnableEvents = True
     Target.Offset(0, 1) = Range("R7") * Target.Value / 100 'pour recalcul des cellules
     Application.EnableEvents = False
  ElseIf Target.Column = plage.Column + 4 Then 'colonne K
     Target.Offset(0, -1) = 100 / Range("R7") * Target.Value
  End If
Next
Application.EnableEvents = True
End Sub


Mais sans succès.

 

[frangy]

Re : Macro calcul de la valeur manquante

Re-bonjour iStarOSX,

J'ai adapté ton code. Regarde si c'est bien ce que tu cherches à obtenir.
La modification de plusieurs cellules en simultané reste risquée.

 

[iStarOSX]

Re : Macro calcul de la valeur manquante

Merci beaucoup frangy, cela fonctionne a merveille.

Je te remercie pour toute ton aide.

A bientôt