Microsoft Office Excel ® Kod Kılavuzu

Microsoft Office Excel® Code Guide

20 Ekim 2003 Pazartesi

SaveAs Password & Password With InputBox

'Module1
Sub SaveAs_Password()
On Error GoTo 20
10
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Password:="0123456789"
ActiveWindow.Close
Application.DisplayAlerts = True
GoTo 10
20
Exit Sub
End Sub

'Module2

Dim Şifre, Deneme As Long
Sub ŞifreSorgusu()
On Error Resume Next
60 Şifre = InputBox("Lütfen şifreyi giriniz!" & vbCrLf & vbCrLf & "Mustafa ULUSARAÇ 01ulusarac@superonline.com", "[PBİD®] Şifre Sorgusu")
If Şifre = "0123456789" Then
Range("A1").Select
Exit Sub
Else
MsgBox "Yanlış şifre girdiniz!" & vbCrLf & vbCrLf & "Mustafa ULUSARAÇ 01ulusarac@superonline.com", vbInformation, "[PBİD®]Lütfen Dikkat..."
End If
Deneme = MsgBox("Yeniden denemek ister misiniz?" & vbCrLf & vbCrLf & "Mustafa ULUSARAÇ 01ulusarac@superonline.com", vbYesNo, "[PBİD®]Lütfen Dikkat...")
If Deneme = 6 Then
GoTo 60
Else
Exit Sub
End If
End Sub

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



1 Ekim 2003 Çarşamba

Timing Control




'UserForm1

'Add Tools on UserForm1: label1
Option Explicit
Dim Kalan As Double
Dim Bitiş As Date

Private Sub UserForm_Initialize()

On Error Resume Next
Me.Caption = "[PBİD®] Timing on UserForm Components..."
End Sub
Private Sub UserForm_Activate()

On Error Resume Next
Application.OnTime VBA.Now + VBA.TimeValue("00:00:30"), "ZamanlanmışKomut"
Bitiş = VBA.Now + VBA.TimeValue("00:00:30")
Do
Kalan = VBA.Round((Bitiş - VBA.Now) * 100000, 0)
Label1.Caption = Kalan & " saniye içinde form kapanacaktir!"
DoEvents
Loop
End Sub

'Module1

Option Explicit

Sub ZamanlanmışKomut()

On Error Resume Next
MsgBox "Be closed within 30 seconds." & vbCrLf & vbCrLf& "Mustafa ULUSARAÇ" & vbCrLf & "01ulusarac@superonline.com", vbInformation, "[PBİD®] Lütfen Dikkat!!!"
Unload UserForm1
End Sub


 

Blog Arşivi

Gadget

Bu içerik henüz şifreli bağlantı üzerinden kullanılamıyor.

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
Anadolu Üniversitesi Açık Öğretim Fakültesi