Microsoft Office Excel ® Kod Kılavuzu

Microsoft Office Excel® Code Guide

20 Aralık 2003 Cumartesi

Data Base Control on UserForm



'UserForm1

Option Explicit
Dim Bakılan As Range
Dim No As Double
Dim VeriTabanı, SatırSayısı, SonSatır

Private Sub UserForm_Initialize()
On Error Resume Next
Me.Caption = "[PBİD®] Data Base Control on UserForm..."
End Sub
Private Sub CommandButton1_Click()
On Error Resume Next
Set VeriTabanı = Application.Cells(1, 1).CurrentRegion: Label1.Caption = " " & VeriTabanı.Address
SatırSayısı = VeriTabanı.Rows.Count: Label2.Caption = " " & SatırSayısı
SonSatır = SatırSayısı + 1: Label3.Caption = " " & SonSatır
Call DoluBoşKayıtBilgisi
Cells(SonSatır, 1).Select
End Sub
Private Sub DoluBoşKayıtBilgisi()
On Error Resume Next
No = 0
For Each Bakılan In VeriTabanı
If IsEmpty(Bakılan) Then
No = No + 1
ComboBox1.AddItem Bakılan.Address
End If
If Not IsEmpty(Bakılan) Then
'No = No + 1
'ComboBox1.AddItem Bakılan.Address
End If
Next Bakılan
Label4.Caption = " " & No
End Sub

10 Aralık 2003 Çarşamba

Sheet PopUp [Page Select] Menu



'Module1

Option Explicit
Dim CB As CommandBar, i As Integer
Dim CBB As CommandBarButton

Sub Auto_Open()
On Error Resume Next
PopUpMenu
End Sub
Sub PopUpMenu()
On Error Resume Next
Set CB = Application.CommandBars("Cell")
For i = CB.Controls.Count To 0 Step -1
CB.Controls(i).Delete
Next i
With CB
For i = 1 To Sheets.Count
With .Controls.Add(Type:=msoControlButton)
.OnAction = "SayfaGoster"
.FaceId = 230
.Caption = Sheets(i).Name
End With
Next i
End With
Set CB = Nothing
End Sub
Sub SayfaGoster()
On Error Resume Next
Set CBB = Application.CommandBars.ActionControl
Sheets(CBB.Caption).Select
Set CBB = Nothing
End Sub
Sub Auto_Close()
On Error Resume Next
Application.CommandBars("Cell").Reset
End Sub

1 Aralık 2003 Pazartesi

Multi Line Select on Sheet



'Module1

Option Explicit
Dim i As Integer
Dim İlk, Son As Double
Dim Satır As Variant

Sub ÇokluSatırSeç()
On Error Resume Next
İlk = InputBox("İlk Satır No:", "[PBİD®]Çoklu Satır Seçimi")
Son = InputBox("Son Satır No:", "[PBİD®]Çoklu Satır Seçimi")
Satır = ""
For i = İlk To Son Step 2
Satır = Satır + Trim(Str(i)) + ":" + Trim(Str(i)) + ","
Next i
Satır = Left(Satır, Len(Satır) - 1)
Range(Satır).Select
End Sub

20 Kasım 2003 Perşembe

UserForm Create With Module Code Editors



'Module1

Option Explicit
Dim Ekran
Dim i, ii, Adet, ÜstPoz, SolPoz As Integer
Dim NewF1 As MSForms.Frame
Dim NewF2 As MSForms.Frame
Dim NewL1 As MSForms.Label
Dim NewOB1 As MSForms.OptionButton
Dim NewCB1 As MSForms.CommandButton
Dim NewCB2 As MSForms.CommandButton
Public UserFormÖrneği, Tercih As Variant

Sub UserFormYap()
On Error Resume Next
Adet = Range("İlAdları").Count
ReDim Hafıza(1 To Adet)
For i = 1 To Adet
Hafıza(i) = Range("İlAdları").Cells(i, 1) 'İlAdları VeriTabanı sayfasının C2:C100 alanı olarak tanımlanmıştır.
Next i
Tercih = UserFormYapıcı(Hafıza, "[PBİD®] UserForm Create With Module Code Editors...")
If Tercih = True Then
[F2] = Hafıza(Tercih)
Else
[F2] = ""
End If
End Sub
Function UserFormYapıcı(Bilgi, Başlık)
SolPoz = 6: ÜstPoz = 6: i = 0: ii = 0: Adet = 0
Application.VBE.MainWindow.Visible = False
Set Ekran = ThisWorkbook.VBProject.VBComponents.Add(3)
'0= vbext_ct_ActiveXDesigner, 1= vbext_ct_ClassModule, 2= vbext_ct_Document, 3= vbext_ct_MSForm, 4= vbext_ct_StdModule
With Ekran
.Properties("Caption") = Başlık
.Properties("Width") = 240
.Properties("BackColor") = &H80000016
Set NewF1 = .Designer.Controls.Add("Forms.Frame.1")
With NewF1
.Caption = "İl Veri Tabanı"
For i = LBound(Bilgi) To UBound(Bilgi)
Set NewOB1 = .Controls.Add("Forms.OptionButton.1")
With NewOB1
.Caption = Bilgi(i)
.Width = 72
.Height = 15
.Left = 6
.Top = ÜstPoz
.Tag = i
.AutoSize = False
End With
ÜstPoz = ÜstPoz + 14
Next i
.ForeColor = vbBlue
.Top = 6
.Left = 6
.Height = 70
.Width = 240 - (6 + 6 + 6)
.ScrollBars = 2
.ScrollHeight = ((i * 14) - 6)
ÜstPoz = .Top + .Height
End With
Set NewCB1 = .Designer.Controls.Add("forms.CommandButton.1")
With NewCB1
.Caption = "Vazgeç"
.Height = 18
.Width = 72
.Left = 6
.Top = ÜstPoz + 6
SolPoz = .Left + .Width + 6
End With
Set NewCB2 = .Designer.Controls.Add("forms.CommandButton.1")
With NewCB2
.Caption = "Tamam"
.Height = 18
.Width = 72
.Left = SolPoz
.Top = ÜstPoz + 6
ÜstPoz = .Top + .Height
End With
Set NewF2 = .Designer.Controls.Add("Forms.Frame.1")
With NewF2
.Caption = ""
.Top = ÜstPoz + 6
.Left = 6
.Width = .Width + 6
.Height = 2
.SpecialEffect = fmSpecialEffectEtched
ÜstPoz = .Top + 6 + 2
End With
Set NewL1 = Ekran.Designer.Controls.Add("forms.Label.1")
With NewL1
.Caption = "Mustafa ULUSARAÇ 01ulusarac@superonline.com"
.Height = 24
.Width = 240 - (6 + 6)
.Left = 6
.Top = ÜstPoz
.ForeColor = vbBlue
.TextAlign = fmTextAlignCenter
ÜstPoz = .Top + 24 + 24
End With
.Properties("Height") = ÜstPoz
With .CodeModule
ii = .CountOfLines
.InsertLines ii + 1, "Option Explicit"
.InsertLines ii + 2, "Dim i, Adet As Integer"
.InsertLines ii + 3, "Dim Ctl as Control"
.InsertLines ii + 4, "Private Sub UserForm_Initialize()"
.InsertLines ii + 5, " On Error Resume Next"
.InsertLines ii + 6, " Adet = Range(""İlAdları"").Count"
.InsertLines ii + 7, " For i = 1 To Adet"
.InsertLines ii + 8, " if [F2] = me(""OptionButton"" & i ).Caption Then me(""OptionButton"" & i ).Value=True"
.InsertLines ii + 9, " Next i"
.InsertLines ii + 10, "End Sub"
.InsertLines ii + 11, "Sub CommandButton1_Click()"
.InsertLines ii + 12, " UserFormÖrneği=False"
.InsertLines ii + 13, " Unload Me"
.InsertLines ii + 14, "End Sub"
.InsertLines ii + 15, "Sub CommandButton2_Click()"
.InsertLines ii + 16, " Dim ctl"
.InsertLines ii + 17, " UserFormÖrneği = False"
.InsertLines ii + 18, " For Each Ctl In Me.Controls"
.InsertLines ii + 19, " If (Ctl.Tag <> """") Then If Ctl Then UserFormÖrneği = Ctl.Tag"
.InsertLines ii + 20, " Next Ctl"
.InsertLines ii + 21, " Unload Me"
.InsertLines ii + 22, "End Sub"
End With
End With
VBA.UserForms.Add(Ekran.Name).Show
ThisWorkbook.VBProject.VBComponents.Remove VBComponent:=Ekran
UserFormYapıcı = UserFormÖrneği
End Function

10 Kasım 2003 Pazartesi

UserForm QueryClose True

'UserForm1
Private Sub CommandButton1_Click()
On Error Resume Next
Unload Me
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
On Error Resume Next
If CloseMode = vbFormControlMenu Then Cancel = True
End Sub

1 Kasım 2003 Cumartesi

UserForm Object Controls



'UserForm1

Option Explicit
Dim Ctr As Control
Dim Say As Integer
Dim Mesaj As String

Private Sub UserForm_Initialize()
On Error Resume Next
Me.Caption = "[PBİD®] UserForm Object Controls..."
Label1.Caption = "": Label2.Caption = ""
End Sub
Private Sub CommandButton1_Click()
On Error Resume Next
Say = 0: Mesaj = ""
For Each Ctr In Me.Controls
If VBA.TypeName(Ctr) = "CheckBox" Then
If Ctr.Value Then
Say = Say + 1
Mesaj = Mesaj & Ctr.Name & vbCr
End If
End If
Next Ctr
Label1.Caption = Say
Label2.Caption = "adet CheckBox işaretli."
End Sub

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


 

10 Eylül 2003 Çarşamba

Private PopUp Menus On The Page 04




'Module1

Option Explicit
Dim i As Single, ii As Single, No As Single, Adet As Double
Type HücreBilgisi

HücreVerisi As Variant
HücreAdresi As String

End Type
Public WB As Workbook, WS As Worksheet, Hücre() As HücreBilgisi, Alan As Range
Dim Durum As Boolean
Declare Function ShowCursor Lib "user32" (ByVal bShow As Long) As Long
Private Declare Function BlockInput Lib "user32" (ByVal fBlock As Long) As Long
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Declare Function GetDeviceCaps Lib "gdi32" (ByVal hdc As Long, ByVal nIndex As Long) As Long
Declare Function GetDC Lib "user32" (ByVal hwnd As Long) As Long
Declare Function ReleaseDC Lib "user32" (ByVal hwnd As Long, ByVal hdc As Long) As Long
Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Private Declare Function SetWindowPos Lib "user32" (ByVal hwnd As Long, ByVal hWndInsertAfter As Long, ByVal x As Long, ByVal y As Long, ByVal cx As Long, ByVal cy As Long, ByVal wFlags As Long) As Long
Const Gizle = &H80
Const Goster = &H40
Private Declare Sub keybd_event Lib "user32" (ByVal bVk As Byte, ByVal bScan As Byte, ByVal dwFlags As Long, ByVal dwExtraInfo As Long)
Const VK_CONTROL = &H11
Const KEYEVENTF_KEYUP = &H2
Const VK_ESCAPE = &H1B
Sub Auto_Open()

On Error Resume Next
Application.DisplayAlerts = False
Application.EnableCancelKey = xlDisabled
Application.CommandBars("Cell").Reset
With ShortcutMenus(xlWorksheetCell)
.MenuItems.AddMenu "Sayfa Özel Komutları [Page Special Commands]", 1
With .MenuItems("Sayfa Özel Komutları [Page Special Commands]")
.MenuItems.Add "Geri Almaya Duyarlı İşlem [Sensitive Transaction Rollback]", OnAction:="GeriAlmayaDuyarlıİşlem"
.MenuItems.Add "İşlemiGeriAl [Undo operation]", OnAction:="İşlemiGeriAl"
.MenuItems.Add "Formül Sildirmemek [Delete the formula of order]", OnAction:="FormülSildirmemek"
.MenuItems.Add "Formül Sildirmek [Formula to Delete]", OnAction:="FormülSildirmek"
.MenuItems.Add "Formül Kopyala [Copy Formula]", OnAction:="FormülKopyala"
End With
.MenuItems.AddMenu "Diğer Özel Komutlar [Other Special Commands]", 2
With .MenuItems("Diğer Özel Komutlar [Other Special Commands]")
.MenuItems.Add "Fare Gizle [Mouse Hide]", OnAction:="FareGizle"
.MenuItems.Add "Fare Göster [Mouse UnHide]", OnAction:="FareGöster"
.MenuItems.Add "Klavye Ve Fareyi Şarta Bağlı Olarak Kilitle [BlockInput KeeyBoard and Mouse]", OnAction:="KlavyeVeFareyiŞartaBağlıOlarakKilitle"
.MenuItems.Add "Windows Gezginini Çağır [Call Explorer]", OnAction:="WindowsGezgininiÇağır"
.MenuItems.Add "Tüm CBB Düğmelerinin Resimleri [ListAllFaces]", OnAction:="TümCBBDüğmelerininResimleri"
.MenuItems.Add "Desktop Kısa Yol [DeskTop ShortCut]", OnAction:="DesktopKısaYol"
.MenuItems.Add "Ekran Görüntü Yoğunluğu [Screen Device Pixels]", OnAction:="EkranGörüntüYoğunluğu"
.MenuItems.Add "Başlat Bar Gizlensin [Start Bar Hidden]", OnAction:="BaşlatBarGizlensin"
.MenuItems.Add "Başlat Bar Görünsün [Start Bar UnHidden]", OnAction:="BaşlatBarGörünsün"
.MenuItems.Add "BaşlatAç [Open Start Bar]", OnAction:="BaşlatAç"
End With
End With
End Sub
Sub Auto_Close()

On Error Resume Next
Application.CommandBars("Cell").Reset
End Sub
Sub FormAç()

On Error Resume Next
Load UserForm1
End Sub

'SAYFA ÖZEL MAKROLARI


Sub GeriAlmayaDuyarlıİşlem()
'Sensitive Transaction Rollback

On Error Resume Next
If VBA.TypeName(Application.Selection) = "Range" Then
Application.ScreenUpdating = False
ReDim Hücre(Application.Selection.Count)
Set WB = Application.ActiveWorkbook
Set WS = Application.ActiveSheet
i = 1
For Each Alan In Application.Selection 'işlemden önce hücre yapısı geri lama hafızasına kaydadilir
Hücre(i).HücreVerisi = Alan.Formula
Hücre(i).HücreAdresi = Alan.Address
i = i + 1
Next Alan
Application.Selection.Formula = "X"
Application.OnUndo "İşlemi Geri Almak", "İşlemiGeriAl"
Else
Exit Sub
End If
Application.ScreenUpdating = True
End Sub
Sub İşlemiGeriAl()
'Undo operation

Application.ScreenUpdating = False
On Error GoTo Hata
WB.Activate
WS.Activate
On Error GoTo 0
For i = 1 To UBound(Hücre)
Range(Hücre(i).HücreAdresi).Formula = Hücre(i).HücreVerisi
Next i
Set WB = Nothing
Set WS = Nothing
Erase Hücre
Application.ScreenUpdating = True
Hata:
End Sub
Sub FormülSildirmemek() 'Delete the formula of order
On Error Resume Next
Durum = True
Call FormülSilmeKontrolü
End Sub
Sub FormülSildirmek()
'Formula to Delete

On Error Resume Next
Durum = False
Call FormülSilmeKontrolü
End Sub
Sub FormülSilmeKontrolü
()
On Error Resume Next
If Durum = False Then Application.OnKey "{Del}": End
If Application.ActiveCell.HasFormula Then
Application.OnKey "{Del}", "FormülSildirmemeMesajı"
Else
Application.OnKey "{Del}"
End If
Application.OnTime VBA.Now + VBA.TimeValue("00:00:1"), "FormülSilmeKontrolü"
End Sub
Sub FormülSildirmemeMesajı()

On Error Resume Next
MsgBox " Formül silememe makrosu aktif durumdadır!" & vbCrLf & vbCrLf & "Mustafa ULUSARAÇ 01ulusarac@superonline.com", vbExclamation, "[PBİD®] Formül Silme İşlemi İptali..."
End Sub
Sub FormülKopyala()
'Copy Formula

On Error Resume Next
Dim Formül(100)
HedefSatır As Double
Formül(1) = "=VLOOKUP(R[-1]C[-2],R1C3:R9C3,1,FALSE)"
Application.ActiveCell.Formula = Formül(1)
HedefSatır = Cells(65536, 3).End(xlUp).Row
Application.ActiveCell.AutoFill Destination:=Range("E2:E" & HedefSatır)
Application.Calculate
End Sub

'DİĞER ÖZEL MAKROLAR


Sub FareGizle()
'Mouse Hide


On Error Resume Next
Application.OnTime Now + TimeValue("00:00:05"), "FareGöster"
ShowCursor False
End Sub
Sub FareGöster()
'Mouse UnHide

On Error Resume Next
ShowCursor True
End Sub
Sub KlavyeVeFareyiŞartaBağlıOlarakKilitle()
'BlockInput KeeyBoard and Mouse

On Error Resume Next
DoEvents
BlockInput True
Call KilitŞartıİşlem
BlockInput False
End Sub
Sub KilitŞartıİşlem()

On Error Resume Next
Sleep 5000 '(5 saniye)
End Sub
Sub WindowsGezgininiÇağır()
'Call Explorer

On Error Resume Next
Shell "C:\WINDOWS\EXPLORER.EXE /n,/e,c:\", vbMaximizedFocus
'Shell (VBA.Environ("SystemRoot") & "\Explorer.exe"), 1
End Sub
Sub TümCBBDüğmelerininResimleri()
'ListAllFaces

Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim cbCtl As CommandBarControl
Dim cbBar As CommandBar
On Error Resume Next
Application.Worksheets.Add
Set cbBar = CommandBars.Add(Position:=msoBarFloating, MenuBar:=False, temporary:=True)
Set cbCtl = cbBar.Controls.Add(Type:=msoControlButton, temporary:=True)
k = 1
Do While Err.Number = 0
For j = 1 To 10
i = i + 1
Application.StatusBar = "Face ID = " & i
cbCtl.FaceId = i
cbCtl.CopyFace
If Err.Number <> 0 Then Exit For
ActiveSheet.Paste Cells(k, j + 1)
Cells(k, j).Value = i
Next
k = k + 1
Loop
Application.StatusBar = False
cbBar.Delete
End Sub
Sub DesktopKısaYol()
'DeskTop ShortCut

On Error Resume Next
Dim KısaYol, Yol, Bağlantı
Set KısaYol = VBA.CreateObject("WScript.Shell")
Yol = KısaYol.SpecialFolders("Desktop")
Set Bağlantı = KısaYol.CreateShortcut(Yol & "\" & ActiveWorkbook.Name & ".lnk")
With Bağlantı
.TargetPath = ActiveWorkbook.FullName
.Save
End With
Set KısaYol = Nothing
End Sub
Sub EkranGörüntüYoğunluğu()
'Screen Pixels

On Error Resume Next
Dim Pix As Long
Pix = GetDC(0)
MsgBox "Görüntü Yoğunluğu : " & GetDeviceCaps(Pix, 8) & " * " & GetDeviceCaps(Pix, 10) & " pixels" & vbCrLf & vbCrLf & "Mustafa ULUSARAÇ 01ulusarac@superonline.com", vbExclamation, "[PBİD®] Ekran Görüntü Yoğunluğu..."
ReleaseDC 0, Pix
End Sub
Sub BaşlatBarGizlensin()
'Start Bar Hidden

On Error Resume Next
Dim hWnd1 As Long
hWnd1 = FindWindow("Shell_traywnd", "")
Call SetWindowPos(hWnd1, 0, 0, 0, 0, 0, Gizle)
End Sub
Sub BaşlatBarGörünsün()
'Start Bar UnHidden

On Error Resume Next
Dim hWnd1 As Long
hWnd1 = FindWindow("Shell_traywnd", "")
Call SetWindowPos(hWnd1, 0, 0, 0, 0, 0, Goster)
End Sub
Sub BaşlatAç()
'Open Start Bar

On Error Resume Next
Call keybd_event(VK_CONTROL, 0, 0, 0)
Call keybd_event(VK_ESCAPE, 0, 0, 0)
Call keybd_event(VK_ESCAPE, 0, KEYEVENTF_KEYUP, 0)
Call keybd_event(VK_CONTROL, 0, KEYEVENTF_KEYUP, 0)
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