Code optimization for financial analysis

[riga04af]

Good morning,

I created the following code to make calculations and then copy the results in the "Cash Flow" sheet. It takes 15 seconds but I'm sure that the code can be optimized. In step 1) formulas are applied to a range of cells in "PTF" sheet; step 2) change the date and the formula are recalculated; step 3) results of the formula calculated in "PTF" sheet are copied in "Cash Flow" sheet. The formula (not calculated in the code) are:

sheets "PTF" cell "K4" : =COUNTIFS($L$7:$L$3312,">"&0)
sheets "PTF" cell "L4" : =SUM($L$7:$L$3312)
sheets "PTF" cell "M4" : =IF($L$4=0,0,SUMPRODUCT($L$7:$L$3312,M7:M3312)/$L$4)
sheets "PTF" cell "N4" : =IF($L$4=0,0,SUMPRODUCT($L$7:$L$3312,N7:N3312)/$L$4)
sheets "PTF" cell "O4" : =IF($L$4=0,0,SUMPRODUCT($L$7:$L$3312,O7:O3312)/$L$4)sheets "PTF" cell "P4" :
sheets "PTF" cell "Q4" : =IF($L$4=0,0,SUMPRODUCT($L$7:$L$3312,P7:P3312)/$L$4)
sheets "PTF" cell "G4" : =IF($L$4=0,0,SUMPRODUCT($L$7:$L$3312,G7:G3312)/$L$4)

My best solution would be have all the calculations in VBA and just copy the results in "Cash Flow" sheet... but I have no idea how to do it....

Many thanks in advance for your suggestions and idea!!!!!

----------------------------------------------------
Sub ParamRisk()

Application.ScreenUpdating = False
Application.Calculation = xlCalculateManual
Application.EnableEvents = False

Dim i As Integer
Dim c As Integer
Dim d As Integer

c = Application.WorksheetFunction.CountA(Worksheets("PTF").Range("B7:B10000"))
d = Application.WorksheetFunction.CountA(Worksheets("Cash Flow").Range("D7:D100"))

'step 1) complex formula are applied to the range in "PTF" sheet

With Sheets("PTF")
.Range("L7:L" & c + 6).Formula = "=SUMIFS($R7:$CH7,$R$6:$CH$6,"">""&$J$4)"
.Range("M7:M" & c + 6).Formula = "=IF(L7=0,0,YEARFRAC($J$4,SUMPRODUCT(--($R$6:$CH$6>$J$4),$R$6:$CH$6,$R7:$CH7)/SUMIFS($R7:$CH7,$R$6:$CH$6,"">""&$J$4),1))"
.Range("N7:N" & c + 6).Formula = "=IF(L7=0,0,IF($J$4=$R$6,F7,BILINTERP(Parametri!$B$6:$W$26,INDEX(Parametri!$C$6:$W$6,MATCH(E7,Parametri!$C$5:$W$5,0)),$R$4)))"
.Range("O7:O" & c + 6).Formula = "=IF($J$4=$R$6,H7,G7*N7)"
.Range("P7:P" & c + 6).Formula = "=IF(L7=0,0,IF($J$4=$R$6,I7,capreq(MAX(0.03%,N7),G7,MAX(1,MIN(5,M7)),IF(C7=""SC"",1,0),D7)*12.5*K7))"
.Range("Q7:Q" & c + 6).Formula = "=IF($J$4=$R$6,J7,P7*8%+O7)"
End With

'step 2) it is applied the change of date, all the formula above are recalculated

For i = 1 To d

Worksheets("PTF").Range("J4").Value = Worksheets("Cash Flow").Range("D" & i + 6)

Worksheets("PTF").Range("G:R").Calculate

'step 3) results of the formula calculated in "PTF" sheet are copied in "Cash Flow" sheet

Worksheets("Cash Flow").Range("E" & i + 6) = Worksheets("PTF").Range("K4").Value
Worksheets("Cash Flow").Range("F" & i + 6) = Worksheets("PTF").Range("L4").Value
Worksheets("Cash Flow").Range("G" & i + 6) = Worksheets("PTF").Range("M4").Value
Worksheets("Cash Flow").Range("H" & i + 6) = Worksheets("PTF").Range("N4").Value
Worksheets("Cash Flow").Range("I" & i + 6) = Worksheets("PTF").Range("G4").Value
Worksheets("Cash Flow").Range("J" & i + 6) = Worksheets("PTF").Range("O4").Value
Worksheets("Cash Flow").Range("K" & i + 6) = Worksheets("PTF").Range("P4").Value
Worksheets("Cash Flow").Range("L" & i + 6) = Worksheets("PTF").Range("Q4").Value

Next i

With Worksheets("PTF").Range("L7:Q" & c + 6)
.Value = .Value
End With

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True

Worksheets("Summary").Select

End Sub