|
- Private Sub Worksheet_Change(ByVal Target As Range)
- If Target.Count > 1 Or Target = "" Then Exit Sub
- If Target.Column <> 3 Or Target.Row < 2 Then Exit Sub
- Dim m&, n%, Arr, i&, d, k, t, x, r1, bm$
- Set d = CreateObject("Scripting.Dictionary")
- m = Target.Row: bm = Target.Offset(0, -1).Value
- Arr = Range("a2:c" & m)
- For i = 1 To UBound(Arr)
- Select Case Day(Arr(i, 1))
- Case 1 To 7
- n = 1
- Case 8 To 14
- n = 2
- Case 15 To 21
- n = 3
- Case 22 To 28
- n = 4
- Case Else
- n = 5
- End Select
- If Arr(i, 3) <> "" Then
- d(n & "|" & Arr(i, 2)) = d(n & "|" & Arr(i, 2)) + Arr(i, 3)
- End If
- Next
- k = d.keys
- t = d.items
- For i = 0 To UBound(k)
- x = Split(k(i), "|")
- If x(1) = bm Then
- Set r1 = Sheet1.Rows(1).Find(x(1), , , 1)
- If Sheet1.Cells(x(0) + 1, r1.Column + 2) < t(i) Then
- MsgBox bm & " 已经超过第" & x(0) & " 周预算,警告"
- Target.Value = "": Exit For
- End If
- End If
- Next
- End Sub
复制代码 |
|