|
当排班情况为休息的时候,字典2里面没有对应的键值和条目,加个判断应该可以了,你试试
Sub demo()
Set d1 = CreateObject("Scripting.Dictionary")
Set d2 = CreateObject("Scripting.Dictionary")
a = Sheets(3).UsedRange
For i = 4 To UBound(a)
For j = 6 To UBound(a, 2)
d1(a(i, 4) & a(3, j)) = a(i, j)
Next
Next
a = Sheets(2).[a1].CurrentRegion
For i = 3 To UBound(a)
d2(a(i, 1)) = Array(a(i, 2), a(i, 3))
Next
Status = Sheets(2).[f2:f5]
a = Sheets(1).[a1].CurrentRegion
off = 1
For i = 2 To UBound(a)
off = 1 - off
If IsError(a(i, 6)) Then GoTo 1
a(i, 7) = d1(a(i, 6) & a(i, 2))
If a(i, 7) <> "ヰ" And a(i, 7) <> "" Then
If d2.exists(a(i, 7)) Then
t = Application.Max((a(i, 3) - d2(a(i, 7))(off)) * (-1) ^ off, 0) * 1440
a(i, 8) = Status(off + IIf(t, 1, 3), 1)
a(i, 9) = t
End If
Else
a(i, 8) = "": a(i, 9) = ""
End If
1 Next
Sheets(1).[a1].CurrentRegion = a
End Sub
|
|