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
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
| For i = 1 To NombreLignes - 1
If ActiveCell.Offset(i, 0).Value = "Base" Then
Puissance = ActiveCell.Offset(i, 14).Value
Fixe = Application.WorksheetFunction.VLookup(Puissance, PlageST, 5, False)
PF_CU_bareme = Application.WorksheetFunction.VLookup(Puissance, PlageST, 6, False)
PF_MU_bareme = Application.WorksheetFunction.VLookup(Puissance, PlageST, 8, False)
PF_LU_bareme = Application.WorksheetFunction.VLookup(Puissance, PlageST, 10, False)
Conso_CU_bareme = Application.WorksheetFunction.VLookup(Puissance, PlageST, 7, False)
Conso_MU_bareme = Application.WorksheetFunction.VLookup(Puissance, PlageST, 9, False)
Conso_LU_bareme = Application.WorksheetFunction.VLookup(Puissance, PlageST, 11, False)
PF_CU = Fixe + (PF_CU_bareme * Puissance)
Conso_CU = (Conso_CU_bareme * ActiveCell.Offset(i, 7).Value) / 100
TURPE2_CU = PF_CU + Conso_CU
MOYENNE_CU = TURPE2_CU / (ActiveCell.Offset(i, 10).Value / 1000)
CTA_CU = 0.21 * PF_CU
PF_MU = Fixe + (PF_MU_bareme * Puissance)
Conso_MU = (Conso_MU_bareme * ActiveCell.Offset(i, 7).Value) / 100
TURPE2_MU = PF_MU + Conso_MU
MOYENNE_MU = TURPE2_MU / (ActiveCell.Offset(i, 10).Value / 1000)
CTA_MU = 0.21 * PF_MU
PF_LU = Fixe + (PF_LU_bareme * Puissance)
Conso_LU = (Conso_LU_bareme * ActiveCell.Offset(i, 7).Value) / 100
TURPE2_LU = PF_LU + Conso_LU
MOYENNE_LU = TURPE2_LU / (ActiveCell.Offset(i, 10).Value / 1000)
CTA_LU = 0.21 * PF_LU
TURPE2_OPTIMISE_BASE = Application.WorksheetFunction.Min(TURPE2_CU + CTA_CU, TURPE2_MU + CTA_MU, TURPE2_LU + CTA_LU)
If TURPE2_OPTIMISE_BASE = TURPE2_CU + CTA_CU Then OPTIMISE = "CU"
If TURPE2_OPTIMISE_BASE = TURPE2_MU + CTA_MU Then OPTIMISE = "MU"
If TURPE2_OPTIMISE_BASE = TURPE2_LU + CTA_LU Then OPTIMISE = "LU"
If OPTIMISE = "CU" Then
PF_OPT = PF_CU
Conso_OPT = Conso_CU
TURPE2_OPT = TURPE2_CU
MOYENNE_OPT = MOYENNE_CU
CTA_OPT = CTA_CU
End If
If OPTIMISE = "MU" Then
PF_OPT = PF_MU
Conso_OPT = Conso_MU
TURPE2_OPT = TURPE2_MU
MOYENNE_OPT = MOYENNE_MU
CTA_OPT = CTA_MU
End If
If OPTIMISE = "LU" Then
PF_OPT = PF_LU
Conso_OPT = Conso_LU
TURPE2_OPT = TURPE2_LU
MOYENNE_OPT = MOYENNE_LU
CTA_OPT = CTA_LU
End If
' CU
ActiveCell.Offset(i, 16).Value = Round(PF_CU, 2)
ActiveCell.Offset(i, 17).Value = Round(Conso_CU, 2)
ActiveCell.Offset(i, 18).Value = Round(TURPE2_CU, 2)
ActiveCell.Offset(i, 19).Value = Round(MOYENNE_CU, 2)
ActiveCell.Offset(i, 20).Value = Round(CTA_CU, 2)
' MU
ActiveCell.Offset(i, 22).Value = Round(PF_MU, 2)
ActiveCell.Offset(i, 23).Value = Round(Conso_MU, 2)
ActiveCell.Offset(i, 24).Value = Round(TURPE2_MU, 2)
ActiveCell.Offset(i, 25).Value = Round(MOYENNE_MU, 2)
ActiveCell.Offset(i, 26).Value = Round(CTA_MU, 2)
' LU
ActiveCell.Offset(i, 28).Value = Round(PF_LU, 2)
ActiveCell.Offset(i, 29).Value = Round(Conso_LU, 2)
ActiveCell.Offset(i, 30).Value = Round(TURPE2_LU, 2)
ActiveCell.Offset(i, 31).Value = Round(MOYENNE_LU, 2)
ActiveCell.Offset(i, 32).Value = Round(CTA_LU, 2)
' minimum TURPE2 BASE optimisé
ActiveCell.Offset(i, 40).Value = OPTIMISE
ActiveCell.Offset(i, 41).Value = Round(PF_OPT, 2)
ActiveCell.Offset(i, 42).Value = Round(Conso_OPT, 2)
ActiveCell.Offset(i, 43).Value = Round(TURPE2_OPT, 2)
ActiveCell.Offset(i, 44).Value = Round(MOYENNE_OPT, 2)
' CTA BASE optimisée
ActiveCell.Offset(i, 46).Value = Round(CTA_OPT, 2)
ActiveCell.Offset(i, 47).Value = Round(CTA_OPT / (ActiveCell.Offset(i, 10).Value / 1000), 2)
End If
If ActiveCell.Offset(i, 0).Value = "Double" Then
Puissance = ActiveCell.Offset(i, 14).Value
Fixe = Application.WorksheetFunction.VLookup(Puissance, PlageDT, 5, False)
PF_MUDT_bareme = Application.WorksheetFunction.VLookup(Puissance, PlageDT, 6, False)
ConsoHP_MUDT_bareme = Application.WorksheetFunction.VLookup(Puissance, PlageDT, 7, False)
ConsoHC_MUDT_bareme = Application.WorksheetFunction.VLookup(Puissance, PlageDT, 8, False)
PF_MUDT = Fixe + (PF_MUDT_bareme * Puissance)
ConsoHP_MUDT = (ConsoHP_MUDT_bareme * ActiveCell.Offset(i, 7).Value) / 100
ConsoHC_MUDT = (ConsoHC_MUDT_bareme * ActiveCell.Offset(i, 8).Value) / 100
TURPE2_MUDT = PF_MUDT + ConsoHP_MUDT + ConsoHC_MUDT
MOYENNE_MUDT = TURPE2_MUDT / (ActiveCell.Offset(i, 10).Value / 1000)
CTA_MUDT = 0.21 * PF_MUDT
' MU DT
ActiveCell.Offset(i, 34).Value = Round(PF_MUDT, 2)
ActiveCell.Offset(i, 35).Value = Round(ConsoHP_MUDT + ConsoHC_MUDT, 2)
ActiveCell.Offset(i, 36).Value = Round(TURPE2_MUDT, 2)
ActiveCell.Offset(i, 37).Value = Round(MOYENNE_MUDT, 2)
ActiveCell.Offset(i, 38).Value = Round(CTA_MUDT, 2)
' TURPE2 DOUBLE (optimisé)
ActiveCell.Offset(i, 40).Value = "MU DT"
ActiveCell.Offset(i, 41).Value = Round(PF_MUDT, 2)
ActiveCell.Offset(i, 42).Value = Round(ConsoHP_MUDT + ConsoHC_MUDT, 2)
ActiveCell.Offset(i, 43).Value = Round(TURPE2_MUDT, 2)
ActiveCell.Offset(i, 44).Value = Round(MOYENNE_MUDT, 2)
' CTA DOUBLE
ActiveCell.Offset(i, 46).Value = Round(CTA_MUDT, 2)
ActiveCell.Offset(i, 47).Value = Round(CTA_MUDT / (ActiveCell.Offset(i, 10).Value / 1000), 2)
End If
Next i |
