Microsoft Office Excel ® Kod Kılavuzu

Microsoft Office Excel® Code Guide

10 Nisan 2007 Salı

Loan Calculator



'Module1

Option Explicit
Dim Sayfa
Sub BorçHesabıDüzenle() 'Loan Calculator Create
    On Error Resume Next
    For Each Sayfa In ThisWorkbook.Sheets
    If Sayfa.Name = "LoanCalculator" Then GoTo Devam
    Next Sayfa
    ThisWorkbook.Worksheets.Add Before:=Sheets(1)
    ActiveSheet.Name = "LoanCalculator"
    Call FormülDüzenle
    Call Biçimlendir
    Call ÖrnekBorçHesabı
    Exit Sub
Devam:
    Sayfa.Select
    Call FormülDüzenle
    Call Biçimlendir
    Call ÖrnekBorçHesabı
End Sub
Private Sub FormülDüzenle() 'Formula Statement

    On Error Resume Next
    ActiveSheet.Unprotect
    Cells.Select
    Selection.Delete Shift:=xlUp
    Range("$A$1").FormulaR1C1 = "Ödeme Koşulları"
    Range("$E$1").FormulaR1C1 = "Ara Ödemeler Tablosu"
    Range("$K$1").FormulaR1C1 = "Periyodik Ödemeler Tablosu"
    Range("$G$3").FormulaR1C1 = "=SUM(R[2]C:R[65533]C)"
    Range("$I$3").FormulaR1C1 = "=SUM(R[2]C:R[65533]C)"
    Range("$M$3").FormulaR1C1 = "=SUM(R[2]C:R[65533]C)"
    Range("$N$3").FormulaR1C1 = "=SUM(R[2]C:R[65533]C)"
    Range("$O$3").FormulaR1C1 = "=SUM(R[2]C:R[65533]C)"
    Range("$A$4").FormulaR1C1 = "St"
    Range("$B$4").FormulaR1C1 = "Satış Tarihi"
    Range("$C$4").FormulaR1C1 = ""
    Range("$E$4").FormulaR1C1 = "Ödeme No"
    Range("$F$4").FormulaR1C1 = "Ödeme Tarihi"
    Range("$G$4").FormulaR1C1 = "Vadeli Tutar"
    Range("$H$4").FormulaR1C1 = "Faiz Tutarı"
    Range("$I$4").FormulaR1C1 = "Peşin Tutarı"
    Range("$K$4").FormulaR1C1 = "Ödeme No"
    Range("$L$4").FormulaR1C1 = "Ödeme Tarihi"
    Range("$M$4").FormulaR1C1 = "Ödeme Tutarı"
    Range("$N$4").FormulaR1C1 = "Faiz Tutarı"
    Range("$O$4").FormulaR1C1 = "Anapara Tutarı"
    Range("$P$4").FormulaR1C1 = "Kalan"
    Range("$A$5").FormulaR1C1 = "Pst"
    Range("$B$5").FormulaR1C1 = "Peşin satış Tutarı"
    Range("$C$5").FormulaR1C1 = ""
    Range("$E$5").FormulaR1C1 = "1"
    Range("$H$5").FormulaR1C1 = "=+RC[-1]-RC[1]"
    Range("$I$5").FormulaR1C1 = "=RC[-2]/(1+R12C3)^(RC[-3]-R4C3+1)"
    Range("$K$5").FormulaR1C1 = "1"
    Range("$L$5").FormulaR1C1 = "=DATE(YEAR(R4C3),MONTH(R4C3)+RC[-1]*R13C3,DAY(R4C3))"
    Range("$M$5").FormulaR1C1 = "=+R[11]C[-10]"
    Range("$N$5").FormulaR1C1 = "=R[4]C[-11]*R14C3"
    Range("$O$5").FormulaR1C1 = "=+RC[-2]-RC[-1]"
    Range("$P$5").FormulaR1C1 = "=R[4]C[-13]-RC[-1]"
    Range("$A$6").FormulaR1C1 = "Po"
    Range("$B$6").FormulaR1C1 = "Peşinat Oranı"
    Range("$C$6").FormulaR1C1 = ""
    Range("$E$6").FormulaR1C1 = "=R[-1]C+1"
    Range("$H$6").FormulaR1C1 = "=+RC[-1]-RC[1]"
    Range("$I$6").FormulaR1C1 = "=RC[-2]/(1+R12C3)^(RC[-3]-R4C3+1)"
    Range("$K$6").FormulaR1C1 = "=+R[-1]C+1"
    Range("$L$6").FormulaR1C1 = "=IF(RC[-1]>R15C3,"""",DATE(YEAR(R4C3),MONTH(R4C3)+RC[-1]*R13C3,DAY(R4C3)))"
    Range("$M$6").FormulaR1C1 = "=IF(RC[-2]>R15C3,0,R5C13)"
    Range("$N$6").FormulaR1C1 = "=IF(RC[-3]>R15C3,0,R[-1]C[2]*R14C3)"
    Range("$O$6").FormulaR1C1 = "=+RC[-2]-RC[-1]"
    Range("$P$6").FormulaR1C1 = "=+R[-1]C-RC[-1]"
    Range("$A$7").FormulaR1C1 = "Pt"
    Range("$B$7").FormulaR1C1 = "Peşinat Tutarı"
    Range("$C$7").FormulaR1C1 = "=+R[-2]C*R[-1]C"
    Range("$A$8").FormulaR1C1 = "Aöp"
    Range("$B$8").FormulaR1C1 = "Ara Ödemeleri Peşin Değeri"
    Range("$C$8").FormulaR1C1 = "=+R[-5]C[6]"
    Range("$A$9").FormulaR1C1 = "Vet"
    Range("$B$9").FormulaR1C1 = "Vadeye Esas Tutar"
    Range("$C$9").FormulaR1C1 = "=+R[-4]C-R[-2]C-R[-1]C"
    Range("$A$10").FormulaR1C1 = "Yfo"
    Range("$B$10").FormulaR1C1 = "Yıllık Faiz Oranı"
    Range("$C$10").FormulaR1C1 = ""
    Range("$A$11").FormulaR1C1 = "Afo"
    Range("$B$11").FormulaR1C1 = "Aylık Faiz Oranı"
    Range("$C$11").FormulaR1C1 = "=+R[-1]C/12"
    Range("$A$12").FormulaR1C1 = "Gfo"
    Range("$B$12").FormulaR1C1 = "Günlük Faiz Oranı"
    Range("$C$12").FormulaR1C1 = "=+R[-2]C/365"
    Range("$A$13").FormulaR1C1 = "Öp"
    Range("$B$13").FormulaR1C1 = "Ödeme Periyodu [1, 2, 3, 4..12 Ay]"
    Range("$C$13").FormulaR1C1 = ""
    Range("$A$14").FormulaR1C1 = "Pfo"
    Range("$B$14").FormulaR1C1 = "Periyodik Faiz Oranı"
    Range("$C$14").FormulaR1C1 = "=R[-4]C*R[-1]C/12"
    Range("$A$15").FormulaR1C1 = "Ts"
    Range("$B$15").FormulaR1C1 = "Taksit Sayısı [Adet]"
    Range("$C$15").FormulaR1C1 = ""
    Range("$A$16").FormulaR1C1 = "Tt"
    Range("$B$16").FormulaR1C1 = "Taksit Tutarı"
    Range("$C$16").FormulaR1C1 = "=IF(R[-2]C=0,0,(R[-7]C*R[-2]C)/(1-(1/(1+R[-2]C)^R[-1]C)))"
    Range("$A$17").FormulaR1C1 = "Vt"
    Range("$B$17").FormulaR1C1 = "Vadeli Tutar"
    Range("$C$17").FormulaR1C1 = "=+R[-1]C*R[-2]C"
    Range("$A$18").FormulaR1C1 = "Aöv"
    Range("$B$18").FormulaR1C1 = "Ara Ödemeleri Vadeli Değeri"
    Range("$C$18").FormulaR1C1 = "=+R[-15]C[4]"
    Range("$A$19").FormulaR1C1 = "Vst"
    Range("$B$19").FormulaR1C1 = "Vadeli Satış Tutarı"
    Range("$C$19").FormulaR1C1 = "=+R[-2]C+R[-12]C+R[-1]C"
    Range("E6:I6").Copy
    Range("E7:E52").PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Range("K6:P6").Copy
    Range("K7:K364").PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
End Sub
Private Sub Biçimlendir() 'Formatted

    On Error Resume Next
    With Range("A1:C1")
        .HorizontalAlignment = xlCenter: .VerticalAlignment = xlBottom: .WrapText = False: .Orientation = 0: .AddIndent = False: .IndentLevel = 0: .ShrinkToFit = False: .ReadingOrder = xlContext: .MergeCells = False: .Merge: .Font.Bold = True: .Borders(xlDiagonalDown).LineStyle = xlNone: .Borders(xlDiagonalUp).LineStyle = xlNone
        With .Borders(xlEdgeLeft)
            .LineStyle = xlContinuous: .Weight = xlMedium: .ColorIndex = xlAutomatic
        End With
        With .Borders(xlEdgeTop)
            .LineStyle = xlContinuous: .Weight = xlMedium: .ColorIndex = xlAutomatic:
        End With
        With .Borders(xlEdgeBottom)
            .LineStyle = xlContinuous: .Weight = xlMedium: .ColorIndex = xlAutomatic
        End With
        With .Borders(xlEdgeRight)
            .LineStyle = xlContinuous: .Weight = xlMedium: .ColorIndex = xlAutomatic
        End With
        .Borders(xlInsideVertical).LineStyle = xlNone
    End With
    With Range("A4:C19")
        .Borders(xlDiagonalDown).LineStyle = xlNone: .Borders(xlDiagonalUp).LineStyle = xlNone
        With .Borders(xlEdgeLeft)
            .LineStyle = xlContinuous: .Weight = xlThin: .ColorIndex = xlAutomatic
        End With
        With .Borders(xlEdgeTop)
            .LineStyle = xlContinuous: .Weight = xlThin: .ColorIndex = xlAutomatic
        End With
        With .Borders(xlEdgeBottom)
            .LineStyle = xlContinuous: .Weight = xlThin: .ColorIndex = xlAutomatic
        End With
        With .Borders(xlEdgeRight)
            .LineStyle = xlContinuous: .Weight = xlThin: .ColorIndex = xlAutomatic
        End With
        With .Borders(xlInsideVertical)
            .LineStyle = xlContinuous: .Weight = xlThin: .ColorIndex = xlAutomatic
        End With
        With .Borders(xlInsideHorizontal)
            .LineStyle = xlContinuous: .Weight = xlThin: .ColorIndex = xlAutomatic
        End With
        .Borders(xlDiagonalDown).LineStyle = xlNone: .Borders(xlDiagonalUp).LineStyle = xlNone
        With .Borders(xlEdgeLeft)
            .LineStyle = xlContinuous: .Weight = xlMedium: .ColorIndex = xlAutomatic
        End With
        With .Borders(xlEdgeTop)
            .LineStyle = xlContinuous: .Weight = xlMedium: .ColorIndex = xlAutomatic
        End With
        With .Borders(xlEdgeBottom)
            .LineStyle = xlContinuous: .Weight = xlMedium: .ColorIndex = xlAutomatic
        End With
        With .Borders(xlEdgeRight)
            .LineStyle = xlContinuous: .Weight = xlMedium: .ColorIndex = xlAutomatic
        End With
        With .Borders(xlInsideVertical)
            .LineStyle = xlContinuous: .Weight = xlThin: .ColorIndex = xlAutomatic
        End With
        With .Borders(xlInsideHorizontal)
            .LineStyle = xlContinuous: .Weight = xlThin: .ColorIndex = xlAutomatic
        End With
            .Borders(xlDiagonalDown).LineStyle = xlNone: .Borders(xlDiagonalUp).LineStyle = xlNone
        With .Borders(xlEdgeLeft)
            .LineStyle = xlContinuous: .Weight = xlMedium: .ColorIndex = xlAutomatic
        End With
        With .Borders(xlEdgeTop)
            .LineStyle = xlContinuous: .Weight = xlMedium: .ColorIndex = xlAutomatic
        End With
        With .Borders(xlEdgeBottom)
            .LineStyle = xlContinuous: .Weight = xlMedium: .ColorIndex = xlAutomatic
        End With
        With .Borders(xlEdgeRight)
            .LineStyle = xlContinuous: .Weight = xlMedium: .ColorIndex = xlAutomatic
        End With
        With .Borders(xlInsideHorizontal)
            .LineStyle = xlContinuous: .Weight = xlThin: .ColorIndex = xlAutomatic
        End With
        With .Font
            .Name = "Arial": .Size = 8: .Strikethrough = False: .Superscript = False: .Subscript = False: .OutlineFont = False: .Shadow = False: .Underline = xlUnderlineStyleNone: .ColorIndex = xlAutomatic
        End With
    End With
    Columns("B:B").EntireColumn.AutoFit: Columns("A:A").EntireColumn.AutoFit: Columns("C:C").ColumnWidth = 12
    With Range("C4")
        .NumberFormat = "m/d/yyyy": .Font.ColorIndex = 5: .Font.Bold = True: .Locked = False: .FormulaHidden = False
    End With
    With Range("C5")
        .NumberFormat = "#,##0.00": .Locked = False: .FormulaHidden = False: .Font.ColorIndex = 5: .Font.Bold = True
    End With
    With Range("C6")
        .NumberFormat = "0.00%": .Locked = False: .FormulaHidden = False: .Font.Bold = True: .Font.ColorIndex = 5
    End With
    Range("C7:C9").NumberFormat = "#,##0.00"
    With Range("C10")
        .NumberFormat = "0.00%": .Locked = False: .FormulaHidden = False: .Font.ColorIndex = 5: .Font.Bold = True
    End With
    Range("C11:C12").NumberFormat = "0.00%"
    With Range("C13")
        .Locked = False: .FormulaHidden = False: .Font.ColorIndex = 5: .Font.Bold = True
    End With
    Range("C14").NumberFormat = "0.00%"
    With Range("C15")
        .Locked = False: .FormulaHidden = False: .Font.Bold = True: .Font.ColorIndex = 5
    End With
    Range("C16:C19").NumberFormat = "#,##0.00"
    With Range("E1:I1")
        .HorizontalAlignment = xlCenter: .VerticalAlignment = xlBottom: .WrapText = False: .Orientation = 0: .AddIndent = False: .IndentLevel = 0: .ShrinkToFit = False: .ReadingOrder = xlContext: .MergeCells = False: .Merge: .Borders(xlDiagonalDown).LineStyle = xlNone: .Borders(xlDiagonalUp).LineStyle = xlNone
        With .Borders(xlEdgeLeft)
            .LineStyle = xlContinuous: .Weight = xlMedium: .ColorIndex = xlAutomatic
        End With
        With .Borders(xlEdgeTop)
            .LineStyle = xlContinuous: .Weight = xlMedium: .ColorIndex = xlAutomatic
        End With
        With .Borders(xlEdgeBottom)
            .LineStyle = xlContinuous: .Weight = xlMedium: .ColorIndex = xlAutomatic
        End With
        With .Borders(xlEdgeRight)
            .LineStyle = xlContinuous: .Weight = xlMedium: .ColorIndex = xlAutomatic
        End With
        .Borders(xlInsideVertical).LineStyle = xlNone: .Font.Bold = True
    End With
    With Range("E4:I52")
        With .Font
            .Name = "Arial": .Size = 8: .Strikethrough = False: .Superscript = False: .Subscript = False: .OutlineFont = False: .Shadow = False: .Underline = xlUnderlineStyleNone: .ColorIndex = xlAutomatic
        End With
        .Borders(xlDiagonalDown).LineStyle = xlNone: .Borders(xlDiagonalUp).LineStyle = xlNone
        With .Borders(xlEdgeLeft)
            .LineStyle = xlContinuous: .Weight = xlThin: .ColorIndex = xlAutomatic
        End With
        With .Borders(xlEdgeTop)
            .LineStyle = xlContinuous: .Weight = xlThin: .ColorIndex = xlAutomatic
        End With
        With .Borders(xlEdgeBottom)
            .LineStyle = xlContinuous: .Weight = xlThin: .ColorIndex = xlAutomatic
        End With
        With .Borders(xlEdgeRight)
            .LineStyle = xlContinuous: .Weight = xlThin: .ColorIndex = xlAutomatic
        End With
        With .Borders(xlInsideVertical)
            .LineStyle = xlContinuous: .Weight = xlThin: .ColorIndex = xlAutomatic
        End With
        With .Borders(xlInsideHorizontal)
            .LineStyle = xlContinuous: .Weight = xlThin: .ColorIndex = xlAutomatic
        End With
            .Borders(xlDiagonalDown).LineStyle = xlNone: .Borders(xlDiagonalUp).LineStyle = xlNone
        With .Borders(xlEdgeLeft)
            .LineStyle = xlContinuous: .Weight = xlMedium: .ColorIndex = xlAutomatic
        End With
        With .Borders(xlEdgeTop)
            .LineStyle = xlContinuous: .Weight = xlMedium: .ColorIndex = xlAutomatic
        End With
        With .Borders(xlEdgeBottom)
            .LineStyle = xlContinuous: .Weight = xlMedium: .ColorIndex = xlAutomatic
        End With
        With .Borders(xlEdgeRight)
            .LineStyle = xlContinuous: .Weight = xlMedium: .ColorIndex = xlAutomatic
        End With
        With .Borders(xlInsideVertical)
            .LineStyle = xlContinuous: .Weight = xlThin: .ColorIndex = xlAutomatic
        End With
        With .Borders(xlInsideHorizontal)
            .LineStyle = xlContinuous: .Weight = xlThin: .ColorIndex = xlAutomatic
        End With
    End With
    With Range("E4:I4")
        .Borders(xlDiagonalDown).LineStyle = xlNone: .Borders(xlDiagonalUp).LineStyle = xlNone
        With .Borders(xlEdgeLeft)
            .LineStyle = xlContinuous: .Weight = xlMedium: .ColorIndex = xlAutomatic
        End With
        With .Borders(xlEdgeTop)
            .LineStyle = xlContinuous: .Weight = xlMedium: .ColorIndex = xlAutomatic
        End With
        With .Borders(xlEdgeBottom)
            .LineStyle = xlContinuous: .Weight = xlMedium: .ColorIndex = xlAutomatic
        End With
        With .Borders(xlEdgeRight)
            .LineStyle = xlContinuous: .Weight = xlMedium: .ColorIndex = xlAutomatic
        End With
        With .Borders(xlInsideVertical)
            .LineStyle = xlContinuous: .Weight = xlThin: .ColorIndex = xlAutomatic
        End With
        .Font.Bold = True: .HorizontalAlignment = xlCenter: .VerticalAlignment = xlBottom: .WrapText = False: .Orientation = 0: .AddIndent = False: .IndentLevel = 0: .ShrinkToFit = False: .ReadingOrder = xlContext: .MergeCells = False
    End With
    Columns("E:E").ColumnWidth = 9
    With Range("E5:E52")
        .HorizontalAlignment = xlCenter: .VerticalAlignment = xlBottom: .WrapText = False: .Orientation = 0: .AddIndent = False: .IndentLevel = 0: .ShrinkToFit = False: .ReadingOrder = xlContext: .MergeCells = False
    End With
    Columns("F:I").ColumnWidth = 12
    With Range("G3")
        With .Font
            .Name = "Arial": .Size = 8: .Strikethrough = False: .Superscript = False: .Subscript = False: .OutlineFont = False: .Shadow = False: .Underline = xlUnderlineStyleNone: .ColorIndex = xlAutomatic
        End With
            .Borders(xlDiagonalDown).LineStyle = xlNone: .Borders(xlDiagonalUp).LineStyle = xlNone
        With .Borders(xlEdgeLeft)
            .LineStyle = xlContinuous: .Weight = xlMedium: .ColorIndex = xlAutomatic
        End With
        With .Borders(xlEdgeTop)
            .LineStyle = xlContinuous: .Weight = xlMedium: .ColorIndex = xlAutomatic
        End With
        With .Borders(xlEdgeBottom)
            .LineStyle = xlContinuous: .Weight = xlMedium: .ColorIndex = xlAutomatic
        End With
        With .Borders(xlEdgeRight)
            .LineStyle = xlContinuous: .Weight = xlMedium: .ColorIndex = xlAutomatic
        End With
        .NumberFormat = "#,##0.00"
    End With
    With Range("I3")
        With .Font
            .Name = "Arial": .Size = 8: .Strikethrough = False: .Superscript = False: .Subscript = False: .OutlineFont = False: .Shadow = False: .Underline = xlUnderlineStyleNone: .ColorIndex = xlAutomatic
        End With
        .Borders(xlDiagonalDown).LineStyle = xlNone: .Borders(xlDiagonalUp).LineStyle = xlNone
        With .Borders(xlEdgeLeft)
            .LineStyle = xlContinuous: .Weight = xlMedium: .ColorIndex = xlAutomatic
        End With
        With .Borders(xlEdgeTop)
            .LineStyle = xlContinuous: .Weight = xlMedium: .ColorIndex = xlAutomatic
        End With
        With .Borders(xlEdgeBottom)
            .LineStyle = xlContinuous: .Weight = xlMedium: .ColorIndex = xlAutomatic
        End With
        With .Borders(xlEdgeRight)
            .LineStyle = xlContinuous: .Weight = xlMedium: .ColorIndex = xlAutomatic
        End With
        .NumberFormat = "#,##0.00"
        End With
        With Range("F5:F52")
            .NumberFormat = "m/d/yyyy": .Font.ColorIndex = 5: .Locked = False: .FormulaHidden = False: .Font.Bold = True
        End With
        With Range("G5:G52")
            .NumberFormat = "#,##0.00": .Locked = False: .FormulaHidden = False: .Font.Bold = True: .Font.ColorIndex = 5
        End With
        Range("H5:I52").NumberFormat = "#,##0.00": Columns("D:D").ColumnWidth = 1: Columns("J:J").ColumnWidth = 1
        With Range("K1:P1")
            .HorizontalAlignment = xlCenter: .VerticalAlignment = xlBottom: .WrapText = False: .Orientation = 0: .AddIndent = False: .IndentLevel = 0: .ShrinkToFit = False: .ReadingOrder = xlContext: .MergeCells = False: .Merge: .Font.Bold = True: .Borders(xlDiagonalDown).LineStyle = xlNone: .Borders(xlDiagonalUp).LineStyle = xlNone
            With .Borders(xlEdgeLeft)
                .LineStyle = xlContinuous: .Weight = xlMedium: .ColorIndex = xlAutomatic
            End With
            With .Borders(xlEdgeTop)
                .LineStyle = xlContinuous: .Weight = xlMedium: .ColorIndex = xlAutomatic
            End With
            With .Borders(xlEdgeBottom)
                .LineStyle = xlContinuous: .Weight = xlMedium: .ColorIndex = xlAutomatic
            End With
            With .Borders(xlEdgeRight)
                .LineStyle = xlContinuous: .Weight = xlMedium: .ColorIndex = xlAutomatic
            End With
            .Borders(xlInsideVertical).LineStyle = xlNone
        End With
        With Range("M3:O3")
        .NumberFormat = "#,##0.00"
        With .Font
            .Name = "Arial": .Size = 8: .Strikethrough = False: .Superscript = False: .Subscript = False: .OutlineFont = False: .Shadow = False: .Underline = xlUnderlineStyleNone: .ColorIndex = xlAutomatic
        End With
    End With
    With Range("M3")
        .Borders(xlDiagonalDown).LineStyle = xlNone: .Borders(xlDiagonalUp).LineStyle = xlNone
        With .Borders(xlEdgeLeft)
            .LineStyle = xlContinuous: .Weight = xlMedium: .ColorIndex = xlAutomatic
        End With
        With .Borders(xlEdgeTop)
            .LineStyle = xlContinuous: .Weight = xlMedium: .ColorIndex = xlAutomatic
        End With
        With .Borders(xlEdgeBottom)
            .LineStyle = xlContinuous: .Weight = xlMedium: .ColorIndex = xlAutomatic
        End With
        With .Borders(xlEdgeRight)
            .LineStyle = xlContinuous: .Weight = xlMedium: .ColorIndex = xlAutomatic
        End With
    End With
    With Range("N3")
        .Borders(xlDiagonalDown).LineStyle = xlNone: .Borders(xlDiagonalUp).LineStyle = xlNone
        With .Borders(xlEdgeLeft)
            .LineStyle = xlContinuous: .Weight = xlMedium: .ColorIndex = xlAutomatic
        End With
        With .Borders(xlEdgeTop)
            .LineStyle = xlContinuous: .Weight = xlMedium: .ColorIndex = xlAutomatic
        End With
        With .Borders(xlEdgeBottom)
            .LineStyle = xlContinuous: .Weight = xlMedium: .ColorIndex = xlAutomatic
        End With
        With .Borders(xlEdgeRight)
            .LineStyle = xlContinuous: .Weight = xlMedium: .ColorIndex = xlAutomatic
        End With
    End With
    With Range("O3")
        .Borders(xlDiagonalDown).LineStyle = xlNone: .Borders(xlDiagonalUp).LineStyle = xlNone
        With .Borders(xlEdgeLeft)
            .LineStyle = xlContinuous: .Weight = xlMedium: .ColorIndex = xlAutomatic
        End With
        With .Borders(xlEdgeTop)
            .LineStyle = xlContinuous: .Weight = xlMedium: .ColorIndex = xlAutomatic
        End With
        With .Borders(xlEdgeBottom)
            .LineStyle = xlContinuous: .Weight = xlMedium: .ColorIndex = xlAutomatic
        End With
        With .Borders(xlEdgeRight)
            .LineStyle = xlContinuous: .Weight = xlMedium: .ColorIndex = xlAutomatic
        End With
    End With
    With Range("K4:P364")
        With .Font
            .Name = "Arial": .Size = 8: .Strikethrough = False: .Superscript = False: .Subscript = False: .OutlineFont = False: .Shadow = False: .Underline = xlUnderlineStyleNone: .ColorIndex = xlAutomatic
        End With
            .Borders(xlDiagonalDown).LineStyle = xlNone: .Borders(xlDiagonalUp).LineStyle = xlNone
        With .Borders(xlEdgeLeft)
            .LineStyle = xlContinuous: .Weight = xlThin: .ColorIndex = xlAutomatic
        End With
        With .Borders(xlEdgeTop)
            .LineStyle = xlContinuous: .Weight = xlThin: .ColorIndex = xlAutomatic
        End With
        With .Borders(xlEdgeBottom)
            .LineStyle = xlContinuous: .Weight = xlThin: .ColorIndex = xlAutomatic
        End With
        With .Borders(xlEdgeRight)
            .LineStyle = xlContinuous: .Weight = xlThin: .ColorIndex = xlAutomatic
        End With
        With .Borders(xlInsideVertical)
            .LineStyle = xlContinuous: .Weight = xlThin: .ColorIndex = xlAutomatic
        End With
        With .Borders(xlInsideHorizontal)
            .LineStyle = xlContinuous: .Weight = xlThin: .ColorIndex = xlAutomatic
        End With
            .Borders(xlDiagonalDown).LineStyle = xlNone: .Borders(xlDiagonalUp).LineStyle = xlNone
        With .Borders(xlEdgeLeft)
            .LineStyle = xlContinuous: .Weight = xlMedium: .ColorIndex = xlAutomatic
        End With
        With .Borders(xlEdgeTop)
            .LineStyle = xlContinuous: .Weight = xlMedium: .ColorIndex = xlAutomatic
        End With
        With .Borders(xlEdgeBottom)
            .LineStyle = xlContinuous: .Weight = xlMedium: .ColorIndex = xlAutomatic
        End With
        With .Borders(xlEdgeRight)
            .LineStyle = xlContinuous: .Weight = xlMedium: .ColorIndex = xlAutomatic
        End With
        With .Borders(xlInsideVertical)
            .LineStyle = xlContinuous: .Weight = xlThin: .ColorIndex = xlAutomatic
        End With
        With .Borders(xlInsideHorizontal)
            .LineStyle = xlContinuous: .Weight = xlThin: .ColorIndex = xlAutomatic
        End With
    End With
    With Range("K4:P4")
        .Borders(xlDiagonalDown).LineStyle = xlNone: .Borders(xlDiagonalUp).LineStyle = xlNone
        With .Borders(xlEdgeLeft)
            .LineStyle = xlContinuous: .Weight = xlMedium: .ColorIndex = xlAutomatic
        End With
        With .Borders(xlEdgeTop)
            .LineStyle = xlContinuous: .Weight = xlMedium: .ColorIndex = xlAutomatic
        End With
        With .Borders(xlEdgeBottom)
            .LineStyle = xlContinuous: .Weight = xlMedium: .ColorIndex = xlAutomatic
        End With
        With .Borders(xlEdgeRight)
            .LineStyle = xlContinuous: .Weight = xlMedium: .ColorIndex = xlAutomatic
        End With
        With .Borders(xlInsideVertical)
            .LineStyle = xlContinuous: .Weight = xlThin: .ColorIndex = xlAutomatic
        End With
        .Font.Bold = True
        .HorizontalAlignment = xlCenter: .VerticalAlignment = xlBottom: .WrapText = False: .Orientation = 0: .AddIndent = False: .IndentLevel = 0: .ShrinkToFit = False: .ReadingOrder = xlContext: .MergeCells = False
    End With
    Columns("K:K").ColumnWidth = 9: Columns("L:P").ColumnWidth = 12
    With Range("L5:L364")
        .NumberFormat = "m/d/yyyy": .HorizontalAlignment = xlCenter: .VerticalAlignment = xlBottom: .WrapText = False: .Orientation = 0: .AddIndent = False: .IndentLevel = 0: .ShrinkToFit = False: .ReadingOrder = xlContext: .MergeCells = False
    End With
    With Range("K5:K364")
        .HorizontalAlignment = xlCenter: .VerticalAlignment = xlBottom: .WrapText = False: .Orientation = 0: .AddIndent = False: .IndentLevel = 0: .ShrinkToFit = False: .ReadingOrder = xlContext: .MergeCells = False
    End With
    Range("M5:P364").NumberFormat = "#,##0.00"
    Rows("2:2").RowHeight = 6
    Range("C4").Select
    With Range("A1:C1,E1:I1,K1:P1,E4:I4,K4:P4").Interior
        .Pattern = xlLightUp
        .PatternThemeColor = xlThemeColorDark1
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .PatternTintAndShade = -0.249946592608417
    End With
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub
Private Sub ÖrnekBorçHesabı() 'Example

    On Error Resume Next
    Range("C4").FormulaR1C1 = "6/4/2009"
    Range("C5").FormulaR1C1 = "6000000"
    Range("C6").FormulaR1C1 = "25%"
    Range("C10").FormulaR1C1 = "10%"
    Range("C13").FormulaR1C1 = "3"
    Range("C15").FormulaR1C1 = "36"
    Range("F5").FormulaR1C1 = "7/12/2009"
    Range("F6").FormulaR1C1 = "9/6/2009"
    Range("F7").FormulaR1C1 = "11/19/2009"
    Range("F8").FormulaR1C1 = "12/24/2009"
    Range("F9").FormulaR1C1 = "3/27/2010"
    Range("G5").FormulaR1C1 = "200000"
    Range("G6").FormulaR1C1 = "210000"
    Range("G7").FormulaR1C1 = "165000"
    Range("G8").FormulaR1C1 = "320000"
    Range("G9").FormulaR1C1 = "290000"
End Sub

Hiç yorum yok:

Blog Arşivi

Bu gadget'ta bir hata oluştu

Bu Blogda Ara

Contributor

Contributor
Mustafa ULUSARAÇ İstanbul, TÜRKİYE
free counters
T. C. Central Bank Indicative Exchange Rates
Currency Exchange Rate Widget,Currency Converter Widget
Borsa İstanbul