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 |
Partager