Les conditons sur les Elseif ne fonctionnent pas

Bonjour,

J'ai écris un bout de code VBA pour faire varier différentes formules sur des cellules en fonction de deux conditions.

Le problème est que le code s'arrete à la première condition en appliquer la 1er formule alors que la condition n'est pas vrai. par exemple je fais varier la cellule (4,i) au-dela de 625 et la (9,i) au-dela de 12.5 mais cela applique seulement la 1er formule

voici le fichier

Voici le code

Sub recherche_EER()

For i = 3 To 14

If (0 < Cells(4, i) < 625 And 10 < Cells(9, i) < 12.5) Then

Cells(6, i) = 2.7806 * Cells(5, i) ^ 3 - 6.7794 * Cells(5, i) ^ 2 + 5.1953 * Cells(5, i) + 8.3336

ElseIf (0 <= Cells(4, i) <= 625 And 12.5 <= Cells(9, i) < 17.5) Then

Cells(6, i) = 25.655 * Cells(5, i) ^ 3 - 54.607 * Cells(5, i) ^ 2 + 32.129 * Cells(5, i) + 5.6732

ElseIf (0 <= Cells(4, i) <= 625 And 17.5 <= Cells(9, i) < 22.5) Then

Cells(6, i) = 13.266 * Cells(5, i) ^ 3 - 29.71 * Cells(5, i) ^ 2 + 18.753 * Cells(5, i) + 5.2773

ElseIf (0 <= Cells(4, i) <= 625 And 22.5 <= Cells(9, i) < 27.5) Then

Cells(6, i) = 14.899 * Cells(5, i) ^ 3 - 35.938 * Cells(5, i) ^ 2 + 25.169 * Cells(5, i) + 2.2998

ElseIf (0 <= Cells(4, i) <= 625 And 27.5 <= Cells(9, i) < 32.5) Then

Cells(6, i) = 13.594 * Cells(5, i) ^ 3 - 32.652 * Cells(5, i) ^ 2 + 22.979 * Cells(5, i) + 1.4894

ElseIf (0 <= Cells(4, i) <= 625 And 32.5 <= Cells(9, i) < 37.5) Then

Cells(6, i) = 11.709 * Cells(5, i) ^ 3 - 27.729 * Cells(5, i) ^ 2 + 19.658 * Cells(5, i) + 0.9244

ElseIf (0 <= Cells(4, i) <= 625 And 37.5 <= Cells(9, i) < 80) Then

Cells(6, i) = 10.497 * Cells(5, i) ^ 3 - 23.85 * Cells(5, i) ^ 2 + 16.406 * Cells(5, i) + 0.7575

' changement puissance

ElseIf (625 < Cells(4, i) <= 875 And 10 <= Cells(9, i) < 12.5) Then

Cells(6, i) = -65.831 * Cells(5, i) ^ 3 - 106.05 * Cells(5, i) ^ 2 - 45.286 * Cells(5, i) + 13.072

ElseIf (625 < Cells(4, i) <= 875 And 12.5 <= Cells(9, i) < 17.5) Then

Cells(6, i) = 32.202 * Cells(5, i) ^ 3 - 64.679 * Cells(5, i) ^ 2 + 34.721 * Cells(5, i) + 5.2354

ElseIf (625 < Cells(4, i) <= 875 And 17.5 <= Cells(9, i) < 22.5) Then

Cells(6, i) = 15.101 * Cells(5, i) ^ 3 - 35.778 * Cells(5, i) ^ 2 + 24.974 * Cells(5, i) + 4.2545

ElseIf (625 < Cells(4, i) <= 875 And 22.5 <= Cells(9, i) < 27.5) Then

Cells(6, i) = 23.83 * Cells(5, i) ^ 3 - 51.182 * Cells(5, i) ^ 2 + 31.113 * Cells(5, i) + 2.0439

ElseIf (625 < Cells(4, i) <= 875 And 27.5 <= Cells(9, i) < 32.5) Then

Cells(6, i) = 16.002 * Cells(5, i) ^ 3 - 36.496 * Cells(5, i) ^ 2 + 23.918 * Cells(5, i) + 1.7096

ElseIf (625 < Cells(4, i) <= 875 And 32.5 <= Cells(9, i) < 37.5) Then

Cells(6, i) = 15.194 * Cells(5, i) ^ 3 - 34.525 * Cells(5, i) ^ 2 + 23.386 * Cells(5, i) + 0.3094

ElseIf (625 < Cells(4, i) <= 875 And 37.5 <= Cells(9, i) < 80) Then

Cells(6, i) = 13.805 * Cells(5, i) ^ 3 - 30.701 * Cells(5, i) ^ 2 + 20.704 * Cells(5, i) - 0.1802

' Changement puissance

ElseIf (875 < Cells(4, i) <= 1250 And 10 <= Cells(9, i) < 12.5) Then

Cells(6, i) = 4.7142 * Cells(5, i) ^ 3 - 9.7012 * Cells(5, i) ^ 2 + 5.5415 * Cells(5, i) + 8.0466

ElseIf (875 < Cells(4, i) <= 1250 And 12.5 <= Cells(9, i) < 17.5) Then

Cells(6, i) = 25.952 * Cells(5, i) ^ 3 - 59.214 * Cells(5, i) ^ 2 + 38.376 * Cells(5, i) + 2.7457

ElseIf (875 < Cells(4, i) <= 1250 And 17.5 <= Cells(9, i) < 22.5) Then

Cells(6, i) = 13.424 * Cells(5, i) ^ 3 - 29.65 * Cells(5, i) ^ 2 + 18.113 * Cells(5, i) + 5.6604

ElseIf (875 < Cells(4, i) <= 1250 And 22.5 <= Cells(9, i) < 27.5) Then

Cells(6, i) = 2.9966 * Cells(5, i) ^ 3 - 10.597 * Cells(5, i) ^ 2 + 8.1369 * Cells(5, i) + 5.8403

ElseIf (875 < Cells(4, i) <= 1250 And 27.5 <= Cells(9, i) < 32.5) Then

Cells(6, i) = 0.2862 * Cells(5, i) ^ 3 - 4.9524 * Cells(5, i) ^ 2 + 4.6733 * Cells(5, i) + 5.344

ElseIf (875 < Cells(4, i) <= 1250 And 32.5 <= Cells(9, i) < 37.5) Then

Cells(6, i) = -2.4495 * Cells(5, i) ^ 3 + 1.6136 * Cells(5, i) ^ 2 + 0.4298 * Cells(5, i) + 4.8933

ElseIf (875 < Cells(4, i) <= 1250 And 37.5 <= Cells(9, i) < 80) Then

Cells(6, i) = -3.0387 * Cells(5, i) ^ 3 + 3.7868 * Cells(5, i) ^ 2 - 1.2721 * Cells(5, i) + 4.2675

' Changement puissance

ElseIf 1250 < Cells(4, i) <= 1750 And 10 <= Cells(9, i) < 12.5 Then

Cells(6, i) = 4.7142 * Cells(5, i) ^ 3 - 9.7012 * Cells(5, i) ^ 2 + 5.5415 * Cells(5, i) + 8.0466

ElseIf 1250 < Cells(4, i) <= 1750 And 12.5 <= Cells(9, i) < 17.5 Then

Cells(6, i) = 25.952 * Cells(5, i) ^ 3 - 59.214 * Cells(5, i) ^ 2 + 38.376 * Cells(5, i) + 2.7457

ElseIf 1250 < Cells(4, i) <= 1750 And 17.5 <= Cells(9, i) < 22.5 Then

Cells(6, i) = 19.108 * Cells(5, i) ^ 3 - 41.165 * Cells(5, i) ^ 2 + 25.423 * Cells(5, i) + 3.7584

ElseIf 1250 < Cells(4, i) <= 1750 And 22.5 <= Cells(9, i) < 27.5 Then

Cells(6, i) = 7.2727 * Cells(5, i) ^ 3 - 19.563 * Cells(5, i) ^ 2 + 13.803 * Cells(5, i) + 4.3405

ElseIf 1250 < Cells(4, i) <= 1750 And 27.5 <= Cells(9, i) < 32.5 Then

Cells(6, i) = 3.6785 * Cells(5, i) ^ 3 - 12.551 * Cells(5, i) ^ 2 + 9.5159 * Cells(5, i) + 4.2417

ElseIf 1250 < Cells(4, i) <= 1750 And 32.5 <= Cells(9, i) < 37.5 Then

Cells(6, i) = 0.6481 * Cells(5, i) ^ 3 - 5.0996 * Cells(5, i) ^ 2 + 4.463 * Cells(5, i) + 4.0825

ElseIf 1250 < Cells(4, i) <= 1750 And 37.5 <= Cells(9, i) < 80 Then

Cells(6, i) = -1.4478 * Cells(5, i) ^ 3 - 0.0617 * Cells(5, i) ^ 2 + 1.2468 * Cells(5, i) + 3.6525

End If

Next

End Sub

PS : je suis débutant, peut être une solution plus rapide doit exister !

Bonjour,

VBA n'interprète pas correctement ce type d'expression :

a < b < c

Il faudrait faire un truc comme ça :

DefBool Y 'Cette ligne doit figurer en tête du module
Sub recherche_EER2()

For i = 3 To 14
a = Cells(4, i).Value
b = Cells(9, i).Value
c = Cells(5, i).Value
Y1 = a > 0 And a < 625 And b > 10 And b < 12.5
Y2 = a >= 0 And a <= 625 And b >= 12.5 And b < 17
Y3 = a >= 0 And a <= 625 And b >= 17.5 And b < 22.5
Y4 = a >= 0 And a <= 625 And b >= 22.5 And b < 27.5
Y5 = a >= 0 And a <= 625 And b >= 27.5 And b < 32.5
Y6 = a >= 0 And a <= 625 And b >= 32.5 And b < 37.5
Y7 = a >= 0 And a <= 625 And b >= 37.5 And b < 80
Y8 = a > 625 And a <= 875 And b >= 10 And b < 12.5
Y9 = a > 625 And a <= 875 And b >= 12.5 And b < 17.5
Y10 = a > 625 And a <= 875 And b >= 17.5 And b < 22.5
Y11 = a > 625 And a <= 875 And b >= 22.5 And b < 27.5
Y12 = a > 625 And a <= 875 And b >= 27.5 And b < 32.5
Y13 = a > 625 And a <= 875 And b >= 32.5 And b < 37.5
Y14 = a > 625 And a <= 875 And b >= 37.5 And b < 80
Y15 = a > 875 And a <= 1250 And b >= 10 And b < 12.5
Y16 = a > 875 And a <= 1250 And b >= 12.5 And b < 17.5
Y17 = a > 875 And a <= 1250 And b = 17.5 And b < 22.5
Y18 = a > 875 And a <= 1250 And b >= 22.5 And b < 27.5
Y19 = a > 875 And a < 1250 And b >= 27.5 And b < 32.5
Y20 = a > 875 And a < 1250 And b >= 32.5 And b < 37.5
Y21 = a > 875 And a < 1250 And b >= 37.5 And b < 80
Y22 = a > 1250 And a < 1750 And b >= 10 And b < 12.5
Y23 = a > 1250 And a < 1750 And b >= 12.5 And b < 17.5
Y24 = a > 1250 And a < 1750 And b >= 17.5 And b < 22.5
Y25 = a > 1250 And a < 1750 And b >= 22.5 And b < 27.5
Y26 = a > 1250 And a < 1750 And b >= 27.5 And b < 32.5
Y27 = a > 1250 And a < 1750 And b >= 32.5 And b < 37.5
Y28 = a > 1250 And a < 1750 And b >= 37.5 And b < 80

    If Y1 Then
        Cells(6, i) = 2.7806 * c ^ 3 - 6.7794 * c ^ 2 + 5.1953 * c + 8.3336
    ElseIf Y2 Then
        Cells(6, i) = 25.655 * c ^ 3 - 54.607 * c ^ 2 + 32.129 * c + 5.6732
    ElseIf Y3 Then
        Cells(6, i) = 13.266 * c ^ 3 - 29.71 * c ^ 2 + 18.753 * c + 5.2773
    ElseIf Y4 Then
        Cells(6, i) = 14.899 * c ^ 3 - 35.938 * c ^ 2 + 25.169 * c + 2.2998
    ElseIf Y5 Then
        Cells(6, i) = 13.594 * c ^ 3 - 32.652 * c ^ 2 + 22.979 * c + 1.4894
    ElseIf Y6 Then
        Cells(6, i) = 11.709 * c ^ 3 - 27.729 * c ^ 2 + 19.658 * c + 0.9244
    ElseIf Y7 Then
        Cells(6, i) = 10.497 * c ^ 3 - 23.85 * c ^ 2 + 16.406 * c + 0.7575
    ' changement puissance
    ElseIf Y8 Then
        Cells(6, i) = -65.831 * c ^ 3 - 106.05 * c ^ 2 - 45.286 * c + 13.072
    ElseIf Y9 Then
        Cells(6, i) = 32.202 * c ^ 3 - 64.679 * c ^ 2 + 34.721 * c + 5.2354
    ElseIf Y10 Then
        Cells(6, i) = 15.101 * c ^ 3 - 35.778 * c ^ 2 + 24.974 * c + 4.2545
    ElseIf Y11 Then
        Cells(6, i) = 23.83 * c ^ 3 - 51.182 * c ^ 2 + 31.113 * c + 2.0439
    ElseIf Y12 Then
        Cells(6, i) = 16.002 * c ^ 3 - 36.496 * c ^ 2 + 23.918 * c + 1.7096
    ElseIf Y13 Then
        Cells(6, i) = 15.194 * c ^ 3 - 34.525 * c ^ 2 + 23.386 * c + 0.3094
    ElseIf Y14 Then
        Cells(6, i) = 13.805 * c ^ 3 - 30.701 * c ^ 2 + 20.704 * c - 0.1802
    ' Changement puissance
    ElseIf Y15 Then
        Cells(6, i) = 4.7142 * c ^ 3 - 9.7012 * c ^ 2 + 5.5415 * c + 8.0466
    ElseIf Y16 Then
        Cells(6, i) = 25.952 * c ^ 3 - 59.214 * c ^ 2 + 38.376 * c + 2.7457
    ElseIf Y17 Then
        Cells(6, i) = 13.424 * c ^ 3 - 29.65 * c ^ 2 + 18.113 * c + 5.6604
    ElseIf Y18 Then
        Cells(6, i) = 2.9966 * c ^ 3 - 10.597 * c ^ 2 + 8.1369 * c + 5.8403
    ElseIf Y19 Then
        Cells(6, i) = 0.2862 * c ^ 3 - 4.9524 * c ^ 2 + 4.6733 * c + 5.344
    ElseIf Y20 Then
        Cells(6, i) = -2.4495 * c ^ 3 + 1.6136 * c ^ 2 + 0.4298 * c + 4.8933
    ElseIf Y21 Then
        Cells(6, i) = -3.0387 * c ^ 3 + 3.7868 * c ^ 2 - 1.2721 * c + 4.2675
    ' Changement puissance
    ElseIf Y22 Then
        Cells(6, i) = 4.7142 * c ^ 3 - 9.7012 * c ^ 2 + 5.5415 * c + 8.0466
    ElseIf Y23 Then
        Cells(6, i) = 25.952 * c ^ 3 - 59.214 * c ^ 2 + 38.376 * c + 2.7457
    ElseIf Y24 Then
        Cells(6, i) = 19.108 * c ^ 3 - 41.165 * c ^ 2 + 25.423 * c + 3.7584
    ElseIf Y25 Then
        Cells(6, i) = 7.2727 * c ^ 3 - 19.563 * c ^ 2 + 13.803 * c + 4.3405
    ElseIf Y26 Then
        Cells(6, i) = 3.6785 * c ^ 3 - 12.551 * c ^ 2 + 9.5159 * c + 4.2417
    ElseIf Y27 Then
        Cells(6, i) = 0.6481 * c ^ 3 - 5.0996 * c ^ 2 + 4.463 * c + 4.0825
    ElseIf Y28 Then
        Cells(6, i) = -1.4478 * c ^ 3 - 0.0617 * c ^ 2 + 1.2468 * c + 3.6525
    Else
    Cells(6, i) = "erreur"
    End If
Next
End Sub

Si j'ai pas fait d'erreur dans les fourchettes....

A+

Super, merci pour avoir pris du temps pour me répondre.

Je vais tester le code que vous m'avez transmis !