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
| Dim réhab As Worksheet
Dim neuf As Worksheet
Dim projections As Worksheet
Dim calculs As Worksheet
Dim montantcalculs As Range
Dim profilsource As Range
Dim profilcalculs As Range
Dim datecalculs As Range
Dim annuitécalculs As Range
Dim annuitéprojections As Range
Sub Calcul_nouvelles_annuités()
Set réhab = Worksheets("Opérations réhab")
Set neuf = Worksheets("Opérations neuves")
Set calculs = Worksheets("Calcul annuités")
Set projections = Worksheets("Projection annuités")
Set profilcalculs = Worksheets("Calcul annuités").Range("B1")
Set datecalculs = Worksheets("Calcul annuités").Range("B2")
Set montantcalculs = Worksheets("Calcul annuités").Range("B3")
Set annuitécalculs = Worksheets("Calcul annuités").Range("F9")
Set profilsource = Worksheets("Opérations réhab").Range("O2")
Set annuitéprojections = Worksheets("Projection annuités").Range("C2")
profilsource = testprofilsource
Do While testprofilsource <> ""
Do While profilsource <> ""
profilcalculs.FormulaR1C1 = profilsource.Value
datecalculs.FormulaR1C1 = profilsource.Offset(0, 1).Value
montantcalculs.FormulaR1C1 = profilsource.Offset(0, 2).Value
With annuitéprojections
.Offset(1, 0).EntireRow.Insert Shift:=xlUp, CopyOrigin:=xlFormatFromLeftOrAbove
.FormulaR1C1 = annuitécalculs.Value
.Offset(0, 1).FormulaR1C1 = annuitécalculs.Offset(0, 1).Value
.Offset(0, 2).FormulaR1C1 = annuitécalculs.Offset(0, 2).Value
.Offset(0, 3).FormulaR1C1 = annuitécalculs.Offset(0, 3).Value
.Offset(0, 4).FormulaR1C1 = annuitécalculs.Offset(0, 4).Value
.Offset(0, 5).FormulaR1C1 = annuitécalculs.Offset(0, 5).Value
.Offset(0, 6).FormulaR1C1 = annuitécalculs.Offset(0, 6).Value
.Offset(0, 7).FormulaR1C1 = annuitécalculs.Offset(0, 7).Value
.Offset(0, 8).FormulaR1C1 = annuitécalculs.Offset(0, 8).Value
.Offset(0, 9).FormulaR1C1 = annuitécalculs.Offset(0, 9).Value
.Offset(0, 10).FormulaR1C1 = annuitécalculs.Offset(0, 10).Value
End With
Set profilsource = profilsource.Offset(0, 3)
Set annuitéprojections = annuitéprojections.Offset(1, 0)
Loop
Set testprofilsource = testprofilsource.Offset(1, 0)
Set testprofilsource = profilsource
Loop
End Sub[/U] |
Partager