|
代码是编了一个,但找不出错在哪里。- Sub 计算New() '目的:让每一期的 “期初+销售-回款”尽量向0值靠近
- arr = Range("a4:z" & [a65536].End(3).Row)
- Set d = CreateObject("scripting.dictionary")
- x = 0
- For i = 1 To UBound(arr)
- bm = arr(i, 2) '以编码为key
- d(arr(i, 2)) = d(arr(i, 2)) & "," & i
- Next
- For Each bm In d.keys
- hsrr = Split(d(bm), ","): r = UBound(hsrr)
- If r > 1 Then
- For i = 2 To r
- k1 = Val(hsrr(i))
- a1 = arr(k1, 5 + x): b1 = arr(k1, 6 + x): c1 = arr(k1, 7 + x)
- If a1 < 0 Then '期初小于0
- For j = i - 1 To 1 Step -1 '从下往上冲抵
- k = Val(hsrr(j))
- a = arr(k, 5 + x): b = arr(k, 6 + x): c = arr(k, 7 + x)
- If a + b - c <> 0 Then
- If (a + b - c) + a1 > 0 Then
- a = a + a1: a1 = 0
- Else
- a1 = a1 + (a + b - c): a = c - b
- End If
- arr(k, 5 + x) = a: arr(k, 6 + x) = b: arr(k, 7 + x) = c
- arr(k1, 5 + x) = a1: arr(k1, 6 + x) = b1: arr(k1, 7 + x) = c1
- End If
- Next
- End If
-
- If b1 < 0 Then '销售小于0
- For j = i - 1 To 1 Step -1 '从下往上冲抵
- k = Val(hsrr(j))
- a = arr(k, 5 + x): b = arr(k, 6 + x): c = arr(k, 7 + x)
- If a + b - c > 0 Then
- If (a + b - c) + b1 > 0 Then
- a = a + b1: a1 = 0
- Else
- b1 = b1 + (a + b - c): a = c - b
- End If
- arr(k, 5 + x) = a: arr(k, 6 + x) = b: arr(k, 7 + x) = c
- arr(k1, 5 + x) = a1: arr(k1, 6 + x) = b1: arr(k1, 7 + x) = c1
- End If
- Next
- End If
-
- If c1 < 0 Then '回款小于0,只与回款冲抵
- For j = i - 1 To 1 Step -1 '从下往上冲抵
- k = Val(hsrr(j))
- a = arr(k, 5 + x): b = arr(k, 6 + x): c = arr(k, 7 + x)
- If c > 0 Then c = c + c1: c1 = 0
- arr(k, 7 + x) = c
- arr(k1, 7 + x) = c1
- Next
- End If
-
- If c1 > 0 Then '回款大于0
- For j = 1 To i - 1 '从上往下冲抵
- k = Val(hsrr(j))
- a = arr(k, 5 + x): b = arr(k, 6 + x): c = arr(k, 7 + x)
- If a + b - c <> 0 Then
- If (a + b - c) - c1 > 0 Then
- a = a - c1: c1 = 0
- Else
- c1 = c1 - (a + b - c): a = c - b
- End If
- arr(k, 5 + x) = a: arr(k, 6 + x) = b: arr(k, 7 + x) = c
- arr(k1, 5 + x) = a1: arr(k1, 6 + x) = b1: arr(k1, 7 + x) = c1
- End If
- Next
- End If
- Next
- End If
- For i = 1 To r
- k = Val(hsrr(i))
- arr(k, 8 + x) = arr(k, 5 + x) + arr(k, 6 + x) - arr(k, 7 + x)
- Next
- Next
- Range("h4:h" & [a65536].End(3).Row) = Application.Index(arr, , 8 + x)
-
- End Sub
复制代码 |
|