|
- Private Sub CommandButton1_Click()
- firlin = 3 '数据行的上一行(即标题的最后一行。标题总共有3行,1-3)
- statime = CDate("7:30")
- arrtmp = Range("c" & firlin - 1 & ":i" & Cells(Application.Rows.Count, "c").End(xlUp).Row)
- '开始时间为7:30
- Sec = 15 '时间间隔为15分钟
- Application.ScreenUpdating = False
- Sheet2.Select
- With Sheet2
- .Range("b4:iv67").ClearContents '清楚原排班表
- For i = 1 To UBound(arrtmp)
- staffname = arrtmp(i, 1)
- iCol = Application.Match(staffname, .Rows(2), 0)
- For t = 2 To 6 Step 2
- '对每一段班,进行排班
- If arrtmp(i, t) <> "" Then
- '每段时间的开始行和结束行
- irow1 = firlin + DateDiff("n", statime, arrtmp(i, t)) / 15 + 1
- irow2 = firlin + DateDiff("n", statime, arrtmp(i, t + 1)) / 15
- .Range(GetColumnLetter(iCol) & irow1 & ":" & GetColumnLetter(iCol) & irow2) = 20
- End If
- Next t
-
- Next i
- End With
- Application.ScreenUpdating = True
- Erase arrtmp
- MsgBox "OK"
- End Sub
复制代码- Function GetColumnLetter(iCol) As String '列号转列标
- GetColumnLetter = VBA.Split(Cells(1, iCol).Address, "$")(1)
- End Function
复制代码 |
|