Microsoft Office Excel ® Kod Kılavuzu

Microsoft Office Excel® Code Guide

10 Ekim 2003 Cuma

Dağıtım ve Depolama Maliyetleri En İyileme Hesabı (Optimum Transport Costing)

'Module1
Option Explicit
Private Sub En_Iyi_Dagitim_Depolama_Maliyeti() 'Optimum Transport Costing
    On Error Resume Next
    Dim i As Integer
    Dim Adet As Integer
    Dim Kolon As Integer
    Dim Satir As Integer
    Dim Kaynak As String
    Dim Hedef1 As String
    Dim Hedef2 As String
    ActiveSheet.Unprotect
    Range("D6:I6,D9:I9,D12:I12,D15:I15,D18:I18,D21:I21").ClearContents
    Range("Q6:T41").ClearContents
    Range("N6:P41").Copy
    Range("Q6:S41").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Range("Q5:S41").Sort Key1:=Range("S6"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
    Adet = Range("Q6:T41").Rows.Count
    For i = 1 To Adet
        Kaynak = "Q" & (5 + i) & ":R" & (5 + i)
        Hedef1 = "T" & (5 + i)
        Range(Kaynak).Copy
        Range("V5:W5").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        Kolon = Application.WorksheetFunction.Match(Range("V5"), Range("D5:I5"), 0)
        Satir = Application.WorksheetFunction.Match(Range("W5"), Range("C6:C23"), 0)
        Hedef2 = Range("D6:I23").Cells(Satir, Kolon).Address
        If Range("Z5").Value <> 0 Then
        Range("Z5").Copy
            Range(Hedef1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            Range(Hedef2).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        End If
    Next i
    Range("J25").Select
    ActiveSheet.Protect
End Sub
Sub Tablo_Hazirla()

    On Error Resume Next
    Dim i As Integer
    Dim WS As Worksheet
    For Each WS In ThisWorkbook.Worksheets
        If WS.Name = "Optimum_Transport_Costing" Then
            VBA.MsgBox "Optimum_Transport_Costing çalışma sayfası mevcuttur.", vbInformation, "[PBİD®"
            Exit Sub
        End If
    Next WS
    With ThisWorkbook.Worksheets.Add
        .Unprotect
        .Name = "Optimum_Transport_Costing"
    End With
    Columns("A:A").ColumnWidth = 1
    Columns("B:B").ColumnWidth = 4
    Columns("C:C").ColumnWidth = 7
    Columns("D:J").ColumnWidth = 10
    Columns("K:L").ColumnWidth = 8
    Columns("M:M").ColumnWidth = 1
    Columns("N:O").ColumnWidth = 4
    Columns("P:P").ColumnWidth = 10
    Columns("Q:R").ColumnWidth = 4
    Columns("S:S").ColumnWidth = 10
    Columns("T:T").ColumnWidth = 8.43
    Columns("U:U").ColumnWidth = 1
    Columns("V:W").ColumnWidth = 4
    Columns("X:Z").ColumnWidth = 8
    Columns("AA:AA").ColumnWidth = 1
    Range("B2").FormulaR1C1 = "EN İYİLENMİŞ (Optimum) DAĞITIM ve DEPOLAMA MALİYETİ HESAPLAMASI"
    Range("B4").FormulaR1C1 = "Veri Tabanı"
    Range("B6").FormulaR1C1 = "Dağıtım ve Depolama Yerleri (DDY)"
    Range("B24").FormulaR1C1 = "Toplam"
    Range("B27").FormulaR1C1 = "Toplam Miktar"
    Range("B28").FormulaR1C1 = "Kalan Miktar"
    Range("C6").FormulaR1C1 = "D1"
    Range("C9").FormulaR1C1 = "D2"
    Range("C12").FormulaR1C1 = "D3"
    Range("C15").FormulaR1C1 = "D4"
    Range("C18").FormulaR1C1 = "D5"
    Range("C21").FormulaR1C1 = "D6"
    Range("D4").FormulaR1C1 = "Üretim Yerleri (ÜY)"
    Range("D5").FormulaR1C1 = "Ü1"
    Range("D6").FormulaR1C1 = ""
    Range("D7").FormulaR1C1 = "3.45"
    Range("D8").FormulaR1C1 = "=R[-2]C*R[-1]C"
    Range("D9").FormulaR1C1 = ""
    Range("D10").FormulaR1C1 = "4.35"
    Range("D11").FormulaR1C1 = "=R[-2]C*R[-1]C"
    Range("D12").FormulaR1C1 = ""
    Range("D13").FormulaR1C1 = "6.2"
    Range("D14").FormulaR1C1 = "=R[-2]C*R[-1]C"
    Range("D15").FormulaR1C1 = ""
    Range("D16").FormulaR1C1 = "9.15"
    Range("D17").FormulaR1C1 = "=R[-2]C*R[-1]C"
    Range("D18").FormulaR1C1 = ""
    Range("D19").FormulaR1C1 = "3.35"
    Range("D20").FormulaR1C1 = "=R[-2]C*R[-1]C"
    Range("D21").FormulaR1C1 = ""
    Range("D22").FormulaR1C1 = "1.2"
    Range("D23").FormulaR1C1 = "=R[-2]C*R[-1]C"
    Range("D24").FormulaR1C1 = "=R[-18]C+R[-15]C+R[-12]C+R[-9]C+R[-6]C+R[-3]C"
    Range("D25").FormulaR1C1 = "=IF(R[-1]C=0,0,R[1]C/R[-1]C)"
    Range("D26").FormulaR1C1 = "=R[-18]C+R[-15]C+R[-12]C+R[-9]C+R[-6]C+R[-3]C"
    Range("D27").FormulaR1C1 = "1835"
    Range("D28").FormulaR1C1 = "=R[-1]C-R[-4]C"
    Range("E5").FormulaR1C1 = "Ü2"
    Range("E6").FormulaR1C1 = ""
    Range("E7").FormulaR1C1 = "1.4"
    Range("E8").FormulaR1C1 = "=R[-2]C*R[-1]C"
    Range("E9").FormulaR1C1 = ""
    Range("E10").FormulaR1C1 = "0.95"
    Range("E11").FormulaR1C1 = "=R[-2]C*R[-1]C"
    Range("E12").FormulaR1C1 = ""
    Range("E13").FormulaR1C1 = "0.65"
    Range("E14").FormulaR1C1 = "=R[-2]C*R[-1]C"
    Range("E15").FormulaR1C1 = ""
    Range("E16").FormulaR1C1 = "3.45"
    Range("E17").FormulaR1C1 = "=R[-2]C*R[-1]C"
    Range("E18").FormulaR1C1 = ""
    Range("E19").FormulaR1C1 = "7.25"
    Range("E20").FormulaR1C1 = "=R[-2]C*R[-1]C"
    Range("E21").FormulaR1C1 = ""
    Range("E22").FormulaR1C1 = "1.45"
    Range("E23").FormulaR1C1 = "=R[-2]C*R[-1]C"
    Range("E24").FormulaR1C1 = "=R[-18]C+R[-15]C+R[-12]C+R[-9]C+R[-6]C+R[-3]C"
    Range("E25").FormulaR1C1 = "=IF(R[-1]C=0,0,R[1]C/R[-1]C)"
    Range("E26").FormulaR1C1 = "=R[-18]C+R[-15]C+R[-12]C+R[-9]C+R[-6]C+R[-3]C"
    Range("E27").FormulaR1C1 = "2155"
    Range("E28").FormulaR1C1 = "=R[-1]C-R[-4]C"
    Range("F5").FormulaR1C1 = "Ü3"
    Range("F6").FormulaR1C1 = ""
    Range("F7").FormulaR1C1 = "5.35"
    Range("F8").FormulaR1C1 = "=R[-2]C*R[-1]C"
    Range("F9").FormulaR1C1 = ""
    Range("F10").FormulaR1C1 = "3.4"
    Range("F11").FormulaR1C1 = "=R[-2]C*R[-1]C"
    Range("F12").FormulaR1C1 = ""
    Range("F13").FormulaR1C1 = "4.5"
    Range("F14").FormulaR1C1 = "=R[-2]C*R[-1]C"
    Range("F15").FormulaR1C1 = ""
    Range("F16").FormulaR1C1 = "5.15"
    Range("F17").FormulaR1C1 = "=R[-2]C*R[-1]C"
    Range("F18").FormulaR1C1 = ""
    Range("F19").FormulaR1C1 = "0.65"
    Range("F20").FormulaR1C1 = "=R[-2]C*R[-1]C"
    Range("F21").FormulaR1C1 = ""
    Range("F22").FormulaR1C1 = "1.45"
    Range("F23").FormulaR1C1 = "=R[-2]C*R[-1]C"
    Range("F24").FormulaR1C1 = "=R[-18]C+R[-15]C+R[-12]C+R[-9]C+R[-6]C+R[-3]C"
    Range("F25").FormulaR1C1 = "=IF(R[-1]C=0,0,R[1]C/R[-1]C)"
    Range("F26").FormulaR1C1 = "=R[-18]C+R[-15]C+R[-12]C+R[-9]C+R[-6]C+R[-3]C"
    Range("F27").FormulaR1C1 = "3650"
    Range("F28").FormulaR1C1 = "=R[-1]C-R[-4]C"
    Range("G5").FormulaR1C1 = "Ü4"
    Range("G6").FormulaR1C1 = ""
    Range("G7").FormulaR1C1 = "5.35"
    Range("G8").FormulaR1C1 = "=R[-2]C*R[-1]C"
    Range("G9").FormulaR1C1 = ""
    Range("G10").FormulaR1C1 = "6.8"
    Range("G11").FormulaR1C1 = "=R[-2]C*R[-1]C"
    Range("G12").FormulaR1C1 = ""
    Range("G13").FormulaR1C1 = "4.95"
    Range("G14").FormulaR1C1 = "=R[-2]C*R[-1]C"
    Range("G15").FormulaR1C1 = ""
    Range("G16").FormulaR1C1 = "1.75"
    Range("G17").FormulaR1C1 = "=R[-2]C*R[-1]C"
    Range("G18").FormulaR1C1 = ""
    Range("G19").FormulaR1C1 = "2.25"
    Range("G20").FormulaR1C1 = "=R[-2]C*R[-1]C"
    Range("G21").FormulaR1C1 = ""
    Range("G22").FormulaR1C1 = "5.35"
    Range("G23").FormulaR1C1 = "=R[-2]C*R[-1]C"
    Range("G24").FormulaR1C1 = "=R[-18]C+R[-15]C+R[-12]C+R[-9]C+R[-6]C+R[-3]C"
    Range("G25").FormulaR1C1 = "=IF(R[-1]C=0,0,R[1]C/R[-1]C)"
    Range("G26").FormulaR1C1 = "=R[-18]C+R[-15]C+R[-12]C+R[-9]C+R[-6]C+R[-3]C"
    Range("G27").FormulaR1C1 = "1715"
    Range("G28").FormulaR1C1 = "=R[-1]C-R[-4]C"
    Range("H5").FormulaR1C1 = "Ü5"
    Range("H6").FormulaR1C1 = ""
    Range("H7").FormulaR1C1 = "3.45"
    Range("H8").FormulaR1C1 = "=R[-2]C*R[-1]C"
    Range("H9").FormulaR1C1 = ""
    Range("H10").FormulaR1C1 = "3.85"
    Range("H11").FormulaR1C1 = "=R[-2]C*R[-1]C"
    Range("H12").FormulaR1C1 = ""
    Range("H13").FormulaR1C1 = "2.65"
    Range("H14").FormulaR1C1 = "=R[-2]C*R[-1]C"
    Range("H15").FormulaR1C1 = ""
    Range("H16").FormulaR1C1 = "5.75"
    Range("H17").FormulaR1C1 = "=R[-2]C*R[-1]C"
    Range("H18").FormulaR1C1 = ""
    Range("H19").FormulaR1C1 = "8"
    Range("H20").FormulaR1C1 = "=R[-2]C*R[-1]C"
    Range("H21").FormulaR1C1 = ""
    Range("H22").FormulaR1C1 = "2.25"
    Range("H23").FormulaR1C1 = "=R[-2]C*R[-1]C"
    Range("H24").FormulaR1C1 = "=R[-18]C+R[-15]C+R[-12]C+R[-9]C+R[-6]C+R[-3]C"
    Range("H25").FormulaR1C1 = "=IF(R[-1]C=0,0,R[1]C/R[-1]C)"
    Range("H26").FormulaR1C1 = "=R[-18]C+R[-15]C+R[-12]C+R[-9]C+R[-6]C+R[-3]C"
    Range("H27").FormulaR1C1 = "2455"
    Range("H28").FormulaR1C1 = "=R[-1]C-R[-4]C"
    Range("I5").FormulaR1C1 = "Ü6"
    Range("I6").FormulaR1C1 = ""
    Range("I7").FormulaR1C1 = "4.65"
    Range("I8").FormulaR1C1 = "=R[-2]C*R[-1]C"
    Range("I9").FormulaR1C1 = ""
    Range("I10").FormulaR1C1 = "11.45"
    Range("I11").FormulaR1C1 = "=R[-2]C*R[-1]C"
    Range("I12").FormulaR1C1 = ""
    Range("I13").FormulaR1C1 = "5.85"
    Range("I14").FormulaR1C1 = "=R[-2]C*R[-1]C"
    Range("I15").FormulaR1C1 = ""
    Range("I16").FormulaR1C1 = "4.75"
    Range("I17").FormulaR1C1 = "=R[-2]C*R[-1]C"
    Range("I18").FormulaR1C1 = ""
    Range("I19").FormulaR1C1 = "3.75"
    Range("I20").FormulaR1C1 = "=R[-2]C*R[-1]C"
    Range("I21").FormulaR1C1 = ""
    Range("I22").FormulaR1C1 = "6.25"
    Range("I23").FormulaR1C1 = "=R[-2]C*R[-1]C"
    Range("I24").FormulaR1C1 = "=R[-18]C+R[-15]C+R[-12]C+R[-9]C+R[-6]C+R[-3]C"
    Range("I25").FormulaR1C1 = "=IF(R[-1]C=0,0,R[1]C/R[-1]C)"
    Range("I26").FormulaR1C1 = "=R[-18]C+R[-15]C+R[-12]C+R[-9]C+R[-6]C+R[-3]C"
    Range("I27").FormulaR1C1 = "715"
    Range("I28").FormulaR1C1 = "=R[-1]C-R[-4]C"
    Range("J4").FormulaR1C1 = "Toplam"
    Range("J6").FormulaR1C1 = "=SUM(RC[-6]:RC[-1])"
    Range("J7").FormulaR1C1 = "=IF(R[-1]C=0,0,R[1]C/R[-1]C)"
    Range("J8").FormulaR1C1 = "=SUM(RC[-6]:RC[-1])"
    Range("J9").FormulaR1C1 = "=SUM(RC[-6]:RC[-1])"
    Range("J10").FormulaR1C1 = "=IF(R[-1]C=0,0,R[1]C/R[-1]C)"
    Range("J11").FormulaR1C1 = "=SUM(RC[-6]:RC[-1])"
    Range("J12").FormulaR1C1 = "=SUM(RC[-6]:RC[-1])"
    Range("J13").FormulaR1C1 = "=IF(R[-1]C=0,0,R[1]C/R[-1]C)"
    Range("J14").FormulaR1C1 = "=SUM(RC[-6]:RC[-1])"
    Range("J15").FormulaR1C1 = "=SUM(RC[-6]:RC[-1])"
    Range("J16").FormulaR1C1 = "=IF(R[-1]C=0,0,R[1]C/R[-1]C)"
    Range("J17").FormulaR1C1 = "=SUM(RC[-6]:RC[-1])"
    Range("J18").FormulaR1C1 = "=SUM(RC[-6]:RC[-1])"
    Range("J19").FormulaR1C1 = "=IF(R[-1]C=0,0,R[1]C/R[-1]C)"
    Range("J20").FormulaR1C1 = "=SUM(RC[-6]:RC[-1])"
    Range("J21").FormulaR1C1 = "=SUM(RC[-6]:RC[-1])"
    Range("J22").FormulaR1C1 = "=IF(R[-1]C=0,0,R[1]C/R[-1]C)"
    Range("J23").FormulaR1C1 = "=SUM(RC[-6]:RC[-1])"
    Range("J24").FormulaR1C1 = "=SUM(RC[-6]:RC[-1])"
    Range("J25").FormulaR1C1 = "=IF(R[-1]C=0,0,R[1]C/R[-1]C)"
    Range("J26").FormulaR1C1 = "=SUM(RC[-6]:RC[-1])"
    Range("J27").FormulaR1C1 = "=RC[-6]+RC[-5]+RC[-4]+RC[-3]+RC[-2]+RC[-1]"
    Range("J28").FormulaR1C1 = "=R[-1]C-R[-4]C"
    Range("K4").FormulaR1C1 = "Toplam Miktar"
    Range("K6").FormulaR1C1 = "765"
    Range("K9").FormulaR1C1 = "1385"
    Range("K12").FormulaR1C1 = "2300"
    Range("K15").FormulaR1C1 = "1855"
    Range("K18").FormulaR1C1 = "1415"
    Range("K21").FormulaR1C1 = "=12525-7720"
    Range("K24").FormulaR1C1 = "=SUM(R[-18]C:R[-1]C)"
    Range("L4").FormulaR1C1 = "Kalan Miktar"
    Range("L6").FormulaR1C1 = "=RC[-1]-RC[-2]"
    Range("L9").FormulaR1C1 = "=RC[-1]-RC[-2]"
    Range("L12").FormulaR1C1 = "=RC[-1]-RC[-2]"
    Range("L15").FormulaR1C1 = "=RC[-1]-RC[-2]"
    Range("L18").FormulaR1C1 = "=RC[-1]-RC[-2]"
    Range("L21").FormulaR1C1 = "=RC[-1]-RC[-2]"
    Range("L24").FormulaR1C1 = "=RC[-1]-RC[-2]"
    Range("N4").FormulaR1C1 = "Mevcut Veri Tabanı"
    Range("N5").FormulaR1C1 = "ÜY"
    For i = 1 To 6
        Range("N6:N11").Cells(i, 1) = "=R5C4"
    Next i
    For i = 1 To 6
        Range("N12:N17").Cells(i, 1) = "=R5C5"
    Next i
    For i = 1 To 6
        Range("N18:N23").Cells(i, 1) = "=R5C6"
    Next i
    For i = 1 To 6
        Range("N24:N29").Cells(i, 1) = "=R5C7"
    Next i
    For i = 1 To 6
        Range("N30:N35").Cells(i, 1) = "=R5C8"
    Next i
    For i = 1 To 6
        Range("N36:N41").Cells(i, 1) = "=R5C9"
    Next i
    Range("O5").FormulaR1C1 = "DY"
    Range("O6,O12,O18,O24,O30,O36") = "=R6C3"
    Range("O7,O13,O19,O25,O31,O37") = "=R9C3"
    Range("O8,O14,O20,O26,O32,O38") = "=R12C3"
    Range("O9,O15,O21,O27,O33,O39") = "=R15C3"
    Range("O10,O16,O22,O28,O34,O40") = "=R18C3"
    Range("O11,O17,O23,O29,O35,O41") = "=R21C3"
    Range("P5").FormulaR1C1 = "Birim Maliyet"
    Range("P6").FormulaR1C1 = "=R[1]C[-12]"
    Range("P7").FormulaR1C1 = "=R[3]C[-12]"
    Range("P8").FormulaR1C1 = "=R[5]C[-12]"
    Range("P9").FormulaR1C1 = "=R[7]C[-12]"
    Range("P10").FormulaR1C1 = "=R[9]C[-12]"
    Range("P11").FormulaR1C1 = "=R[11]C[-12]"
    Range("P12").FormulaR1C1 = "=R[-5]C[-11]"
    Range("P13").FormulaR1C1 = "=R[-3]C[-11]"
    Range("P14").FormulaR1C1 = "=R[-1]C[-11]"
    Range("P15").FormulaR1C1 = "=R[1]C[-11]"
    Range("P16").FormulaR1C1 = "=R[3]C[-11]"
    Range("P17").FormulaR1C1 = "=R[5]C[-11]"
    Range("P18").FormulaR1C1 = "=R[-11]C[-10]"
    Range("P19").FormulaR1C1 = "=R[-9]C[-10]"
    Range("P20").FormulaR1C1 = "=R[-7]C[-10]"
    Range("P21").FormulaR1C1 = "=R[-5]C[-10]"
    Range("P22").FormulaR1C1 = "=R[-3]C[-10]"
    Range("P23").FormulaR1C1 = "=R[-1]C[-10]"
    Range("P24").FormulaR1C1 = "=R[-17]C[-9]"
    Range("P25").FormulaR1C1 = "=R[-15]C[-9]"
    Range("P26").FormulaR1C1 = "=R[-13]C[-9]"
    Range("P27").FormulaR1C1 = "=R[-11]C[-9]"
    Range("P28").FormulaR1C1 = "=R[-9]C[-9]"
    Range("P29").FormulaR1C1 = "=R[-7]C[-9]"
    Range("P30").FormulaR1C1 = "=R[-23]C[-8]"
    Range("P31").FormulaR1C1 = "=R[-21]C[-8]"
    Range("P32").FormulaR1C1 = "=R[-19]C[-8]"
    Range("P33").FormulaR1C1 = "=R[-17]C[-8]"
    Range("P34").FormulaR1C1 = "=R[-15]C[-8]"
    Range("P35").FormulaR1C1 = "=R[-13]C[-8]"
    Range("P36").FormulaR1C1 = "=R[-29]C[-7]"
    Range("P37").FormulaR1C1 = "=R[-27]C[-7]"
    Range("P38").FormulaR1C1 = "=R[-25]C[-7]"
    Range("P39").FormulaR1C1 = "=R[-23]C[-7]"
    Range("P40").FormulaR1C1 = "=R[-21]C[-7]"
    Range("P41").FormulaR1C1 = "=R[-19]C[-7]"
    Range("Q4").FormulaR1C1 = "Fiyat Sıralamalı Veri Tabanı"
    Range("Q5").FormulaR1C1 = "ÜY"
    Range("R5").FormulaR1C1 = "DY"
    Range("S5").FormulaR1C1 = "Birim Maliyet"
    Range("T5").FormulaR1C1 = "Miktar"
    Range("V4").FormulaR1C1 = "ÜY"
    Range("V5").FormulaR1C1 = ""
    Range("W4").FormulaR1C1 = "DY"
    Range("W5").FormulaR1C1 = ""
    Range("X4").FormulaR1C1 = "ÜY Mikatar"
    Range("X5").FormulaR1C1 = "=IF(ISNA(HLOOKUP(RC[-2],R5C4:R28C9,24,FALSE))=TRUE,0,HLOOKUP(RC[-2],R5C4:R28C9,24,FALSE))"
    Range("Y4").FormulaR1C1 = "DY Miktar"
    Range("Y5").FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC[-2],R6C3:R23C12,10,FALSE))=TRUE,0,VLOOKUP(RC[-2],R6C3:R23C12,10,FALSE))"
    Range("Z4").FormulaR1C1 = "Minumum"
    Range("Z5").FormulaR1C1 = "=MIN(RC[-2]:RC[-1])"
    With Range("L4:L5,B2:L2,K4:K5,J4:J5,C6:C8,C9:C11,C12:C14,C15:C17,C18:C20,C21:C23,B24:C26,B4:C5,D4:I4,N4:P4,Q4:T4")
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = True
        .ReadingOrder = xlContext
        .MergeCells = True
        .Merge
    End With
    With Range("K6:K8,K9:K11,K12:K14,K15:K17,K18:K20,K21:K23,K24:K26,L6:L8,L9:L11,L12:L14,L15:L17,L18:L20,L21:L23,L24:L26")
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = True
        .ReadingOrder = xlContext
        .MergeCells = True
        .Merge
    End With
    With Range("B6:B23")
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Orientation = 90
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = True
        .ReadingOrder = xlContext
        .MergeCells = True
        .Merge
    End With
    With Range("D5:I5")
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = True
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Range("B4:L26")
        .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("B4:L5")
        .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:L26")
        .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
    End With
    With Range("J4:J26")
        .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("B24:L26")
        .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
    End With
    With Range("B27:J28")
        .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("B24:C28")
        .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
    End With
    With Range("J27:J28")
        .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
    End With
    With Range("N4:P41")
        .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("N4:P5")
        .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("Q4:T41")
        .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("Q4:T5")
        .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("V4:Z5")
        .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("V4:Z4")
        .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
    End With
    With Range("B4:C28")
        .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
    End With
    With Range("B2:L2").Font
        .Name = "Arial"
        .Size = 14
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
    With Range("D6:I6,D9:I9,D12:I12,D15:I15,D18:I18,D21:I21,K6:K23,D27:I27")
        .NumberFormat = "#,##0 ""kg"""
        With .Font
            .Name = "Arial"
            .FontStyle = "Normal"
            .Size = 9
            .Strikethrough = False
            .Superscript = False
            .Subscript = False
            .OutlineFont = False
            .Shadow = False
            .Underline = xlUnderlineStyleNone
            .ColorIndex = 32
        End With
        .Locked = False
        .FormulaHidden = False
    End With
    Range("L6:L26,K24:K26,D24:J24,D28:J28,J27,X5:Z5,T6:T41").NumberFormat = "#,##0 ""kg"""
    With Range("D7:I7,D10:I10,D13:I13,D16:I16,D19:I19,D22:I22")
        .NumberFormat = "#,##0.00 ""Tl/kg"""
        With .Font
            .Name = "Arial"
            .FontStyle = "Normal"
            .Size = 9
            .Strikethrough = False
            .Superscript = False
            .Subscript = False
            .OutlineFont = False
            .Shadow = False
            .Underline = xlUnderlineStyleNone
            .ColorIndex = 32
        End With
        .Locked = False
        .FormulaHidden = False
    End With
    Range("D25:J25,J7,J10,J13,J16,J19,J22,P6:P41,S6:S41").NumberFormat = "#,##0.00 ""Tl/kg"""
    Range("D8:J8,D11:J11,D14:J14,D17:J17,D20:J20,D23:J23,D26:J26").NumberFormat = "#,##0 ""TL"""
    Range("J25").Font.Bold = True
    With Range("D6:I6,D9:I9,D12:I12,D15:I15,D18:I18,D21:I21")
        .FormatConditions.Delete
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlNotEqual, Formula1:="0"
        With .FormatConditions(1).Font
            .Bold = True
            .Italic = False
        End With
        .FormatConditions(1).Interior.ColorIndex = 36
    End With
    With ActiveSheet.Buttons.Add(474.75, 324, 78, 19.5)
        .OnAction = "En_Iyi_Dagitim_Depolama_Maliyeti"
        .Name = "Düğme"
        .Characters.Text = "Hesapla"
        With .Characters(Start:=1, Length:=7).Font
            .Name = "Arial"
            .FontStyle = "Kalın"
            .ColorIndex = 32
            .Size = 9
            .Strikethrough = False
            .Superscript = False
            .Subscript = False
            .OutlineFont = False
            .Shadow = False
            .Underline = xlUnderlineStyleNone
            .ColorIndex = xlAutomatic
        End With
    End With
    With ActiveWindow
        .DisplayGridlines = False
        .DisplayHeadings = False
    End With
    Range("J25").Select
    ActiveSheet.Protect
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