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
| Sub MaJ()
Dim CAHIER As Workbook
Dim source As Worksheet
Dim cible As Worksheet
Dim ligne As Integer
Dim Val1 As Variant
Dim Val2 As Variant
Dim Val3 As Variant
Dim Val4 As Variant
Set CAHIER = ActiveWorkbook
Set cible = CAHIER.Worksheets("Capitalisation")
Set source = CAHIER.Worksheets("Temps calcul")
Worksheets("Capitalisation").Activate
For ligne = 2 To 10000
If Cells(ligne, 2).Value <> "" Then
GoTo suite
Else
Rows(ligne & ":" & ligne).Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
cible.Cells(ligne, 2).Value = source.Cells(13, 2).Value
cible.Cells(ligne, 3).Value = source.Cells(8, 6).Value
cible.Cells(ligne, 4).Value = 1
cible.Cells(ligne, 5).Value = source.Cells(11, 4).Value
cible.Cells(ligne, 6).Value = source.Cells(11, 5).Value
cible.Cells(ligne, 7).Value = source.Cells(11, 6).Value
cible.Cells(ligne, 8).Value = source.Cells(11, 7).Value
cible.Cells(ligne, 9).FormulaR1C1 = "=RC[-6]*RC[-5]*R[3]C[-6]*(RC[-4]+RC[-3]+RC[-2]+RC[-1])"
cible.Cells(ligne + 1, 9).Formula = "=SUM(I2:I" & ligne & ")"
Val1 = cible.Cells(ligne + 6, 5).Formula
cible.Cells(ligne + 6, 5).Formula = "=R[-3]C[-2]*R[-6]C[-2]*R[-6]C[-1]*R[-6]C + & Val1 &"
'Val2 = cible.Cells(ligne + 6, 6).Formula
'cible.Cells(ligne + 6, 6).Formula = "=(" & Val2 & "+ R[-3]C[-3]*R[-6]C[-3]*R[-6]C[-2]*R[-6]C)"
'Val3 = cible.Cells(ligne + 6, 7).Formula
'cible.Cells(ligne + 6, 7).Formula = "=(" & Val3 & "+ R[-3]C[-4]*R[-6]C[-4]*R[-6]C[-3]*R[-6]C)"
'Val4 = cible.Cells(ligne + 6, 8).Formula
'cible.Cells(ligne + 6, 8).Formula = "=(" & Val4 & "+ R[-3]C[-5]*R[-6]C[-5]*R[-6]C[-4]*R[-6]C)"
Exit For
End If
suite:
Next
End Sub |
Partager