Microsoft Office Tutorials and References
In Depth Information
strR1C1Ad dress = "R" & Format(intRow, "#0") & "C" & _
Format(intColumn, "#0")
strA1Address = Appli
cation.ConvertFormula(strR1C1Address, _
xlR1C1, xlA1)
Range(strA1Addres s) = "Payment:" + Str$(counter)
Range(strA1Addres s).Range("B1") = "=PPMT(APR/" & _
Str$(Fre quency) & "," & Str$(counter) & _
", Payments,Principal, Future_Value)"
Ran ge(strA1Address).Select
Selection .Range("C1") = "=IPMT(APR/" & Str$(Frequency) _
& "," & Str$(counter) & ",Payments,Principal," _
& " Future_Value)"
Selection.Of
fset(0, 3) = "=R[-1]C+RC[-2]"
Selection.Of
fset(0, 4) = "=RC[-2]+r[-1]c"
Next counter
' Reset "Pres ent Value" and "Interest Paid" formulas for
' first payment
Range("D6").Form ula = "=Principal+B6"
Range("E6" ).Formula = "=C6"
' Autosize colum ns to necessary width
Colu mns("A:E").Select
Columns.En tireColumn.AutoFit
' Set Titles
Range("A1").Valu e = ActiveSheet.Name
Rang e("A1:E1").Select
Selection.Merge
Range("A2").Value = "Amortization Chart"
Rang e("A2:E2").Merge
With Range("A1")
.Horiz ontalAlignment = xlCenter
.Font.Bold = True
End With
With Range("A2")
.Horiz ontalAlignment = xlCenter
.Font.Bold = True
End With
End Sub
Search JabSto ::




Custom Search