Page 195 - Microsoft Office Excel 2003 Programming Inside Out

Part 3: The Excel Object Model
Ranges and Cells

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]" Chapter 8
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

169