- 帖子
- 254
- 主題
- 6
- 精華
- 0
- 積分
- 310
- 點名
- 0
- 作業系統
- W10
- 軟體版本
- Excel 2016
- 閱讀權限
- 20
- 性別
- 男
- 來自
- 台灣
- 註冊時間
- 2019-6-16
- 最後登錄
- 2024-9-23
|
19#
發表於 2020-6-28 19:36
| 只看該作者
請問准提大大 如果要設定幾輪上 夜班 日班 假設一輪是１２天
該如何寫呢？
準提大大的版本 排版更是清楚 但我應該在哪一段修改 要怎麼寫？
以下是之前修改過的 但對我來說已經是極限了．．實在想不出來 ＞＂＜
問題出在這裡：
如果用 ＂ｗｗ＂ 週數計算上班的天數就會有問題．．不知如何修改
＂ｄ＂天數會搭不到
Select Case DateAdd("d", -1, K) Mod Cells(1, 3) + 1 '常日班
Case 1 To Cells(1, 4)
G.Offset = "上班"
G.Offset.Font.Color = RGB(0, 0, 89)
G.Interior.Color = RGB(150, 201, 123)
Case Cells(1, 4) + 1 To Cells(1, 4) + Cells(1, 5)
If G.Offset >= Cells(1, 1) Then
G.Offset = "休假"
G.Offset.Font.Color = RGB(114, 0, 55)
G.Offset.Interior.Color = RGB(255, 255, 92)
End If
End Select
Select Case DateAdd("d", -1, K) Mod Cells(1, 7) + 1 '周輪班
Case 1 To Cells(1, 4)
G.Offset = "夜班"
G.Offset.Font.Color = RGB(114, 0, 55)
G.Offset.Interior.Color = RGB(255, 208, 0)
Case Cells(1, 4) + 1 To Cells(1, 4) + Cells(1, 5)
End Select
－－－－－－－－－－－－－－－－－－－－－－- Public Sub 周輪班練習()
- Range(Cells(2, 1).End(xlToRight), Cells(2, 1).End(xlDown)).Clear
- Cells(1, 3) = Cells(1, 4) + Cells(1, 5)
- Cells(1, 7) = Cells(1, 3) * 2
- If Cells(1, 2) = "" Then
- Cells(1, 2) = Year(Date)
- Else
- Cells(1, 2) = Cells(1, 2)
- End If
- S = 3
- E = 1
- For F = 1 To 12 '建立範圍
- For P = 1 To Day(DateSerial(Cells(1, 2), F + 1, 0))
- Cells(S, E) = DateSerial(Cells(1, 2), F, P)
- Cells(S - 1, E) = F & "月" & P & "日" & WeekdayName(Weekday(P))
- E = E + 1
- If P = Day(DateSerial(Cells(1, 2), F + 1, 0)) Then
- If F = 12 Then Exit For
- S = S + 2
- E = 1
- End If
- Next P
- Next F
-
- For E = ActiveWorkbook.Names.Count To 1 Step -1 '清除定義名稱
- If ActiveWorkbook.Names(E).Name <> "" Then
- ActiveWorkbook.Names(E).Delete
- End If
- Next E
-
- Y = 65
- For i = 3 To Cells(3, 1).End(xlDown).Row Step 2 '定義名稱
- 範圍名稱 = Chr(Y)
- Names.Add Name:="第" & 範圍名稱 & "項", RefersTo:=Range(Cells(i, 1), Cells(i, 1).End(xlToRight))
- Y = Y + 1
- Next i
-
- Set AWN = ActiveWorkbook.Names '合併
- For R = 1 To AWN.Count
- If R <> 1 Then
- K = Mid(AWN(R).RefersToR1C1Local, 2, Len(AWN(R))) & ","
- Else
- K = AWN(R).RefersToR1C1Local & ","
- End If
- U = U + K
- Next R
- Names.Add Name:="指定範圍", RefersTo:=Mid(U, 1, Len(U) - 1)
-
- For E = ActiveWorkbook.Names.Count To 1 Step -1 '清除定義名稱
- If ActiveWorkbook.Names(E).Name <> "指定範圍" Then
- ActiveWorkbook.Names(E).Delete
- End If
- Next E
-
- If Cells(1, 1) = "" Then
- Cells(1, 1) = Cells(3, 1)
- Else
- Cells(1, 1) = Cells(1, 1)
- End If
-
- D = Cells(1, 1)
-
- For Each G In Range("指定範圍") '周輪班
- If G.Offset >= Cells(1, 1) Then
- If Weekday(G) = 1 Or Weekday(G) = 7 Then '六日上色
- G.Offset(-1, 0).Interior.Color = RGB(172, 199, 213)
- End If
-
- K = G.Offset
-
- Select Case DateAdd("d", -1, K) Mod 6 + 1 '常日班
- Case 1 To 4
- G.Offset = "上班"
- G.Offset.Font.Color = RGB(0, 0, 89)
- G.Interior.Color = RGB(150, 201, 123)
- Case 5 To 6
- If G.Offset >= Cells(1, 1) Then
- G.Offset = "休假"
- G.Offset.Font.Color = RGB(114, 0, 55)
- G.Offset.Interior.Color = RGB(255, 255, 92)
- End If
- End Select
-
- Select Case DateAdd("d", -1, K) Mod 12 + 1 '周輪班
- Case 1 To 4
- G.Offset = "夜班"
- G.Offset.Font.Color = RGB(114, 0, 55)
- G.Offset.Interior.Color = RGB(255, 208, 0)
- Case 5 To 6
- End Select
-
- End If
- Next G
-
- End Sub
|
|
發表於 2020-6-28 11:24
| 
