Calcul conditionnel avec VBA Excel

**NEC14** · 06/10/2017, 11h11

Bonjour le Forum,

Je tente d'écrire une formule conditionnelle Excel avec VBA mais je n'y arrive pas, pourriez-vous m'apporter votre aide s'il vous plait ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
=SI(Q34=0;0;SI(Q34<15000;0;SI(OU(Q34=15000;Q34<49999,99);G8;SI(OU(Q34>49999,99;Q34<99999,99);G11;SI(Q34>100000;G14;"")))))

J'ai essayé avec l'enregistreur de macros et je ne m'y retrouve pas

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
        "=IF(R[-6]C[1]=0,0,IF(R[-6]C[1]<15000,0,IF(OR(R[-6]C[1]=15000,R[-6]C[1]<49999.99),R[-32]C[-9],IF(OR(R[-6]C[1]>49999.99,R[-6]C[1]<99999.99),R[-29]C[-9],IF(R[-6]C[1]>100000,R[-26]C[-9],"""")))))"

Je vous en remercie par avance
René

**Jean-Philippe André** · 06/10/2017, 11h24

Salut,

si on prend la syntaxe la plus proche de ta formule d'origine
=SI(Q34=0;0;SI(Q34<15000;0;SI(OU(Q34=15000;Q34<49999,99);G8;SI(OU(Q34>49999,99;Q34<99999,99);G11;SI(Q34>100000;G14;"")))))

on obtient

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Range("P40").Formula= "=IF(Q34=0,0,IF(Q34<15000,0,IF(OR(Q34=15000,Q34<49999.99),G8,IF(OR(Q34>49999.99,Q34<99999.99),G11,IF(Q34>100000,G14,"""")))))"

**MolikDLuffy** · 06/10/2017, 11h38

Bonjour René,

Dans un premier temps, vous n'avez pas besoin de mettre des OU dans votre formule conditionnelle

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SI(Q5=0;0;SI(Q5<15000;0;SI(Q5<50000;G8;SI(Q5<100000;G11;G14;""))))

Je vais maintenant voir au niveau du VBA

A suivre

**MolikDLuffy** · 06/10/2017, 11h49

Voilà la formule VBA ci-dessous et l'exemple Excel ci-joint

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
Sub conditionnel()
If Range("Q5") = 0 Then
Range("C5") = 0
Else
If Range("Q5") < 15000 Then
Range("C5") = 0
Else
If Range("Q5") < 50000 Then
Range("C5") = Range("G8")
Else
If Range("Q5") < 100000 Then
Range("C5") = Range("G11")
Else
Range("C5") = Range("G14")
End If
End If
End If
End If
 
End Sub

Juste une Question, je suppose que vous avez plusieurs lignes qui doivent avoir la même formule. Pourriez-vous joindre le fichier afin que je puisse faire un meilleur code car si c'est le cas, nous devons utiliser des boucles.

**NEC14** · 06/10/2017, 12h08

Merci beaucoup pour votre réponse, le calcul se fait correctement après une petite rectification dans l'emplacement des conditions (c'est de ma faute).
Cependant, la formule s'affiche dans la barre de formule et je ne souhaite pas. Il faudrait que le résultat s'affiche directement dans la cellule comme je l'ai fait pour ces autres formules :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("P24").Value = Range("G6").Value
Range("P33").Value = Range("G21").Value
Range("P44").Value = Range("G22").Value
 
Range("Q23").Value = Application.Sum(Range("K25:K97"))
Range("Q24").Value = Application.Sum(Range("Q23").Value * (Range("G6").Value))
Range("Q25").Value = Application.Sum(Range("Q23").Value - (Range("Q24").Value))
Range("Q28").Value = Application.Sum(Range("K17").Value * (Range("N28").Value)) + Application.Sum(Range("L17").Value * (Range("P28").Value))
Range("Q29").Value = Application.Sum(Range("K18").Value * (Range("N29").Value)) + Application.Sum(Range("L18").Value * (Range("P29").Value))
Range("Q30").Value = Application.Sum(Range("K19").Value * (Range("N30").Value)) + Application.Sum(Range("L19").Value * (Range("P30").Value))
Range("Q32").Value = Application.Sum(Range("Q28:Q30"))
Range("Q33").Value = Application.Sum(Range("Q32").Value * (Range("G21").Value))
Range("Q34").Value = Application.Sum(Range("Q32").Value + (Range("Q33").Value))
Range("Q38").Value = Application.Sum(Range("Q36").Value + (Range("Q34").Value) + (Range("Q25").Value))
 
'Range("P40").Formula = "=IF(Q38=0,0,IF(Q38<15000,0,IF(Q38>99999.99,G14,IF(OR(Q38=15000,Q38<49999.99),G8,IF(OR(Q38>49999.99,Q38<99999.99),G11,"""")))))"
 
Range("Q40").Value = Application.Sum(Range("Q38").Value * (Range("P40").Value))
Range("Q42").Value = Application.Sum(Range("Q38").Value - (Range("Q40").Value))
Range("Q44").Value = Application.Sum(Range("Q42").Value * (Range("G22").Value))
Range("Q46").Value = Application.Sum(Range("Q42").Value + (Range("Q44").Value))
End Sub

Merci encore pour votre aide

OUPS! je n'avais pas vu la dernière réponse, toutes mes excuses

**NEC14** · 06/10/2017, 12h14

Le code proposé par MolikDLuffy fonctionne très bien, merci beaucoup à tous les deux.

**MolikDLuffy** · 06/10/2017, 12h16

RENE14 : Le code proposé par MolikDLuffy fonctionne très bien, merci beaucoup à tous les deux.

C'est parfait

N'oubliez pas cliquer sur "résoudre"

**NEC14** · 06/10/2017, 12h23

Juste une dernière question, est-ce qu'il est possible de simplifier toute ma procédure ?
Merci par avance

**MolikDLuffy** · 06/10/2017, 12h28

Envoyé par RENE14

Juste une dernière question, est-ce qu'il est possible de simplifier toute ma procédure ?
Merci par avance

Je suis désolé RENE mais il y a trop de cellule... Il me faudrait le fichier ou des imprimes écrans pour mieux comprendre...

Pour ma part, j'opte pour le fichier :p

**NEC14** · 06/10/2017, 13h05

Voici un modèle du fichier en pièce jointe

Pièce jointe 317370

Merci beaucoup

**MolikDLuffy** · 06/10/2017, 13h21

Envoyé par RENE14

Voici un modèle du fichier en pièce jointe

Merci beaucoup

Je ne comprends pas pourquoi vous voulez des macros. Si j'ai bien compris, une feuille correspond à un Lot.

Il suffit juste d'automatiser le tableau (qui est déjà fait) et les montants de la colonne Q.

**MolikDLuffy** · 06/10/2017, 13h44

Pour rester dans votre idée :
Voici de façon plus simple votre code VBA

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
Private Sub Worksheet_Activate()
Range("P24") = Range("G6").Value
Range("P33") = Range("G21").Value
Range("P44") = Range("G22").Value
 
Range("Q23") = [Sum(K25:K97)]
Range("Q24") = Range("Q23") * Range("G6")
Range("Q25") = Range("Q23") - Range("Q24")
Range("Q28") = Range("K17") * Range("N28") + Range("L17") * Range("P28")
Range("Q29") = Range("K18") * Range("N29") + Range("L18") * Range("P29")
Range("Q30") = Range("K19") * Range("N30") + Range("L19") * Range("P30")
Range("Q32") = [Sum(Q28:Q30)]
Range("Q33") = Range("Q32") * Range("G21")
Range("Q34") = Range("Q32") + Range("Q33")
Range("Q38") = Range("Q36") + Range("Q34") + Range("Q25")
 
If Range("Q38") = 0 Then
Range("P40") = 0
Else
If Range("Q38") < 15000 Then
Range("P40") = 0
Else
If Range("Q38") < 50000 Then
Range("P40") = Range("G8")
Else
If Range("Q38") < 100000 Then
Range("P40") = Range("G11")
Else
Range("P40") = Range("G14")
End If
End If
End If
End If
 
Range("Q40") = Range("Q38") * Range("P40")
Range("Q42") = Range("Q38") - Range("Q40")
Range("Q44") = Range("Q42") * Range("G22")
Range("Q46") = Range("Q42") + Range("Q44")
End Sub

**NEC14** · 06/10/2017, 14h28

Merci beaucoup pour votre aide.
Je passe par les macros car je ne peux pas protéger le document par un mot de passe (non autorisé par mon boss), alors pour ne pas prendre le risque de voir les formules de calculs être effacées, je passe par VBA.
Il y a 9 lots identiques à celui-ci, je vais pouvoir répéter les formules dans chaque feuille. J'aurai pu passer par un module, mais les tableaux ne sont pas tous identiques.

Encore mille mercis pour votre coopération.

J'ai encore ces dernières formules à trouver avec VBA
celle-ci des cellules G25 à G97

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
=SI(F25="";"";RECHERCHEV(F25;$A$4:$D$178;2))

et cette autre des cellules K25 à K97

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
=SI(F25="";"";(G25*H25)+(I25*J25))

**MolikDLuffy** · 06/10/2017, 15h06

Voici le code final avec intégration du code Application.WorksheetFunction.Vlookup

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
Private Sub Worksheet_Activate()
For ligne = 25 To 97 'correspond au tableau qui va de la ligne 25 à la ligne 97
If Cells(ligne, 6) <> "" Then
Cells(ligne, 7) = Application.WorksheetFunction.VLookup(Cells(ligne, 6).Value, Range("A1:D178"), 2, 0)
Cells(ligne, 9) = Application.WorksheetFunction.VLookup(Cells(ligne, 6).Value, Range("A1:D178"), 3, 0)
End If
Next ligne
 
Range("P24") = Range("G6").Value
Range("P33") = Range("G21").Value
Range("P44") = Range("G22").Value
 
Range("Q23") = [Sum(K25:K97)]
Range("Q24") = Range("Q23") * Range("G6")
Range("Q25") = Range("Q23") - Range("Q24")
Range("Q28") = Range("K17") * Range("N28") + Range("L17") * Range("P28")
Range("Q29") = Range("K18") * Range("N29") + Range("L18") * Range("P29")
Range("Q30") = Range("K19") * Range("N30") + Range("L19") * Range("P30")
Range("Q32") = [Sum(Q28:Q30)]
Range("Q33") = Range("Q32") * Range("G21")
Range("Q34") = Range("Q32") + Range("Q33")
Range("Q38") = Range("Q36") + Range("Q34") + Range("Q25")
 
If Range("Q38") = 0 Then
Range("P40") = 0
Else
If Range("Q38") < 15000 Then
Range("P40") = 0
Else
If Range("Q38") < 50000 Then
Range("P40") = Range("G8")
Else
If Range("Q38") < 100000 Then
Range("P40") = Range("G11")
Else
Range("P40") = Range("G14")
End If
End If
End If
End If
 
Range("Q40") = Range("Q38") * Range("P40")
Range("Q42") = Range("Q38") - Range("Q40")
Range("Q44") = Range("Q42") * Range("G22")
Range("Q46") = Range("Q42") + Range("Q44")
 
 
End Sub

**NEC14** · 06/10/2017, 15h22

Merci beaucoup, le code fonctionne plutôt bien. Cependant, lorsque j'efface les données dans la liste de choix, les résultats de la recherche reste dans le tableau, ils ne sont pas effacés automatiquement.
Reste encore cette formule à glisser dans les cellules de K25 àK97.
Je cherche de mon côté mais vous êtes plus rapide que moi, si je trouve la solution, je vous avertis

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
=SI(F25="";"";(G25*H25)+(I25*J25))

J'ai tenté de faire ça, mais cela me répète le même résultat, même quand la colonne nombre est vide

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 
Dim MCell As Range
    For Each MCell In Range("K25:K97")
    MCell = Range("G25") * Range("H25") + Range("I25") * Range("J25")
    Next

**MolikDLuffy** · 06/10/2017, 15h50

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
Private Sub Worksheet_Activate()
For ligne = 25 To 97
If Cells(ligne, 6) <> "" Then
Cells(ligne, 7) = Application.WorksheetFunction.VLookup(Cells(ligne, 6).Value, Range("A1:D178"), 2, 0)
Cells(ligne, 9) = Application.WorksheetFunction.VLookup(Cells(ligne, 6).Value, Range("A1:D178"), 3, 0)
Cells(ligne, 11) = Cells(ligne, 7) * Cells(ligne, 8) + Cells(ligne, 9) * Cells(ligne, 10)
End If
Next ligne
 
Range("P24") = Range("G6").Value
Range("P33") = Range("G21").Value
Range("P44") = Range("G22").Value
 
Range("Q23") = [Sum(K25:K97)]
Range("Q24") = Range("Q23") * Range("G6")
Range("Q25") = Range("Q23") - Range("Q24")
Range("Q28") = Range("K17") * Range("N28") + Range("L17") * Range("P28")
Range("Q29") = Range("K18") * Range("N29") + Range("L18") * Range("P29")
Range("Q30") = Range("K19") * Range("N30") + Range("L19") * Range("P30")
Range("Q32") = [Sum(Q28:Q30)]
Range("Q33") = Range("Q32") * Range("G21")
Range("Q34") = Range("Q32") + Range("Q33")
Range("Q38") = Range("Q36") + Range("Q34") + Range("Q25")
 
If Range("Q38") = 0 Then
Range("P40") = 0
Else
If Range("Q38") < 15000 Then
Range("P40") = 0
Else
If Range("Q38") < 50000 Then
Range("P40") = Range("G8")
Else
If Range("Q38") < 100000 Then
Range("P40") = Range("G11")
Else
Range("P40") = Range("G14")
End If
End If
End If
End If
 
Range("Q40") = Range("Q38") * Range("P40")
Range("Q42") = Range("Q38") - Range("Q40")
Range("Q44") = Range("Q42") * Range("G22")
Range("Q46") = Range("Q42") + Range("Q44")
 
 
End Sub

Pour effacer les données, je vous conseille de cliquer sur un bouton qui intègre le code suivant :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 
Sub effacer()
Sheets("Feuil1").Select
Range("A1:D178").ClearContents
End Sub

**NEC14** · 06/10/2017, 16h05

Merci pour votre solution, mais j'ai préféré écrire ce code pour effacer les cellules, est-ce correct ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
 
If Cells(ligne, 6) = "" Then
Cells(ligne, 7).ClearContents
Cells(ligne, 8).ClearContents
Cells(ligne, 9).ClearContents
Cells(ligne, 10).ClearContents
End If

**MolikDLuffy** · 06/10/2017, 16h09

Envoyé par RENE14

Merci pour votre solution, mais j'ai préféré écrire ce code pour effacer les cellules, est-ce correct ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
 
If Cells(ligne, 6) = "" Then
Cells(ligne, 7).ClearContents
Cells(ligne, 8).ClearContents
Cells(ligne, 9).ClearContents
Cells(ligne, 10).ClearContents
End If

je pense à tester

bien sur il faut intégrer un For ligne = ... to ....

**NEC14** · 06/10/2017, 18h52

Juste une dernière réflexion avant de clôturer ce post, est-ce qu'il ne serait pas plus simple de créer un formulaire de saisie qui alimenterait le tableau F25:K97 au lieu de me compliquer avec une tartinée de formule ?

Sinon merci beaucoup pour ta précieuse aide.

Cordiales salutations
René

**MolikDLuffy** · 06/10/2017, 19h17

Envoyé par RENE14

Juste une dernière réflexion avant de clôturer ce post, est-ce qu'il ne serait pas plus simple de créer un formulaire de saisie qui alimenterait le tableau F25:K97 au lieu de me compliquer avec une tartinée de formule ?

Sinon merci beaucoup pour ta précieuse aide.

Cordiales salutations
René

Toute programmation est amenée à tartiner de formule

mais tant que ça marche ça va

Avec un formulaire ça peut-être styler visuellement mais pas plus rapide que si l'on fait ça manuellement (Selon mon avis bien entendu).

Tu es libre de tester un formulaire mais il y aura besoin également de formule.

Calcul conditionnel avec VBA Excel [XL-2010]

Macros et VBA Excel

Discussions similaires

Partager

Partager