Sub x()
Dim a As Worksheet: Set a = ThisWorkbook.Sheets("Chart")
Dim b&, c&, d#, e#, f#, g#, h As Boolean, i As Variant, j As Variant, k&, l As Variant
b = 13
c = a.Cells(a.Rows.Count, "B").End(xlUp).Row
If c >= 13 Then
With a.Range(a.Cells(13, 2), a.Cells(c, 25))
.ClearContents
.Interior.ColorIndex = xlNone
End With
End If
i = Array(14, 16, 18, 20, 22, 24)
Do
If b = 13 Then
a.Cells(b, 2).Formula = "=IF(Inputs!E9>Inputs!E10,Inputs!E10,Inputs!E9)"
Else
j = b - 1
Do While j > 1
If IsDate(a.Cells(j, 2).Value) Then Exit Do
j = j - 1
Loop
a.Cells(b, 2).Formula = "=DATE(YEAR(B" & j & ")+1,MONTH(B" & j & "),DAY(B" & j & "))"
End If
a.Cells(b, 3).Formula = "=YEAR(B" & b & ")"
a.Cells(b, 4).Formula = "=INT((B" & b & "-Inputs!$D$9)/365.25)"
a.Cells(b, 5).Formula = "=IF(D" & b & ">Inputs!$F$15,0,D" & b & ")"
a.Cells(b, 6).Formula = "=INT((B" & b & "-Inputs!$D$10)/365.25)"
a.Cells(b, 7).Formula = "=IF(F" & b & ">Inputs!$F$20,0,F" & b & ")"
Application.Calculate
d = a.Cells(b, 4).Value
e = a.Cells(b, 5).Value
f = a.Cells(b, 6).Value
g = a.Cells(b, 7).Value
If IsNumeric(d) And d > 90 Then a.Cells(b, 4).Font.Color = vbRed
If IsNumeric(e) And e > 90 Then a.Cells(b, 5).Font.Color = vbRed
If IsNumeric(f) And f > 90 Then a.Cells(b, 6).Font.Color = vbRed
If IsNumeric(g) And g > 90 Then a.Cells(b, 7).Font.Color = vbRed
If IsNumeric(d) And IsNumeric(e) And IsNumeric(f) And IsNumeric(g) Then
If (d >= 90 Or d = 0) And (e >= 90 Or e = 0) And (f >= 90 Or f = 0) And (g >= 90 Or g = 0) Then Exit Do
End If
h = False
If (IsNumeric(d) And d >= 90) Or (IsNumeric(e) And e >= 90) Or (IsNumeric(f) And f >= 90) Or (IsNumeric(g) And g >= 90) Then h = True
If Not h Then
a.Cells(b, 8).Formula = "=Math!AH" & (b - 4)
a.Cells(b, 9).Formula = "=Math!AL" & (b - 4)
a.Cells(b, 10).Formula = "=Math!AC" & (b - 4)
a.Cells(b, 12).Formula = "=IF(K" & b & "<0,0,K" & b & ")"
a.Cells(b, 13).Formula = "=IF(L" & b & "=0,AU" & b & ",L" & b & "+AU" & b & ")"
End If
For Each l In i
If b = 13 Then
Select Case l
Case 14: a.Cells(b, l).Formula = "=-FV(O" & b & "/12,12,-L" & b & "/12,N" & b - 1 & ",0)"
Case 16: a.Cells(b, l).Formula = "=-FV(Q" & b & "/12,12,0,P" & b - 1 & ",0)"
Case 18: a.Cells(b, l).Formula = "=-FV(S" & b & "/12,12,0,R" & b - 1 & ",0)"
Case 20: a.Cells(b, l).Formula = "=-FV(U" & b & "/12,12,0,T" & b - 1 & ",1)"
Case 22: a.Cells(b, l).Formula = "=-FV($W$13/12,12,0,V" & b - 1 & ",0)"
Case 24: a.Cells(b, l).Formula = "=-FV($Y$13/12,12,0,X" & b - 1 & ",0)"
End Select
ElseIf a.Cells(b - 3, l).HasFormula Then
If IsNumeric(a.Cells(b - 3, l).Value) Then
If a.Cells(b - 3, l).Value <= 0 Then
a.Cells(b, l).ClearContents
Else
Select Case l
Case 14: a.Cells(b, l).Formula = "=-FV(O" & b & "/12,12,-L" & b & "/12,N" & b - 3 & ",0)"
Case 16: a.Cells(b, l).Formula = "=-FV(Q" & b & "/12,12,0,P" & b - 3 & ",0)"
Case 18: a.Cells(b, l).Formula = "=-FV(S" & b & "/12,12,0,R" & b - 3 & ",0)"
Case 20: a.Cells(b, l).Formula = "=-FV(U" & b & "/12,12,0,T" & b - 3 & ",1)"
Case 22: a.Cells(b, l).Formula = "=-FV($W$13/12,12,0,V" & b - 3 & ",0)"
Case 24: a.Cells(b, l).Formula = "=-FV($Y$13/12,12,0,X" & b - 3 & ",0)"
End Select
End If
End If
End If
Next l
a.Cells(b, 15).Formula = "=$N$8"
a.Cells(b, 17).Formula = "=$P$7"
a.Cells(b, 19).Formula = "=$R$7"
a.Cells(b, 21).Formula = "=$T$7"
a.Cells(b, 23).Formula = "=$V$7"
a.Cells(b, 25).Formula = "=$X$7"
If d < 90 And e < 90 And f < 90 And g < 90 Then
k = b + 1
a.Rows(k).Insert
a.Cells(k, 2).Value = a.Cells(b, 3).Value & " RMD"
a.Cells(k, 3).Value = "'[PLACEHOLDER] RMD Formula"
a.Range(a.Cells(k, 2), a.Cells(k, 25)).Interior.Color = RGB(220, 220, 220)
k = k + 1
a.Rows(k).Insert
a.Cells(k, 2).Value = a.Cells(b, 3).Value & " Tax"
a.Cells(k, 3).Value = "'[PLACEHOLDER] Tax Formula"
a.Range(a.Cells(k, 2), a.Cells(k, 25)).Interior.Color = RGB(220, 220, 220)
k = k + 1
b = b + 3
Else
b = b + 1
End If
Loop
End Sub
