Sub A2_FBaseB_AgeBAT_A()
'*****************
'On définit les formules de calcul
F_SUNsurSUB = "=IF(RC13>2000,RC16/RC15,"""")"
F_AgeConst = "=IF(AND(RC17=srn,RC18=app),IF(RC10="""",0,IF(RC12="""",R7C2-RC10,0)),0)"
F_AgeReha = "=IF(AND(RC17=srn,RC18=app),IF(RC10="""",0,IF(RC12="""",0,R7C2-YEAR(RC12))),0)"
F_Reha5 = "=IF(AND(RC17=srn,RC18=app),IF((RC12=""""),0,IF(R7C2-YEAR(RC12)>5,0,RC13)),0)"
F_Const5 = "=IF(AND(RC17=srn,RC18=app),IF(ISNUMBER(RC10),IF(R7C2-RC10<=5,RC13-IF(ISNUMBER(RC35),RC[-1],0),0),0),0)"
F_moins5 = "=IF(AND(RC17=srn,RC18=app),(IF((RC12=""""),0,IF(R7C2-YEAR(RC12)>5,0,RC13)))+(IF(ISNUMBER(RC10),IF(R7C2-RC10<=5,RC13-IF(ISNUMBER(RC35),RC[-2],0),0),0)),0)"
F_5a10 = "=IF(AND(RC17=srn,RC18=app),IF(ISNUMBER(RC10),IF(AND(R7C2-RC10>5,R7C2-RC10<=10),RC13-IF(ISNUMBER(RC35),RC[-3],0),0),0),0)"
F_Plus10 = "=IF(AND(RC17=srn,RC18=app),IF(ISNUMBER(RC10),IF(R7C2-RC10>10,RC13-IF(ISNUMBER(RC35),RC[-4],0),0),0),0)"
F_SurfP = "=0.5*RC38+RC39"
F_SousTot = "=SUBTOTAL(9,R[2]C:R[64984]C)"
'*****************
Application.ScreenUpdating = False
NOM_Colonnes = Array("N/B", "Age", "AR", "R5", "Const5", "M5", "M510", "pl10", "M2P")
Formules = Array(F_SUNsurSUB, F_AgeConst, F_AgeReha, F_Reha5, F_Const5, F_moins5, F_5a10, F_Plus10, F_Plus10)
Formats = Array("0.00%", "#,##0", "#,##0", "#,##0", "#,##0", "#,##0", "#,##0", "#,##0", "#,##0")
With Sheets("BaseB")
'Effacement ligne NLignEntBaseB a partir de N/B
Set c = .Rows(NLignEntBaseB).Find("N/B", LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
Range(c.Address & ":" & Cells(NLignEntBaseB, Columns.Count).Address).ClearContents
End If
DerCol = .Cells(NLignEntBaseB, .Columns.Count).End(xlToLeft).Column + 1 'On détermine la derniere Colonne Vide de la ligne Entête
DerLgn = .Cells(NLignEntBaseB, 1).CurrentRegion.Rows.Count - 1 'On détermine le Nombre de ligne de la Plage Cible
For n = LBound(NOM_Colonnes) To UBound(NOM_Colonnes)
With .Cells(NLignEntBaseB, DerCol)
.Value = NOM_Colonnes(n)
With .Resize(DerLgn).Offset(1, 0)
.FormulaR1C1 = Formules(n)
.Value = .Value 'Permet d'effacer les Formules
.NumberFormat = Formats(n)
End With
End With
DerCol = DerCol + 1
Next
End With
End Sub