- 帖子
- 234
- 主題
- 19
- 精華
- 0
- 積分
- 276
- 點名
- 0
- 作業系統
- Windows XP
- 軟體版本
- office 2003
- 閱讀權限
- 20
- 性別
- 男
- 註冊時間
- 2013-1-7
- 最後登錄
- 2021-10-7
|
2#
發表於 2020-5-14 10:38
| 只看該作者
回復 1# 52ee24
不是很懂你的問題,只了解星期日不排程,每日總量不超過一定數量
試試看
Sub 排程()
Application.DisplayAlerts = False
Sheets("WIP").Select
r1 = Sheets("WIP").Range("a1").CurrentRegion.Rows.Count
For i = 5 To 2197 Step 8
Sheets("排程").Range(Sheets("排程").Cells(3, i), Sheets("排程").Cells(2200, i)).Resize(, 3).ClearContents '清除"批號" & "數量"
Next i
'這段沒資料可使用,先Disable,請自行Enable
'料號
'Sheets("WIP").Range("aj2").Formula = "=LEFT(G2,12)"
'工單代號
'Sheets("WIP").Range("ak2").Formula = "=LEFT(A2,2)"
'標工
'Sheets("WIP").Range("al2").Formula = "=VLOOKUP(D:D,製程站順序與表準工時!A:C,3,0)"
'日產能
'Sheets("WIP").Range("am2").Formula = "=VLOOKUP(D:D,製程站順序與表準工時!A:D,4,0)"
'需要天數
'Sheets("WIP").Range("an2").Formula = "=K2/AL2"
'停滯時數
'Sheets("WIP").Range("ao2").Formula = "=NOW()-W2"
'雷射結束時間
'Sheets("WIP").Range("ap2").Formula = "=VLOOKUP(A:A,工作站產出批號明細!B:R,17,0)"
'計畫完工日
'Sheets("WIP").Range("aq2").Formula = "=IFERROR(IF(AP2+CE2<AT2,AT2,AP2+CE2),"""")"
'離完工天數
'Sheets("WIP").Range("ar2").Formula = "=IFERROR(AQ2-TODAY(),"""")"
'製程剩餘天數
'Sheets("WIP").Range("as2").Formula = "=IFERROR(VLOOKUP(D:D,製程站順序與表準工時!A:E,5,0),"""")"
'建議交貨日
'Sheets("WIP").Range("at2").Formula = "=IF(AS2="""","""",TODAY()+AS2)"
'預計完工
'Sheets("WIP").Range("au2").Formula = "=IFERROR(IF(AQ2<>"""",AQ2-3,AT2),"""")"
'課別
'Sheets("WIP").Range("av2").Formula = "=VLOOKUP(D:D,RunCard!E:G,3,0)"
'製別
'Sheets("WIP").Range("aw2").Formula = "=VLOOKUP(D:D,RunCard!E:H,4,0)"
'製造別
'Sheets("WIP").Range("ax2").Formula = "=VLOOKUP(D:D,RunCard!E:K,7,0)"
'先一次
'Sheets("WIP").Range("aj2:ax2").AutoFill (Sheets("WIP").Range("aj2:ax" & r1))
'Sheets("WIP").Range("aj2:ax" & r1) = Sheets("WIP").Range("aj2:ax" & r1).Value
'第二段啦
'製成需要天數
'Sheets("WIP").Range("ay2").Formula = "=SUMIFS(RunCard!$I:$I,RunCard!$A:$A,WIP!$G2,RunCard!$K:$K,WIP!AY$1)"
'Sheets("WIP").Range("ay2").AutoFill (Sheets("WIP").Range("ay2:ay" & r1))
'Sheets("WIP").Range("ay2:ay" & r1).AutoFill (Sheets("WIP").Range("ay2:cd" & r1))
'Sheets("WIP").Range("ay2:cd" & r1) = Sheets("WIP").Range("ay2:cd" & r1).Value
'總天數
'Sheets("WIP").Range("ce2").Formula = "=SUM(AY2:cd2)"
'Sheets("WIP").Range("ce2").AutoFill (Sheets("WIP").Range("ce2:ce" & r1))
'第三段啦
'計算日期
'Sheets("WIP").Range("cf2").Formula = "=IF($AU2=0,"""",$AU2-SUM(AY2:$cd2))"
'Sheets("WIP").Range("cf2").AutoFill (Sheets("WIP").Range("cf2:cf" & r1))
'Sheets("WIP").Range("cf2:cf" & r1).AutoFill (Sheets("WIP").Range("cf2:dk" & r1))
'Sheets("WIP").Range("cf2:dk" & r1) = Sheets("WIP").Range("cf2:dk" & r1).Value
'第四段啦
'計算工作日
'Sheets("WIP").Range("cn2").Formula = "=IFERROR(ABS($AU2-BT2-NETWORKDAYS.INTL(BT2,$AU2,11)),"""")"
'Sheets("WIP").Range("cn2").AutoFill (Sheets("WIP").Range("cn2:cn" & r1))
'Sheets("WIP").Range("cn2:cn" & r1).AutoFill (Sheets("WIP").Range("cn2:dg" & r1))
'Sheets("WIP").Range("cn2:dg" & r1) = Sheets("WIP").Range("cn2:dg" & r1).Value
'第五段啦
'日期+工作日
'Sheets("WIP").Range("dl2").Formula = "=IF($AU2="""","""",IF((TEXT(cf2,""ddd"")=""Sun""),VALUE(TEXT(cf2+1,""yyyy/mm/dd"")),VALUE(TEXT(cf2,""yyyy/mm/dd""))))"
'Sheets("WIP").Range("dl2").AutoFill (Sheets("WIP").Range("dl2:dl" & r1))
'Sheets("WIP").Range("dl2:dl" & r1).AutoFill (Sheets("WIP").Range("dl2:eq" & r1))
'Sheets("WIP").Range("dl2:eq" & r1) = Sheets("WIP").Range("dl2:eq" & r1).Value
'預計完工(轉換)
'Sheets("WIP").Range("er2").Formula = "=IFERROR(VALUE(TEXT(AU2,""yyyy/mm/dd"")),"""")"
'Sheets("WIP").Range("er2").AutoFill (Sheets("WIP").Range("er2:er" & r1))
'Sheets("WIP").Range("er2:er" & r1) = Sheets("WIP").Range("er2:er" & r1).Value
'排程
Sheets("排程").Select
'01雷射清洗前
For j = 2 To r1
dl = Sheets("WIP").Range("dl" & j)
If dl <> "" Then
lot = Sheets("WIP").Range("a" & j)
Wip = Sheets("WIP").Range("k" & j) '增加數量以便統計
'找製程別
Dim c As Range
Set c = Sheets("排程").Range("b:b")
Fnd = c.Find(what:="01雷射清洗前", LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByColumns, searchdirection:=xlNext, MatchCase:=False, matchbyte:=False, searchformat:=False).Row
'找日期欄
Dim c1 As Range
Set c1 = Sheets("排程").Range("a1:cfm1")
fnd1 = c1.Find(what:=dl, LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByColumns, searchdirection:=xlNext, MatchCase:=False, matchbyte:=False, searchformat:=False).Column
'找日期列
Dim c2 As Range
Set c2 = Sheets("排程").Columns(fnd1)
fnd2 = c2.Find(what:="", after:=Cells(1, fnd1), LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByColumns, searchdirection:=xlNext, MatchCase:=False, matchbyte:=False, searchformat:=False).Row
plus = Cells(Fnd, fnd1 - 2)
If plus < 2000 Then '總量<2000排入行程
Cells(fnd2, fnd1) = lot
Cells(fnd2, fnd1).Offset(, 2) = Wip 'lot的數量
Else
For x = 6 To (Cells(2, fnd1).End(2).Column - fnd1) Step 8 '判斷其他日期的總量
If Cells(Fnd, fnd1).Offset(, x) < 2000 And Cells(Fnd, fnd1).Offset(-2, (x + 3)) <> "日" Then '排除"日",判斷其他工作日的總量<2000
Dim c3 As Range
Set c3 = Sheets("排程").Columns(fnd1 + (x + 2))
fnd3 = c3.Find(what:="", after:=Cells(1, fnd1 + (x + 2)), LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByColumns, searchdirection:=xlNext, MatchCase:=False, matchbyte:=False, searchformat:=False).Row
Cells(fnd3, fnd1).Offset(, (x + 2)) = lot
Cells(fnd3, fnd1).Offset(, (x + 4)) = Wip
Exit For
End If
Next
End If
End If
Next j
End Sub |
|
