- 帖子
- 34
- 主題
- 10
- 精華
- 0
- 積分
- 67
- 點名
- 0
- 作業系統
- windows 10
- 軟體版本
- office 2010
- 閱讀權限
- 20
- 註冊時間
- 2016-4-1
- 最後登錄
- 2021-5-5
|
回復 1# 馬納
我完成的第二版，程式碼如下，請指教！
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
For j = 0 To 23
Cells(9 + j, 19) = "01,02,03,04,05,06,07,08,09,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,"
For i = 0 To 11
'刪除輪休
If Cells(34, 4 + i) <> "" Then
Cells(9 + j, 19) = Replace(Cells(9 + j, 19), Cells(34, 4 + i) & ",", "")
End If
'刪除外宿
If Cells(35, 4 + i) <> "" Then
Cells(9 + j, 19) = Replace(Cells(9 + j, 19), Cells(35, 4 + i) & ",", "")
End If
'刪除差假
If Cells(34, 19 + i) <> "" Then
Cells(9 + j, 19) = Replace(Cells(9 + j, 19), Cells(34, 19 + i) & ",", "")
End If
'刪除休假
If Cells(35, 19 + i) <> "" Then
Cells(9 + j, 19) = Replace(Cells(9 + j, 19), Cells(35, 19 + i) & ",", "")
End If
Next i
'刪除已派
For i = 0 To 13
If Cells(9 + j, 5 + i) <> "" Then
Cells(9 + j, 19) = Replace(Cells(9 + j, 19), Cells(9 + j, 5 + i) & ",", "")
End If
Next i
For i = 0 To 1
If Cells(9 + j, 29 + i) <> "" Then
Cells(9 + j, 19) = Replace(Cells(9 + j, 19), Cells(9 + j, 29 + i) & ",", "")
End If
Next i
'設定職務派任清單
With Range(Cells(9 + j, 4), Cells(9 + j, 15)).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Cells(9 + j, 19)
End With
With Range(Cells(9 + j, 29), Cells(9 + j, 30)).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Cells(9 + j, 19)
End With
Next j
End Sub |
|
發表於 2016-4-8 17:35
| 
