根据设备的维护频率分配日期和标准

Question

>伙计们，我需要你们的帮助，了解如何开发一个代码来根据设备的维护频率分配日期。 每个设备都有一个具有定义频率的适当计划。

• 天 (s(
• 周 (s(
• 月份

为了有一个很好的概述，我想看看计划在一段时间(5年(内的行为，考虑到每个设备的维护计划的频率。 我有一个设备列表，我想修复他们的维护计划开始日期。这意味着它们将根据其频率一起进行一些维护。 我有 2 个工作表(总体计划(和(开始参考(。

StartingReference 有一个这样的表：

+-------------+-----------+
| Equipment   | Starts on |
+=============+===========+
| Equipment A | 1/13/2020 |
+-------------+-----------+
| Equipment B | 1/13/2020 |
+-------------+-----------+
| Equipment C | 2/28/2020 |
+-------------+-----------+
| Equipment D | 2/28/2020 |
+-------------+-----------+

我需要一个代码，可以读取D和E列上的日期和频率，并在下一个截止日期的列上标记"x"。检查 E 列上的日期，并根据包含我所有 5 年期限的行 #1 在相应日期添加"X" -每列都有一个特定的日期。 在开始根据频率列分发日期之前，代码将检查该设备是否在"开始参考"表中定义了某个特定日期。

我的工作表总体计划具有以下结构。

+-------------+-----------------+------------------+------------+---------------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+
| Equipment   | Discipline      | Maintenance Type | Frequency  | Next Due date | 01/01 | 01/02 | (...) | 01/08 | 01/09 | (...) | 01/13 | (...) | 02/01 | (...) | 02/28 | (...) | 04/01 | (...) |
+=============+=================+==================+============+===============+=======+=======+=======+=======+=======+=======+=======+=======+=======+=======+=======+=======+=======+=======+
| Equipment A | Mechanical      | A                | 1 Week(s)  | 1/1/2020      | x     |       |       | x     |       |       | x     |       |       |       |       |       |       |       |
+-------------+-----------------+------------------+------------+---------------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+
| Equipment A | Instrumentation | B                | 1 Month(s) | 1/1/2020      | x     |       |       |       |       |       | x     |       | x     |       |       |       |       |       |
+-------------+-----------------+------------------+------------+---------------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+
| Equipment C | Electrical      | C                | 3 Month(s) | 1/1/2020      | x     |       |       |       |       |       |       |       |       |       |       |       | x     |       |
+-------------+-----------------+------------------+------------+---------------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+
| Equipment B | Instrumentation | B                | 1 Month(s) | 2/28/2020     |       |       |       |       |       |       | X     |       |       |       | x     |       |       |       |
+-------------+-----------------+------------------+------------+---------------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+

我开始写一些代码，但我需要承认 - 我迷路了......

Option Explicit
Sub DistributeMaintenancePlans()
Dim Dict As Object, CriteriaInterval As Range

With SetUp.Range("A2").CurrentRegion
Set CriteriaInterval = .Offset(1).Resize(.Rows.Count - 1, .Columns.Count) 'No Headers
End With
Dim EquipCriteria As Range
Set Dict = New Scripting.Dictionary
For Each EquipCriteria In CriteriaInterval
Dict(EquipCriteria.Value) = EquipCriteria.Offset(, 1).Value
Next
'Loop
Dim Arr() As Variant
With Sheet2.Range("A2").CurrentRegion
Arr = .Offset(1).Resize(.Rows.Count - 1, .Columns.Count) 'No Headers
End With
Dim i As Long, j As Long, tmp As Variant
Dim tmpDate As Date, lastDate As Date, tmpTxt As Variant, tdDays As Long
lastDate = Application.WorksheetFunction.Max(Sheets("General Plans").Range("1:1"))
For i = LBound(Arr, 1) To UBound(Arr, 1)
tmpTxt = Split(Arr(i, 4), " ")
Select Case tmpTxt(1)
Case "Month(s)"
tdDays = tmpTxt(0) * 30
Case "Week(s)"
tdDays = tmpTxt(0) * 7
Case "Day(s)"
tdDays = tmpTxt(0)
End Select
tmpDate = Arr(i, 1) + tdDays
'I need to find a way to put a loop to distribute the dates here
'tmpDate = Arr(i, 1) + tdDays

Next i
Set Dict = Nothing
Set CriteriaInterval = Nothing

结束子

Answer 1

我需要找到一种方法来放置一个循环来在此处分发日期 tmpDate = Arr(i， 1( + tdDays

请参阅下面的代码我正在尝试...

Option Explicit
Sub DistributeMaintenancePlans()
Dim Dict As Object, CriteriaInterval As Range

With SetUp.Range("A2").CurrentRegion
Set CriteriaInterval = .Offset(1).Resize(.Rows.Count - 1, .Columns.Count) 'No Headers
End With
Dim EquipCriteria As Range
Set Dict = New Scripting.Dictionary
For Each EquipCriteria In CriteriaInterval
Dict(EquipCriteria.Value) = EquipCriteria.Offset(, 1).Value
Next
'Loop
Dim Arr() As Variant
With Sheet2.Range("A2").CurrentRegion
Arr = .Offset(1).Resize(.Rows.Count - 1, .Columns.Count) 'No Headers
End With
Dim i As Long, j As Long, tmp As Variant
Dim tmpDate As Date, lastDate As Date, tmpTxt As Variant, tdDays As Long
lastDate = Application.WorksheetFunction.Max(Sheets("General Plans").Range("1:1"))
For i = LBound(Arr, 1) To UBound(Arr, 1)
tmpTxt = Split(Arr(i, 4), " ")
Select Case tmpTxt(1)
Case "Month(s)"
tdDays = tmpTxt(0) * 30
Case "Week(s)"
tdDays = tmpTxt(0) * 7
Case "Day(s)"
tdDays = tmpTxt(0)
End Select
tmpDate = Arr(i, 1) + tdDays
'I need to find a way to put a loop to distribute the dates here
'tmpDate = Arr(i, 1) + tdDays

Next i
Set Dict = Nothing
Set CriteriaInterval = Nothing
End Sub