- Sub lqxs()
- Dim Arr, i&, Brr, Crr(1 To 5000, 1 To 7)
- Dim d, k, t, t1, aa, j&, b, n&
- Set d = CreateObject("Scripting.Dictionary")
- Sheet3.Activate
- Arr = Sheet1.[a1].CurrentRegion
- Brr = Sheet2.[a1].CurrentRegion
- For i = 2 To UBound(Brr)
- If Not d.exists(Brr(i, 2)) Then
- d.Add Brr(i, 2), i
- Else
- d(Brr(i, 2)) = d(Brr(i, 2)) & "," & i
- End If
- Next
- k = d.keys
- t = d.items
- For i = 2 To UBound(Arr)
- If d.exists(Arr(i, 3)) Then
- t1 = d(Arr(i, 3))
- If InStr(t1, ",") Then
- aa = Split(t1, ",")
- For j = 0 To UBound(aa)
- n = n + 1
- b = Arr(i, 5) / Brr(aa(j), 3)
- Crr(n, 1) = Arr(i, 1)
- Crr(n, 2) = Brr(aa(j), 4)
- Crr(n, 3) = Brr(aa(j), 6)
- Crr(n, 4) = Arr(i, 4) * Brr(aa(j), 5)
- Crr(n, 6) = Format(Crr(n, 4) * Brr(aa(j), 7) * b, "0.0")
- Crr(n, 5) = Format(Crr(n, 6) / Crr(n, 4), "0.00")
- Crr(n, 7) = "套装"
- Next
- Else
- n = n + 1
- Crr(n, 1) = Arr(i, 1)
- Crr(n, 2) = Brr(t1, 4)
- Crr(n, 3) = Brr(t1, 6)
- Crr(n, 4) = Arr(i, 4) * Brr(t1, 5)
- Crr(n, 6) = Arr(i, 6)
- Crr(n, 5) = Crr(n, 6) / Crr(n, 4)
- Crr(n, 7) = "套装"
- End If
- Else
- n = n + 1
- Crr(n, 1) = Arr(i, 1)
- Crr(n, 2) = Arr(i, 2)
- Crr(n, 3) = Arr(i, 3)
- Crr(n, 4) = Arr(i, 4)
- Crr(n, 6) = Arr(i, 6)
- Crr(n, 5) = Arr(i, 5)
- End If
- Next
- [b3].Resize(n, 7) = Crr
- End Sub
复制代码 |