|
- Sub 排班()
- '日期
- Dim i As Integer, j As Integer, k As Integer, Arr, k1 As Integer
- Arr = Range("k2:k" & Cells(65536, "k").End(xlUp).Row)
- If Cells(65536, "k").End(xlUp).Row = 1 Then MsgBox "无值班人员"
- For i = 2 To 18 Step 2
- For j = 2 To 8
- Cells(i, j) = Format(Cells(3, "l").Value + k, "yyyy-m-d")
- If Range("n:n").Find(Cells(i, j).Value, lookat:=xlWhole) Is Nothing Then
- If Cells(3, "m").Value = "单人" Then
- k1 = k1 + 1
- If k1 = UBound(Arr, 1) Then
- Cells(i, j).Offset(1, 0) = Arr(UBound(Arr, 1), 1)
- k1 = 0
- Else
- Cells(i, j).Offset(1, 0) = Arr(k1, 1)
- End If
- Else
- '双人
- If j <= 6 Then
- k1 = k1 + 1
- If k1 = UBound(Arr, 1) Then
- Cells(i, j).Offset(1, 0) = Arr(UBound(Arr, 1), 1)
- k1 = 0
- Else
- Cells(i, j).Offset(1, 0) = Arr(k1, 1)
- End If
- Else
- k1 = k1 + 1
- If k1 = UBound(Arr, 1) Then
- Cells(i, j).Offset(1, 0) = Arr(UBound(Arr, 1), 1) & "," & Arr(1, 1)
- k1 = 1
- Else
- If k1 + 1 = UBound(Arr, 1) Then
- Cells(i, j).Offset(1, 0) = Arr(k1, 1) & "," & Arr(k1 + 1, 1)
- k1 = 0
- Else
- Cells(i, j).Offset(1, 0) = Arr(k1, 1) & "," & Arr(k1 + 1, 1)
- k1 = k1 + 1
- End If
- End If
- End If
- End If
- Else
- Cells(i, j).Offset(1, 0) = ""
- End If
- k = k + 1
- Next j
- Next i
- End Sub
复制代码 以上,见附件
|
|