Excel Forum

Code: Select all

Sub CreateShiftSchedule()
    Dim ws As Worksheet
    Dim employees As Variant
    Dim daysInMonth As Integer
    Dim dayIndex As Integer
    Dim employeeIndex As Integer
    Dim shiftPattern As Variant
    Dim employeeShifts() As String
    Dim totalEmployees As Integer
    Dim countPattern As Integer

    ' กำหนดชื่อตารางงาน
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    ' กำหนดพนักงาน
    employees = Array("Emp1", "Emp2", "Emp3", "Emp4", "Emp5", "Emp6", "Emp7", "Emp8")
    daysInMonth = 30 ' จำนวนวันในเดือน June
    totalEmployees = UBound(employees) + 1

    ' สร้าง pattern การทำงาน 4 วัน หยุด 2 วัน
    shiftPattern = Array("D", "D", "N", "N", "O", "O") ' D = Day shift, N = Night shift, O = Off
    countPattern = UBound(shiftPattern) + 1
    
    ' สร้างตารางการทำงาน
    ReDim employeeShifts(totalEmployees - 1, daysInMonth - 1)
    
    For employeeIndex = 0 To totalEmployees - 1
        For dayIndex = 0 To daysInMonth - 1
            employeeShifts(employeeIndex, dayIndex) = shiftPattern((dayIndex + (employeeIndex Mod countPattern)) Mod countPattern)
        Next dayIndex
    Next employeeIndex
    
    ' เขียนตารางการทำงานลงในชีท
    For employeeIndex = 0 To totalEmployees - 1
        ws.Cells(employeeIndex + 2, 1).Value = employees(employeeIndex)
        For dayIndex = 0 To daysInMonth - 1
            ws.Cells(1, dayIndex + 2).Value = dayIndex + 1
            ws.Cells(employeeIndex + 2, dayIndex + 2).Value = employeeShifts(employeeIndex, dayIndex)
        Next dayIndex
    Next employeeIndex
End Sub