Microsoft 365 Code VBA avec plusieurs conditions SI

2pi_N

XLDnaute Nouveau
Bonjour,

Je suis débutant en programmation VBA. J'ai écrit une procédure pour effectuer un calcul conditionnel mais lorsque je lance la macro, aucun résultat ne s'affiche (il ne se produit rien) comme je le voudrais. Je ne sais pas où j'ai commis une erreur. J'ai préféré passer par VBA au lieu d'une formule avec SI directement dans Excel car j'ai 18 conditions (ça serait trop volumineux pour une cellule).
VB:
Sub Projection_production()

Dim quart As Byte ' numéro de quart
Dim emballe As Byte ' quantité de tables emballées
Dim reparation As Byte ' quantité de tables à décaper
Dim projection As Single ' projection de la production

quart = Worksheets("Feuil2").Range("B3")
emballe = Worksheets("Feuil2").Range("C3")
reparation = Worksheets("Feuil2").Range("D3")
projection = Worksheets("Feuil2").Range("C6")

If quart = 1 Then
projection = (1.1732 * emballe) + 168.535 - reparation
ElseIf quart = 2 Then
projection = (1.35112 * emballe) + 152.738 - reparation
ElseIf quart = 3 Then
projection = (-0.0289901 * (emballe) ^ 2) + (3.38947 * emballe) + 104.08 - reparation
ElseIf quart = 4 Then
projection = (-0.0140713 * (emballe) ^ 2) + (2.81667 * emballe) + 88.9892 - reparation
ElseIf quart = 5 Then
projection = ((0.029762) * emballe ^ (1.85892)) + 135.756 - reparation
ElseIf quart = 6 Then
projection = (0.00405467 * (emballe) ^ 2) + (1.26597 * emballe) + 97.0924 - reparation
ElseIf quart = 7 Then
projection = (-0.0255767 * (emballe) ^ 2) + (5.11439 * emballe) - 45.6093 - reparation
ElseIf quart = 8 Then
projection = (-0.0190742 * (emballe) ^ 2) + (4.50296 * emballe) - 51.8346 - reparation
ElseIf quart = 9 Then
projection = (-0.0112471 * (emballe) ^ 2) + (3.27573 * emballe) - 18.4008 - reparation
ElseIf quart = 10 Then
projection = (-0.012623 * (emballe) ^ 2) + (3.5508 * emballe) - 39.067 - reparation
ElseIf quart = 11 Then
projection = (-0.00520585 * (emballe) ^ 2) + (2.29414 * emballe) - 1.13597 - reparation
ElseIf quart = 12 Then
projection = (-0.0046817 * (emballe) ^ 2) + (2.2468 * emballe) - 14.7206 - reparation
ElseIf quart = 13 Then
projection = (1.13711 * emballe) + 38.7858 - reparation
ElseIf quart = 14 Then
projection = (1.12553 * emballe) + 28.904 - reparation
ElseIf quart = 15 Then
projection = (1.10336 * emballe) + 26.1241 - reparation
ElseIf quart = 16 Then
projection = (1.03689 * emballe) + 23.6494 - reparation
ElseIf quart = 17 Then
projection = (0.984626 * emballe) + 20.7106 - reparation
ElseIf quart = 18 Then
projection = (0.92043 * emballe) + 21.7956 - reparation
Else: projection = 200

End If

End Sub

Pouvez-vous m'aider s'il vous plaît ?

Merci d'avance
 

Pièces jointes

  • Projection journalière production.xlsm
    350.9 KB · Affichages: 8

D.D.

XLDnaute Impliqué
Bonjour,
Essaye:
VB:
Sub Projection_production()
    Dim Quart As Byte ' numéro de quart
    Dim Emballe As Byte ' quantité de tables emballées
    Dim Reparation As Byte ' quantité de tables à décaper
    Dim Projection As Single ' projection de la production
    
    Quart = Worksheets("Feuil2").Range("B3")
    Emballe = Worksheets("Feuil2").Range("C3")
    Reparation = Worksheets("Feuil2").Range("D3")
    Projection = 0
    
    If Quart = 1 Then Projection = (1.1732 * Emballe) + 168.535 - Reparation
    If Quart = 2 Then Projection = (1.35112 * Emballe) + 152.738 - Reparation
    If Quart = 3 Then Projection = (-0.0289901 * (Emballe) ^ 2) + (3.38947 * Emballe) + 104.08 - Reparation
    If Quart = 4 Then Projection = (-0.0140713 * (Emballe) ^ 2) + (2.81667 * Emballe) + 88.9892 - Reparation
    If Quart = 5 Then Projection = ((0.029762) * Emballe ^ (1.85892)) + 135.756 - Reparation
    If Quart = 6 Then Projection = (0.00405467 * (Emballe) ^ 2) + (1.26597 * Emballe) + 97.0924 - Reparation
    If Quart = 7 Then Projection = (-0.0255767 * (Emballe) ^ 2) + (5.11439 * Emballe) - 45.6093 - Reparation
    If Quart = 8 Then Projection = (-0.0190742 * (Emballe) ^ 2) + (4.50296 * Emballe) - 51.8346 - Reparation
    If Quart = 9 Then Projection = (-0.0112471 * (Emballe) ^ 2) + (3.27573 * Emballe) - 18.4008 - Reparation
    If Quart = 10 Then Projection = (-0.012623 * (Emballe) ^ 2) + (3.5508 * Emballe) - 39.067 - Reparation
    If Quart = 11 Then Projection = (-0.00520585 * (Emballe) ^ 2) + (2.29414 * Emballe) - 1.13597 - Reparation
    If Quart = 12 Then Projection = (-0.0046817 * (Emballe) ^ 2) + (2.2468 * Emballe) - 14.7206 - Reparation
    If Quart = 13 Then Projection = (1.13711 * Emballe) + 38.7858 - Reparation
    If Quart = 14 Then Projection = (1.12553 * Emballe) + 28.904 - Reparation
    If Quart = 15 Then Projection = (1.10336 * Emballe) + 26.1241 - Reparation
    If Quart = 16 Then Projection = (1.03689 * Emballe) + 23.6494 - Reparation
    If Quart = 17 Then Projection = (0.984626 * Emballe) + 20.7106 - Reparation
    If Quart = 18 Then Projection = (0.92043 * Emballe) + 21.7956 - Reparation
    If Projection = 0 Then Projection = 200
    
    Worksheets("Feuil2").Range("C6") = Projection
End Sub
 

Hasco

XLDnaute Barbatruc
Repose en paix
Bonjour,

Une solution parmi d'autres : en déclarant la variable Projection en tant que Range au lieu de Byte et l'initialiser avec set :

VB:
Sub Projection_production()

    Dim quart As Byte    ' numéro de quart
    Dim emballe As Byte    ' quantité de tables emballées
    Dim reparation As Byte    ' quantité de tables à décaper
    Dim Projection As Range    ' projection de la production

    quart = Worksheets("Feuil2").Range("B3")
    emballe = Worksheets("Feuil2").Range("C3")
    reparation = Worksheets("Feuil2").Range("D3")
    Set Projection = Worksheets("Feuil2").Range("C6")

    If quart = 1 Then
        Projection = (1.1732 * emballe) + 168.535 - reparation
    ElseIf quart = 2 Then
        Projection = (1.35112 * emballe) + 152.738 - reparation
    ElseIf quart = 3 Then
        Projection = (-0.0289901 * (emballe) ^ 2) + (3.38947 * emballe) + 104.08 - reparation
    ElseIf quart = 4 Then
        Projection = (-0.0140713 * (emballe) ^ 2) + (2.81667 * emballe) + 88.9892 - reparation
    ElseIf quart = 5 Then
        Projection = ((0.029762) * emballe ^ (1.85892)) + 135.756 - reparation
    ElseIf quart = 6 Then
        Projection = (0.00405467 * (emballe) ^ 2) + (1.26597 * emballe) + 97.0924 - reparation
    ElseIf quart = 7 Then
        Projection = (-0.0255767 * (emballe) ^ 2) + (5.11439 * emballe) - 45.6093 - reparation
    ElseIf quart = 8 Then
        Projection = (-0.0190742 * (emballe) ^ 2) + (4.50296 * emballe) - 51.8346 - reparation
    ElseIf quart = 9 Then
        Projection = (-0.0112471 * (emballe) ^ 2) + (3.27573 * emballe) - 18.4008 - reparation
    ElseIf quart = 10 Then
        Projection = (-0.012623 * (emballe) ^ 2) + (3.5508 * emballe) - 39.067 - reparation
    ElseIf quart = 11 Then
        Projection = (-0.00520585 * (emballe) ^ 2) + (2.29414 * emballe) - 1.13597 - reparation
    ElseIf quart = 12 Then
        Projection = (-0.0046817 * (emballe) ^ 2) + (2.2468 * emballe) - 14.7206 - reparation
    ElseIf quart = 13 Then
        Projection = (1.13711 * emballe) + 38.7858 - reparation
    ElseIf quart = 14 Then
        Projection = (1.12553 * emballe) + 28.904 - reparation
    ElseIf quart = 15 Then
        Projection = (1.10336 * emballe) + 26.1241 - reparation
    ElseIf quart = 16 Then
        Projection = (1.03689 * emballe) + 23.6494 - reparation
    ElseIf quart = 17 Then
        Projection = (0.984626 * emballe) + 20.7106 - reparation
    ElseIf quart = 18 Then
        Projection = (0.92043 * emballe) + 21.7956 - reparation
    Else: Projection = 200

    End If

End Sub

Cordialement
 

Phil69970

XLDnaute Barbatruc
Bonjour à tous

Et autrement ma version .....

VB:
Sub Projection_production()
Dim quart As Byte ' numéro de quart
Dim emballe As Byte ' quantité de tables emballées
Dim reparation As Byte ' quantité de tables à décaper
Dim projection As Single ' projection de la production

With Worksheets("Feuil2")
    quart = .Range("B3")
    emballe = .Range("C3")
    reparation = .Range("D3")
    projection = .Range("C6")

    Select Case quart
        Case 1
            projection = (1.1732 * emballe) + 168.535 - reparation
        Case 2
            projection = (1.35112 * emballe) + 152.738 - reparation
        Case 3
            projection = (-0.0289901 * (emballe) ^ 2) + (3.38947 * emballe) + 104.08 - reparation
        Case 4
            projection = (-0.0140713 * (emballe) ^ 2) + (2.81667 * emballe) + 88.9892 - reparation
        Case 5
            projection = ((0.029762) * emballe ^ (1.85892)) + 135.756 - reparation
        Case 6
            projection = (0.00405467 * (emballe) ^ 2) + (1.26597 * emballe) + 97.0924 - reparation
        Case 7
            projection = (-0.0255767 * (emballe) ^ 2) + (5.11439 * emballe) - 45.6093 - reparation
        Case 8
            projection = (-0.0190742 * (emballe) ^ 2) + (4.50296 * emballe) - 51.8346 - reparation
        Case 9
            projection = (-0.0112471 * (emballe) ^ 2) + (3.27573 * emballe) - 18.4008 - reparation
        Case 10
            projection = (-0.012623 * (emballe) ^ 2) + (3.5508 * emballe) - 39.067 - reparation
        Case 11
            projection = (-0.00520585 * (emballe) ^ 2) + (2.29414 * emballe) - 1.13597 - reparation
        Case 12
            projection = (-0.0046817 * (emballe) ^ 2) + (2.2468 * emballe) - 14.7206 - reparation
        Case 13
            projection = (1.13711 * emballe) + 38.7858 - reparation
        Case 14
            projection = (1.12553 * emballe) + 28.904 - reparation
        Case 15
            projection = (1.10336 * emballe) + 26.1241 - reparation
        Case 16
            projection = (1.03689 * emballe) + 23.6494 - reparation
        Case 17
            projection = (0.984626 * emballe) + 20.7106 - reparation
        Case 18
            projection = (0.92043 * emballe) + 21.7956 - reparation
        Case Else
            projection = 200
    End Select
    .Range("C6") = projection
End With

End Sub

Merci de ton retour

@Phil69970
 

D.D.

XLDnaute Impliqué
Bonjour à tous

Et autrement ma version .....

VB:
Sub Projection_production()
Dim quart As Byte ' numéro de quart
Dim emballe As Byte ' quantité de tables emballées
Dim reparation As Byte ' quantité de tables à décaper
Dim projection As Single ' projection de la production

With Worksheets("Feuil2")
    quart = .Range("B3")
    emballe = .Range("C3")
    reparation = .Range("D3")
    projection = .Range("C6")

    Select Case quart
        Case 1
            projection = (1.1732 * emballe) + 168.535 - reparation
        Case 2
            projection = (1.35112 * emballe) + 152.738 - reparation
        Case 3
            projection = (-0.0289901 * (emballe) ^ 2) + (3.38947 * emballe) + 104.08 - reparation
        Case 4
            projection = (-0.0140713 * (emballe) ^ 2) + (2.81667 * emballe) + 88.9892 - reparation
        Case 5
            projection = ((0.029762) * emballe ^ (1.85892)) + 135.756 - reparation
        Case 6
            projection = (0.00405467 * (emballe) ^ 2) + (1.26597 * emballe) + 97.0924 - reparation
        Case 7
            projection = (-0.0255767 * (emballe) ^ 2) + (5.11439 * emballe) - 45.6093 - reparation
        Case 8
            projection = (-0.0190742 * (emballe) ^ 2) + (4.50296 * emballe) - 51.8346 - reparation
        Case 9
            projection = (-0.0112471 * (emballe) ^ 2) + (3.27573 * emballe) - 18.4008 - reparation
        Case 10
            projection = (-0.012623 * (emballe) ^ 2) + (3.5508 * emballe) - 39.067 - reparation
        Case 11
            projection = (-0.00520585 * (emballe) ^ 2) + (2.29414 * emballe) - 1.13597 - reparation
        Case 12
            projection = (-0.0046817 * (emballe) ^ 2) + (2.2468 * emballe) - 14.7206 - reparation
        Case 13
            projection = (1.13711 * emballe) + 38.7858 - reparation
        Case 14
            projection = (1.12553 * emballe) + 28.904 - reparation
        Case 15
            projection = (1.10336 * emballe) + 26.1241 - reparation
        Case 16
            projection = (1.03689 * emballe) + 23.6494 - reparation
        Case 17
            projection = (0.984626 * emballe) + 20.7106 - reparation
        Case 18
            projection = (0.92043 * emballe) + 21.7956 - reparation
        Case Else
            projection = 200
    End Select
    .Range("C6") = projection
End With

End Sub

Merci de ton retour

@Phil69970
J'aime bien les "Case".
 

Statistiques des forums

Discussions
313 263
Messages
2 096 655
Membres
106 701
dernier inscrit
KOFFI