|
|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
撇開VBA不看~~這是我見過僅僅用公式寫出來最好版本的排程了~~和我現在用的自動化版本幾乎一樣
只差不用產品換線時候的換線時間了~
下邊是我現在用的~供參考~~Sheets("RATE")是我單獨建立的一個工作表
把標準工時...資料錄入在裡邊~~排程頁面就單獨看每天要做多少
~~有人說比較直觀~~也有人說核對標準的時候麻煩
Sub reSchedule()
'bottom Row
rwBottom = Cells(rwHideBottomRow, clBottomRow) - 1
'clear old schedule
'daily production Qty
Rows(rwBottom + 1).ClearContents
'daily production Qty record
Range(Columns(clNote + 1), Columns(clNote + 40)).ClearContents
'capacity data
Rows(rwHideCapacity).ClearContents
Range(Cells(7, 11), Cells(77, 61)).ClearContents
Set rateSheet = Sheets("RATE")
model = ""
currentDay = clStartDay
capacityOccupied# = Cells(rwHideCapacity, currentDay)
For rwMo = rwMoStart To rwBottom
'Qty
openQty = Cells(rwMo, clMoQty)
If Cells(rwMo, clLot) > 0 Then openQty = Cells(rwMo, clLot)
If Cells(rwMo, clInputQty) > 0 Then openQty = openQty - Cells(rwMo, clInputQty)
'Rate
If Cells(rwMo, clModel) <> "" And Cells(rwMo, clModel) <> model Then
model = Cells(rwMo, clModel).Value
End If
shift = ActiveSheet.name
Set modelNames = rateSheet.Columns(1)
Set shiftNames = rateSheet.Rows(1)
rw = Application.Match(model, modelNames, 0)
cl = Application.Match(shift, shiftNames, 0)
DailyRate = rateSheet.Cells(rw, cl)
If DailyRate = 0 Then
ss = "rw " + model + " ? " + shift + " cl RATE !"
ans = MsgBox(ss, 16)
Exit Sub
End If
'production Qty record
clPQ = clNote
'check special input
For i = clStartDay To clEndDay
If Application.IsNumber(Cells(rwMo, i)) Then
Select Case Cells(rwMo, i).Font.ColorIndex
Case Is = 5:
ocp# = Cells(rwMo, i) / DailyRate
Cells(rwHideCapacity, i) = Cells(rwHideCapacity, i) + ocp#
Cells(rwBottom + 1, i) = Cells(rwBottom + 1, i) + Cells(rwMo, i)
Cells(rwMo, clPQ + 1) = i
Cells(rwMo, clPQ + 2) = Cells(rwMo, i)
clPQ = clPQ + 2
capacityOccupied# = Cells(rwHideCapacity, currentDay)
Case Is = 3:
ocp# = Cells(rwMo, i) / DailyRate
Cells(rwHideCapacity, i) = Cells(rwHideCapacity, i) + ocp#
Cells(rwBottom + 1, i) = Cells(rwBottom + 1, i) + Cells(rwMo, i)
Cells(rwMo, clPQ + 1) = i
Cells(rwMo, clPQ + 2) = Cells(rwMo, i)
clPQ = clPQ + 2
Case Else:
Cells(rwMo, i) = ""
End Select
Else
' If Cells(rwMo, i) = " ◆" Then
' Cells(rwMo, i) = ""
' End If
End If
Next i
If clPQ > clNote Then GoTo nextMo
If openQty = 0 Then GoTo nextMo
'Capacity
Do
Select Case Cells(rwDailyNote, currentDay)
Case Is = "0": maxCapacity = 0
Case Is = "8": maxCapacity = 0.8
Case Is = "10": maxCapacity = 1
Case Is = "11": maxCapacity = 1.1
Case Is = "12": maxCapacity = 1.2
Case Is = "16": maxCapacity = 1.6
Case Is = "18": maxCapacity = 1.8
Case Is = "20": maxCapacity = 2
Case Else: maxCapacity = 1
End Select
allocateQty = Int(DailyRate * (maxCapacity - capacityOccupied#))
If allocateQty > openQty Then
allocateQty = openQty
openQty = 0
nextDay = 0
capacityOccupied# = capacityOccupied# + CDbl(allocateQty / DailyRate)
Cells(rwHideCapacity, currentDay) = capacityOccupied#
Else
openQty = openQty - allocateQty
nextDay = 1
Cells(rwHideCapacity, currentDay) = maxCapacity 'capacity full occupied
capacityOccupied# = Cells(rwHideCapacity, currentDay + 1)
End If
'production Qty
With Cells(rwMo, currentDay)
.Value = allocateQty
.Font.ColorIndex = 10
.Interior.ColorIndex = colorID
End With
'dialy production Qty
Cells(rwBottom + 1, currentDay) = Cells(rwBottom + 1, currentDay) + allocateQty
'production Qty recorded
Cells(rwMo, clPQ + 1) = currentDay
Cells(rwMo, clPQ + 2) = allocateQty
clPQ = clPQ + 2
currentDay = currentDay + nextDay
Loop Until openQty = 0
nextMo:
Next rwMo
'LOTS MO DEALING
' For rwMo = rwMoStart To rwBottom
' If Cells(rwMo, clLot) > 0 And _
' Cells(rwMo, clInputQty) < Cells(rwMo, clLot) Then
' mo = Cells(rwMo, clMoNo)
' Set monos = Columns(clMoNo)
' rw = Application.Match(mo, monos, 0)
' If rw < rwMo Then
' For i = clStartDay To clEndDay
' If Application.IsNumber(Cells(rwMo, i)) Then
' Cells(rw, i) = Cells(rw, i) + Cells(rwMo, i)
' Cells(rwMo, i) = " ◆"
' End If
' Next i
' End If
' End If
' Next rwMo
'check if capaicty over-loading
For d = clStartDay To clEndDay
Select Case Cells(rwDailyNote, d)
Case Is = "0": maxCapacity = 0
Case Is = "8": maxCapacity = 0.8
Case Is = "10": maxCapacity = 1
Case Is = "11": maxCapacity = 1.1
Case Is = "12": maxCapacity = 1.2
Case Is = "16": maxCapacity = 1.6
Case Is = "18": maxCapacity = 1.8
Case Is = "20": maxCapacity = 1.8
Case Else: maxCapacity = 1
End Select
Set cell1 = Cells(rwDailyNote, d)
Set cell2 = Cells(rwBottom, d)
Set theDay = Range(cell1, cell2)
Select Case Cells(rwHideCapacity, d)
Case Is > maxCapacity: theDay.Interior.ColorIndex = 0
Case Is < maxCapacity: theDay.Interior.ColorIndex = 0
Case Else: theDay.Interior.ColorIndex = 0
End Select
Next d
'un-Confirm mo
'For rwMo = rwMoStart To rwBottom
' qid = clNote + 1
' Do Until IsEmpty(Cells(rwMo, qid))
' cl = CLng(Cells(rwMo, qid))
' If Not IsEmpty(Cells(rwMo, clUnConfirm)) Then
' Cells(rwMo, cl).Interior.ColorIndex = 35
' End If
' Cells(rwMo, qid) = Cells(rwDay, cl)
' qid = qid + 2
' Loop
'Next rwMo
End Sub
|
|
楼主: duwei9981
[复制链接]
沪公网安备 31011702000001号 沪ICP备11019229号-2
发表于 2014-3-9 09:46
