|
楼主 |
发表于 2024-10-24 14:59
|
显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
- Sub ProductionSchedulingRevised()
- '项目占位计划B版2024-10-22-3,虽然能进行排产,我不知道怎么写设备占位的代码。请哪位老师帮忙添加一下吧,谢谢了
- 但没有把设备占位考虑进来,未达到我的使用要求。
- Dim lastRow As Long
- Dim ws As Worksheet
- Set ws = ThisWorkbook.Sheets("排产清单")
- lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
- Dim orderNumbers As Object
- Set orderNumbers = CreateObject("Scripting.Dictionary")
- Dim i As Long
- For i = 2 To lastRow
- Dim orderNumber As String
- orderNumber = ws.Cells(i, 1).Value
- If Not orderNumbers.Exists(orderNumber) Then
- orderNumbers.Add orderNumber, i
- End If
- Next i
- Dim currentDate As Date
- currentDate = Date
- Dim equipmentUsage As Object
- Set equipmentUsage = CreateObject("Scripting.Dictionary")
- Dim processedRows As Object
- Set processedRows = CreateObject("Scripting.Dictionary")
- For Each orderKey In orderNumbers.Keys
- Dim firstProcessRow As Long
- firstProcessRow = orderNumbers(orderKey)
- If Not processedRows.Exists(firstProcessRow) Then
- ws.Cells(firstProcessRow, 10).Value = currentDate
- Dim duration As Long
- duration = ws.Cells(firstProcessRow, 11).Value
- ws.Cells(firstProcessRow, 12).Value = currentDate + duration
- processedRows.Add firstProcessRow, True
- End If
- Dim currentRow As Long
- currentRow = firstProcessRow
- Do While True
- Dim nextProcessRow As Long
- nextProcessRow = FindNextProcessRow(ws, currentRow)
- If nextProcessRow = 0 Then Exit Do
- Dim equipment As String
- equipment = ws.Cells(nextProcessRow, 9).Value
- If IsEquipmentAvailableForPriorityRevised(ws, equipment, ws.Cells(nextProcessRow, 10).Value, ws.Cells(nextProcessRow, 13).Value, equipmentUsage) Then
- ws.Cells(nextProcessRow, 10).Value = ws.Cells(currentRow, 12).Value
- duration = ws.Cells(nextProcessRow, 11).Value
- ws.Cells(nextProcessRow, 12).Value = ws.Cells(nextProcessRow, 10).Value + duration
- processedRows.Add nextProcessRow, True
- currentRow = nextProcessRow
- Else
- currentRow = WaitForEquipmentAvailability(ws, currentRow, equipment, equipmentUsage)
- End If
- Loop
- Next orderKey
- End Sub
- Function FindNextProcessRow(ws As Worksheet, startRow As Long) As Long
- Dim i As Long
- For i = startRow + 1 To ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
- If ws.Cells(i, 1).Value = ws.Cells(startRow, 1).Value And ws.Cells(i, 6).Value = ws.Cells(startRow, 6).Value + 10 Then
- FindNextProcessRow = i
- Exit Function
- End If
- Next i
- FindNextProcessRow = 0
- End Function
- Function IsEquipmentAvailableForPriorityRevised(ws As Worksheet, equipment As String, startDate As Date, priority As Long, equipmentUsage As Object) As Boolean
- If equipmentUsage.Exists(equipment) Then
- Dim lastUsedDate As Date
- lastUsedDate = equipmentUsage(equipment)
- Dim i As Long
- For i = 2 To ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
- If ws.Cells(i, 9).Value = equipment And ws.Cells(i, 10).Value > startDate And ws.Cells(i, 13).Value <= priority Then
- If ws.Cells(i, 10).Value < lastUsedDate Then
- IsEquipmentAvailableForPriorityRevised = False
- Else
- IsEquipmentAvailableForPriorityRevised = True
- End If
- Exit Function
- End If
- Next i
- Else
- IsEquipmentAvailableForPriorityRevised = True
- End If
- End Function
- Function WaitForEquipmentAvailability(ws As Worksheet, currentRow As Long, equipment As String, equipmentUsage As Object) As Long
- Dim endDate As Date
- endDate = ws.Cells(currentRow, 12).Value
- Dim nextAvailableRow As Long
- Do
- nextAvailableRow = FindNextAvailableTimeForPriorityRevised(ws, currentRow, equipment)
- If nextAvailableRow = 0 Then
- ' Wait until equipment is available
- DoEvents
- Else
- Dim newStartDate As Date
- newStartDate = ws.Cells(nextAvailableRow, 10).Value
- If IsEquipmentAvailableForPriorityRevised(ws, equipment, newStartDate, ws.Cells(nextAvailableRow, 13).Value, equipmentUsage) Then
- Exit Do
- Else
- currentRow = nextAvailableRow
- End If
- End If
- Loop
- WaitForEquipmentAvailability = nextAvailableRow
- End Function
- Function FindNextAvailableTimeForPriorityRevised(ws As Worksheet, currentRow As Long, equipment As String) As Long
- Dim endDate As Date
- endDate = ws.Cells(currentRow, 12).Value
- Dim i As Long
- For i = currentRow + 1 To ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
- If ws.Cells(i, 9).Value = equipment And ws.Cells(i, 9).Value > endDate Then
- FindNextAvailableTimeForPriorityRevised = i
- Exit Function
- End If
- Next i
- FindNextAvailableTimeForPriorityRevised = currentRow
- End Function
复制代码 |
|