|
或者把各个模块中的第一行Option Explicit删除了。
- Sub afa()
- Dim Arrldq, Myr&, i&, Brrdr, nd, ks, js, n1
- Dim sale,rdc,rdu,oe,gxrxh,gxrjh,fa,cogs,m&,mm&,j&,ii&
- Sheet11.Activate
- ks = Val(Left([b3].Value, 4)) - 1
- js = Val(Left([b4].Value, 4))
- n1 = js - ks + 1
- ReDim nd(1 To n1)
- For i = 1 To n1
- nd(i) = i + ks - 1
- Next
- Myr = [f65536].End(xlUp).Row
- Arrldq = Range("a18:dq" & Myr)
- Range("dr18:en20000").ClearContents
- Brrdr = Range("dr18:en" & Myr)
- For i = 1 To UBound(Arrldq)
- sale = 0: rdc = 0: rdu = 0: oe = 0: gxrxh = 0: gxrjh = 0: cogs = 0: m = 0: mm = 0: fa = 0
- For ii = 1 To n1
- sale = sale + Arrldq(i, nd(ii) - 2000 + 21)
- rdc = rdc + Arrldq(i, nd(ii) - 2000 + 51)
- rdu = rdu + Arrldq(i, nd(ii) - 2000 + 61)
- oe = oe + Arrldq(i, nd(ii) - 2000 + 41)
- gxrxh = gxrxh + Arrldq(i, nd(ii) - 2000 + 71) '关系人销货
- gxrjh = gxrjh + Arrldq(i, nd(ii) - 2000 + 81) '关系人进货
- cogs = cogs + Arrldq(i, nd(ii) - 2000 + 31) 'COGS
- If Arrldq(i, nd(ii) - 2000 + 21) > 0 Then m = m + 1
- If Arrldq(i, nd(ii) - 2000 + 31) > 0 Then m = m + 1
- If Arrldq(i, nd(ii) - 2000 + 41) > 0 Then m = m + 1
- If Arrldq(i, nd(ii) - 2000 + 91) < 0 Then mm = mm + 1
- Next
- For j = 1 To UBound(Brrdr, 2)
- If j = 1 Then
- Brrdr(i, j) = Arrldq(i, j + 101) 'DR18=CX18
- ElseIf j < 11 Then
- If Arrldq(i, j + 111) <> 0 Then 'DS18
- Brrdr(i, j) = (Arrldq(i, j + 101) + Arrldq(i, j + 102)) / 2
- Else
- Brrdr(i, j) = Arrldq(i, j + 102)
- End If
- ElseIf j = 11 Then 'EB WA RD to Sales
- If sale <> 0 Then
- If rdc > 0 Then
- Brrdr(i, j) = rdc / sale
- Else
- Brrdr(i, j) = rdu / sale
- End If
- End If
- ElseIf j = 12 Then
- If sale <> 0 Then
- Brrdr(i, j) = oe / sale 'EC WA OE to Sales
- End If
- ElseIf j = 13 Then
- For ii = 1 To n1
- fa = fa + Brrdr(i, nd(ii) - 2000)
- Next
- If sale <> 0 Then
- Brrdr(i, j) = fa / sale 'ED WA FA to Sales
- End If
- ElseIf j = 14 Then
- If sale <> 0 Then
- Brrdr(i, j) = gxrxh / sale 'EE WA 关系人销货 to Sales
- End If
- ElseIf j = 15 Then
- If cogs <> 0 Then
- Brrdr(i, j) = gxrjh / cogs 'EE WA 关系人进货 to COGS
- End If
- ElseIf j = 17 Then
- If m = 3 * n1 Then
- Brrdr(i, j) = "接受" 'EH 缺少连续五年
- Else
- Brrdr(i, j) = "拒绝"
- End If
- ElseIf j = 18 Then 'EI 任意X年
- If Brrdr(i, j - 1) = "拒绝" Then
- Brrdr(i, j) = "拒绝"
- Else
- If mm >= [EI16].Value Then
- Brrdr(i, j) = "拒绝"
- Else
- Brrdr(i, j) = "接受"
- End If
- End If
- ElseIf j = 19 Then 'EJ RD to Sales
- If Brrdr(i, j - 1) = "拒绝" Or Brrdr(i, j - 2) = "拒绝" Then
- Brrdr(i, j) = "拒绝"
- Else
- If [c11] <> "" Then
- If Brrdr(i, 11) < [c11].Value Then Brrdr(i, j) = "拒绝": GoTo 100
- End If
- If [d11] <> "" Then
- If Brrdr(i, 11) > [d11].Value Then Brrdr(i, j) = "拒绝": GoTo 100
- End If
- Brrdr(i, j) = "接受"
- End If
- ElseIf j = 20 Then 'EK OE to Sales
- If Brrdr(i, j - 1) = "拒绝" Or Brrdr(i, j - 2) = "拒绝" Or Brrdr(i, j - 3) = "拒绝" Then
- Brrdr(i, j) = "拒绝"
- Else
- If [c12] <> "" Then
- If Brrdr(i, 12) < [c12].Value Then Brrdr(i, j) = "拒绝": GoTo 100
- End If
- If [d12] <> "" Then
- If Brrdr(i, 12) > [d12].Value Then Brrdr(i, j) = "拒绝": GoTo 100
- End If
- Brrdr(i, j) = "接受"
- End If
- ElseIf j = 21 Then 'EJ FA to Sales
- If Brrdr(i, j - 1) = "拒绝" Or Brrdr(i, j - 2) = "拒绝" Or Brrdr(i, j - 3) = "拒绝" Or Brrdr(i, j - 4) = "拒绝" Then
- Brrdr(i, j) = "拒绝"
- Else
- If [c13] <> "" Then
- If Brrdr(i, 13) < [c13].Value Then Brrdr(i, j) = "拒绝": GoTo 100
- End If
- If [d13] <> "" Then
- If Brrdr(i, 13) > [d13].Value Then Brrdr(i, j) = "拒绝": GoTo 100
- End If
- Brrdr(i, j) = "接受"
- End If
- ElseIf j = 22 Then 'EM 关系人销货 to Sales
- If Brrdr(i, j - 1) = "拒绝" Or Brrdr(i, j - 2) = "拒绝" Or Brrdr(i, j - 3) = "拒绝" Or Brrdr(i, j - 4) = "拒绝" Or Brrdr(i, j - 5) = "拒绝" Then
- Brrdr(i, j) = "拒绝"
- Else
- If [c14] <> "" Then
- If Brrdr(i, 14) < [c14].Value Then Brrdr(i, j) = "拒绝": GoTo 100
- End If
- If [d14] <> "" Then
- If Brrdr(i, 14) > [d14].Value Then Brrdr(i, j) = "拒绝": GoTo 100
- End If
- Brrdr(i, j) = "接受"
- End If
- ElseIf j = 23 Then 'EN 关系人销货 to Sales
- If Brrdr(i, j - 1) = "拒绝" Or Brrdr(i, j - 2) = "拒绝" Or Brrdr(i, j - 3) = "拒绝" Or Brrdr(i, j - 4) = "拒绝" Or Brrdr(i, j - 5) = "拒绝" Or Brrdr(i, j - 6) = "拒绝" Then
- Brrdr(i, j) = "拒绝"
- Else
- If [c15] <> "" Then
- If Brrdr(i, 15) < [c15].Value Then Brrdr(i, j) = "拒绝": GoTo 100
- End If
- If [d15] <> "" Then
- If Brrdr(i, 15) > [d15].Value Then Brrdr(i, j) = "拒绝": GoTo 100
- End If
- Brrdr(i, j) = "接受"
- End If
-
- End If
- 100:
- Next
- Next
- Range("dr18").Resize(UBound(Brrdr), UBound(Brrdr, 2)) = Brrdr
- [eh13:en15].ClearContents
- Dim jsjj
- jsjj = [eh13:en15]
- For i = 17 To 23
- For j = 1 To UBound(Brrdr)
- If Brrdr(j, i) = "接受" Then
- jsjj(1, i - 16) = jsjj(1, i - 16) + 1
- Else
- jsjj(2, i - 16) = jsjj(2, i - 16) + 1
- End If
- jsjj(3, i - 16) = jsjj(3, i - 16) + 1
- Next
- Next
- [eh13].Resize(3, 7) = jsjj
- End Sub
复制代码 |
|