O sayfaya ait kodları paylaşır mısınız.
Private Sub Worksheet_Activate()
ActiveSheet.Unprotect "1007"
Range("B6:L" & Rows.Count).ClearContents: Range("M6:AH" & Rows.Count).ClearContents
[B4:L4,P4:AH4].ClearContents: [N5] = ""
With Application: .ScreenUpdating = False: .Calculation = xlCalculationManual: End With
For Each Sh In ThisWorkbook.Sheets
If Sh.Name <> "Personel_Servis_Planı" Then
If Sh.AutoFilterMode Then Sh.AutoFilterMode = False
For Each XD In Sh.Range("L6:L" & Sh.Cells(Rows.Count, 12).End(3).Row)
If WorksheetFunction.CountIf([B:B], XD.Value) = 0 Then a = Cells(Rows.Count, 2).End(3).Row + 1: say = say + 1
If WorksheetFunction.CountIf([B:B], XD.Value) > 0 Then a = WorksheetFunction.Match(XD.Value, [B:B], 0)
Cells(a, 2) = XD.Value
If UCase(XD.Offset(0, -8)) = "X" Then '08:00 Güzergah Bazlı Personel Girişi
Cells(a, 4) = Cells(a, 4) + 1: Cells(4, 4) = Cells(4, 4) + 1
If UCase(XD.Offset(0, -9)) = "X" Then Cells(a, 3) = Cells(a, 3) + 1: Cells(4, 3) = Cells(4, 3) + 1 'Kreş Girişi Var
End If
If UCase(XD.Offset(0, -7)) = "X" Then '16:00 Güzergah Bazlı Personel Girişi
Cells(a, 5) = Cells(a, 5) + 1: Cells(4, 5) = Cells(4, 5) + 1
'If UCase(XD.Offset(0, -9)) = "X" Then Cells(a, 3) = Cells(a, 3) + 1: Cells(4, 3) = Cells(4, 3) + 1 'Kreş Girişi Yok
End If
If UCase(XD.Offset(0, -6)) = "X" Then '00:00 Güzergah Bazlı Personel Girişi
Cells(a, 6) = Cells(a, 6) + 1: Cells(4, 6) = Cells(4, 6) + 1
'If UCase(XD.Offset(0, -9)) = "X" Then Cells(a, 3) = Cells(a, 3) + 1: Cells(4, 3) = Cells(4, 3) + 1 'Kreş Girişi Yok
End If
'------------------------------------------------------------------------------------------
If UCase(XD.Offset(0, -5)) = "X" Then '16:00 Güzergah Bazlı Personel Çıkışı
Cells(a, 7) = Cells(a, 7) + 1: Cells(4, 7) = Cells(4, 7) + 1
'If UCase(XD.Offset(0, -9)) = "X" Then Cells(a, 3) = Cells(a, 3) + 1
End If
If UCase(XD.Offset(0, -4)) = "X" Then '18:00 Güzergah Bazlı Personel Çıkışı
Cells(a, 8) = Cells(a, 8) + 1: Cells(4, 8) = Cells(4, 8) + 1
'If UCase(XD.Offset(0, -9)) = "X" Then Cells(a, 3) = Cells(a, 3) + 1
End If
If UCase(XD.Offset(0, -3)) = "X" Then '20:00 Güzergah Bazlı Personel Çıkışı
Cells(a, 9) = Cells(a, 9) + 1: Cells(4, 9) = Cells(4, 9) + 1
'If UCase(XD.Offset(0, -9)) = "X" Then Cells(a, 3) = Cells(a, 3) + 1
End If
If UCase(XD.Offset(0, -2)) = "X" Then '00:00 Güzergah Bazlı Personel Çıkışı
Cells(a, 10) = Cells(a, 10) + 1: Cells(4, 10) = Cells(4, 10) + 1
'If UCase(XD.Offset(0, -9)) = "X" Then Cells(a, 3) = Cells(a, 3) + 1
End If
If UCase(XD.Offset(0, -1)) = "X" Then '08:00 Güzergah Bazlı Personel Çıkışı
Cells(a, 11) = Cells(a, 11) + 1: Cells(4, 11) = Cells(4, 11) + 1
'If UCase(XD.Offset(0, -9)) = "X" Then Cells(a, 3) = Cells(a, 3) + 1
End If
Next
End If
Next
son = Cells(Rows.Count, 2).End(3).Row
Range("L6:L" & son).Formula = "=
IF(
SUM(D6:F6)<>
SUM(G6:K6),""X"","""")"
Range("L6:L" & son).Value = Range("L6:L" & son).Value:
Range("B6:L" & son).Sort [B5], 1: [N5].Activate
With Application: .ScreenUpdating = True: .Calculation = xlCalculationAutomatic: End With
ActiveSheet.Protect "1007"
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, [N5]) Is Nothing Then Exit Sub
GUZERGAH_DURAKLAR
End Sub
Sub GUZERGAH_DURAKLAR()
ActiveSheet.Unprotect "1007"
Range("M6:AH" & Rows.Count).ClearContents: [P4:AH4].ClearContents: g = [N5].Value
If [N5] = "" Then Exit Sub
With Application: .ScreenUpdating = False: .Calculation = xlCalculationManual: End With
For Each Sh In ThisWorkbook.Sheets
If Sh.Name <> "Personel Servis Planı" And Sh.Cells(Rows.Count, 11).End(3).Row > 2 Then
For Each XD In Sh.Range("L6:L" & Sh.Cells(Rows.Count, 12).End(3).Row)
If XD.Value <> "" And XD.Value = g Then
If WorksheetFunction.CountIf([
N:
N], XD.Offset(0, 1).Value) = 0 Then: a = Cells(Rows.Count, 14).End(3).Row + 1: Cells(a, 13) = a - 5
If WorksheetFunction.CountIf([N:N], XD.Offset(0, 1).Value) > 0 Then a = WorksheetFunction.Match(XD.Offset(0, 1).Value, [N:N], 0)
Cells(a, 14) = XD.Offset(0, 1).Value
If UCase(XD.Offset(0, -8)) = "X" Then '08:00 Durak Bazlı Personel Girişi
Cells(a, 17) = Cells(a, 17) + 1: Cells(4, 17) = Cells(4, 17) + 1
If UCase(XD.Offset(0, -9)) = "X" Then Cells(a, 16) = Cells(a, 16) + 1: Cells(4, 16) = Cells(4, 16) + 1 '08:00 Kreş Girişi
End If
If UCase(XD.Offset(0, -7)) = "X" Then '16:00 Durak Bazlı Personel Girişi
Cells(a, 18) = Cells(a, 18) + 1: Cells(4, 18) = Cells(4, 18) + 1
'If UCase(XD.Offset(0, -9)) = "X" Then Cells(a, 16) = Cells(a, 16) + 1: Cells(4, 16) = Cells(4, 16) + 1 '16:00 Kreş Girişi Yok
End If
If UCase(XD.Offset(0, -6)) = "X" Then '00:00 Durak Bazlı Personel Girişi
Cells(a, 19) = Cells(a, 19) + 1: Cells(4, 19) = Cells(4, 19) + 1
'If UCase(XD.Offset(0, -9)) = "X" Then Cells(a, 16) = Cells(a, 16) + 1: Cells(4, 16) = Cells(4, 16) + 1 '00:00 Kreş Girişi Yok
End If
If UCase(XD.Offset(0, -5)) = "X" Then '16:00
Cells(a, 22) = Cells(a, 22) + 1: Cells(4, 22) = Cells(4, 22) + 1 '16:00 DURAK ÇIKIŞ ADETLERİ
'If UCase(XD.Offset(0, -9)) = "X" Then: Cells(a, 21) = Cells(a, 21) + 1: Cells(4, 21) = Cells(4, 21) + 1 '16:00 Kreş Çıkışı Yok
End If
If UCase(XD.Offset(0, -4)) = "X" Then '18:00
Cells(a, 25) = Cells(a, 25) + 1: Cells(4, 25) = Cells(4, 25) + 1 '18:00 DURAK ÇIKIŞ ADETLERİ
If UCase(XD.Offset(0, -9)) = "X" Then: Cells(a, 24) = Cells(a, 24) + 1: Cells(4, 24) = Cells(4, 24) + 1 '16:00 Kreş Çıkışı Var
End If
If UCase(XD.Offset(0, -3)) = "X" Then '20:00
Cells(a, 28) = Cells(a, 28) + 1: Cells(4, 28) = Cells(4, 28) + 1 '20:00 DURAK ÇIKIŞ ADETLERİ
'If UCase(XD.Offset(0, -9)) = "X" Then: Cells(a, 27) = Cells(a, 27) + 1: Cells(4, 27) = Cells(4, 27) + 1 '20:00 Kreş Çıkışı Yok
End If
If UCase(XD.Offset(0, -2)) = "X" Then '00:00
Cells(a, 31) = Cells(a, 31) + 1: Cells(4, 31) = Cells(4, 31) + 1 '00:00 DURAK ÇIKIŞ ADETLERİ
'If UCase(XD.Offset(0, -9)) = "X" Then: Cells(a, 30) = Cells(a, 30) + 1: Cells(4, 30) = Cells(4, 30) + 1 '00:00 Kreş Çıkışı Yok
End If
If UCase(XD.Offset(0, -1)) = "X" Then '08:00
Cells(a, 34) = Cells(a, 34) + 1: Cells(4, 34) = Cells(4, 34) + 1 '08:00 DURAK ÇIKIŞ ADETLERİ
'If UCase(XD.Offset(0, -9)) = "X" Then: Cells(a, 33) = Cells(a, 33) + 1: Cells(4, 33) = Cells(4, 33) + 1 '08:00 Kreş Çıkışı Yok
End If
End If
Next
End If
Next
With Application: .ScreenUpdating = True: .Calculation = xlCalculationAutomatic: End With
ActiveSheet.Protect "1007"
End Sub