Calcul conditionnel avec VBA Excel

Version imprimable

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:

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:

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é

06/10/2017, 10h24
Jean-Philippe André

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:

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,"""")))))"
06/10/2017, 10h38
MolikDLuffy

Bonjour René,

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

Code:

=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

1 pièce(s) jointe(s)

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

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.

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:

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

06/10/2017, 11h14
NEC14

Le code proposé par MolikDLuffy fonctionne très bien, merci beaucoup à tous les deux.
06/10/2017, 11h16
MolikDLuffy

Citation:

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"
06/10/2017, 11h23
NEC14

Juste une dernière question, est-ce qu'il est possible de simplifier toute ma procédure ?
Merci par avance
06/10/2017, 11h28
MolikDLuffy

Citation:

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
06/10/2017, 12h05
NEC14

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

Pièce jointe 317370

Merci beaucoup
06/10/2017, 12h21
MolikDLuffy

Citation:

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.

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

Code:

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

06/10/2017, 13h28
NEC14
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:

1 2 =SI(F25="";"";RECHERCHEV(F25;$A$4:$D$178;2))
et cette autre des cellules K25 à K97
Code:

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

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

Code:

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

06/10/2017, 14h22
NEC14
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:

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:

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

Code:

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:

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

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

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

06/10/2017, 15h09
MolikDLuffy
Citation:
Envoyé par RENE14

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

Code:

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 ....
06/10/2017, 17h52
NEC14

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é
06/10/2017, 18h17
MolikDLuffy

Citation:

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 :lol: 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.
06/10/2017, 18h28
NEC14

Merci pour votre avis
Bon weekend:coucou: