期待大大们简化
- Sub lx()
- Dim ar(1 To 1000, 1 To 6)
- xs = Sheet1.[a1].CurrentRegion
- th = Sheet2.[a1].CurrentRegion
- Set d = CreateObject("scripting.dictionary")
- Set dd = CreateObject("scripting.dictionary")
- For i = 2 To UBound(th)
- s = th(i, 1) & th(i, 2)
- d(s) = th(i, 3)
- dd(s) = th(i, 4)
- Next
- For i = 2 To UBound(xs)
- s = xs(i, 1) & xs(i, 3)
- If d(s) <> 0 And xs(i, 4) > d(s) Then
- c = c + 1
- ar(c, 1) = xs(i, 1)
- ar(c, 2) = xs(i, 2)
- ar(c, 3) = xs(i, 3)
- ar(c, 4) = xs(i, 4) - d(s)
- ar(c, 5) = xs(i, 5)
- ar(c, 6) = ar(c, 4) * ar(c, 5)
- c = c + 1
- ar(c, 1) = xs(i, 1)
- ar(c, 2) = "新客户"
- ar(c, 3) = xs(i, 3)
- ar(c, 4) = d(s)
- d(s) = 0
- ar(c, 5) = dd(s)
- ar(c, 6) = ar(c, 4) * ar(c, 5)
- ElseIf d(s) <> 0 And xs(i, 4) <= d(s) Then
- c = c + 1
- ar(c, 1) = xs(i, 1)
- ar(c, 2) = xs(i, 2)
- ar(c, 3) = xs(i, 3)
- ar(c, 4) = 0
- ar(c, 5) = xs(i, 5)
- ar(c, 6) = ar(c, 4) * ar(c, 5)
- c = c + 1
- ar(c, 1) = xs(i, 1)
- ar(c, 2) = "新客户"
- ar(c, 3) = xs(i, 3)
- ar(c, 4) = xs(i, 4)
- d(s) = d(s) - xs(i, 4)
- ar(c, 5) = dd(s)
- ar(c, 6) = ar(c, 4) * ar(c, 5)
- Else
- c = c + 1
- ar(c, 1) = xs(i, 1)
- ar(c, 2) = xs(i, 2)
- ar(c, 3) = xs(i, 3)
- ar(c, 4) = xs(i, 4)
- ar(c, 5) = xs(i, 5)
- ar(c, 6) = xs(i, 6)
- End If
- Next
- [a33].Resize(c, 6) = ar
- End Sub
复制代码 |