Private Sub CommandButton2_Click()
'3個月
If TextBox2.Value = 1 Then A = Array(3, 5, 7, 15, 17, 19)
If TextBox2.Value = 2 Then A = Array(5, 7, 9, 17, 19, 21)
If TextBox2.Value = 3 Then A = Array(7, 9, 11, 19, 21, 23)
If TextBox2.Value = 4 Then A = Array(9, 11, 13, 21, 23, 25)
If TextBox2.Value = 5 Then A = Array(11, 13, 15, 23, 25)
If TextBox2.Value = 6 Then A = Array(13, 15, 17, 25)
If TextBox2.Value = 7 Then A = Array(15, 17, 19)
If TextBox2.Value = 8 Then A = Array(17, 19, 21)
If TextBox2.Value = 9 Then A = Array(19, 21, 23)
If TextBox2.Value = 10 Then A = Array(21, 23, 25)
If TextBox2.Value = 11 Then A = Array(23, 25)
If TextBox2.Value = 12 Then A = Array(25)
If TextBox2.Value = "" Then A = Array(3, 5, 7, 15, 17, 19)
For I = LBound(A) To UBound(A)
For J = 4 To Cells(3, 4).End(xlToRight).Column
If Cells(A(I), J) = "日班" Then Cells(A(I), J) = "夜班"
If Cells(A(I), J) <> "" And Cells(A(I), J) = "夜班" Then
Cells(A(I), J).Font.Color = RGB(Cells(1, 26), Cells(1, 27), Cells(1, 28))
Cells(A(I), J).Interior.Color = RGB(Cells(1, 32), Cells(1, 33), Cells(1, 34))
End If
Next J
Next I
Erase A
UserForm3.Hide
TextBox2.Value = ""
End Sub
請問前輩們 以下這段該如何改寫 才能夠改成動態的規律 如何可以縮減程式碼
還有一個延伸問題 當延伸到最後一個月時不滿3個月 該如何延伸至下一年的第一個月
請前輩們指教 程式特別攏長 看起來可能會有些不方便 不好意思 ^^"
If TextBox2.Value = 1 Then A = Array(3, 5, 7, 15, 17, 19)
If TextBox2.Value = 2 Then A = Array(5, 7, 9, 17, 19, 21)
If TextBox2.Value = 3 Then A = Array(7, 9, 11, 19, 21, 23)
If TextBox2.Value = 4 Then A = Array(9, 11, 13, 21, 23, 25)
If TextBox2.Value = 5 Then A = Array(11, 13, 15, 23, 25)
If TextBox2.Value = 6 Then A = Array(13, 15, 17, 25)
If TextBox2.Value = 7 Then A = Array(15, 17, 19)
If TextBox2.Value = 8 Then A = Array(17, 19, 21)
If TextBox2.Value = 9 Then A = Array(19, 21, 23)
If TextBox2.Value = 10 Then A = Array(21, 23, 25)
If TextBox2.Value = 11 Then A = Array(23, 25)
If TextBox2.Value = 12 Then A = Array(25)
If TextBox2.Value = "" Then A = Array(3, 5, 7, 15, 17, 19)
 新增資料夾.rar (109.11 KB)
|
發表於 2020-6-15 13:42
| 
發表於 2020-6-16 11:52
| 