|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
方案太多,把计算规模减小了一半(http://blog.csdn.net/northwolves/archive/2008/12/20/3563350.aspx):
- Sub getit()
- Dim s() As String, i&, j&, k&, l&, n&, t, v, w, temp$, sum1&, sum2&
- t = Sheet1.[a2:c41]
- sum1 = 170
- sum2 = 200
- ReDim s(UBound(t), sum1, sum2)
- For i = 1 To UBound(t)
- If t(i, 2) <= sum1 And t(i, 3) <= sum2 Then s(1, t(i, 2), t(i, 3)) = t(i, 1)
- Next
- For j = 2 To UBound(t)
- For k = 1 To sum1
- For l = 1 To sum2
- If s(j - 1, k, l) > "" Then
- v = Split(s(j - 1, k, l))
- For m = 0 To UBound(v)
- If Not v(m) Like "*" & UBound(t) Then
- w = Split(v(m), ",")
- For i = Val(w(UBound(w))) + 1 To UBound(t)
- If k + t(i, 2) <= sum1 And l + t(i, 3) <= sum2 Then s(j, k + t(i, 2), l + t(i, 3)) = Trim(s(j, k + t(i, 2), l + t(i, 3)) & " " & v(m) & "," & t(i, 1))
- Next
- End If
- Next
- End If
- Next
- Next
- Next
- ReDim v(65535, 1 To 3)
- v(0, 1) = "序号"
- v(0, 2) = "重量"
- v(0, 3) = "体积"
- For k = 1 To sum1
- For l = 1 To sum2
- For j = 1 To UBound(t)
- If s(j, k, l) > "" Then
- w = Split(s(j, k, l))
- For m = 0 To UBound(w)
- n = n + 1
- v(n, 1) = w(m)
- v(n, 2) = k
- v(n, 3) = l
- Next
- End If
- Next j, l, k
- Sheet2.[a1].Resize(n, 3) = v
- End Sub
复制代码
[ 本帖最后由 northwolves 于 2008-12-22 16:15 编辑 ] |
|