怎样用递归求BOM的实际用量问题

fhb197999

各位老师好！请帮帮小弟，需求都写在附件中了！

一把小刀闯天下

'假设Q列有序，级联数量相乘，并联相加

Option Explicit

Sub test()
  Dim arr, i, j, k, sum, crr, n
  arr = Range("a2:q" & Cells(Rows.Count, "b").End(xlUp).Row + 1)
  ReDim brr(1 To UBound(arr, 1), 1 To 2)
  crr = arr
  For i = 2 To UBound(arr, 1) - 1
    For j = i To UBound(arr, 1) - 1
      If arr(j, 17) <> arr(j + 1, 17) Then
        arr = crr: n = n + 1: sum = 0
        For k = i To j: Call rec(arr, arr(k, 17), sum, 1): Next
        brr(n, 1) = arr(i, 17): brr(n, 2) = sum
        i = j: Exit For
      End If
  Next j, i
  With [s3]
    .Resize(Rows.Count - 2, UBound(brr, 2)).ClearContents
    If n > 0 Then .Resize(n, UBound(brr, 2)) = brr
  End With
End Sub

Function rec(arr, s, sum, n)
  Dim i
  For i = 2 To UBound(arr, 1) - 1
    If Len(arr(i, 2)) And arr(i, 17) = s Then
      sum = sum + arr(i, 3) * n: arr(i, 2) = vbNullString
      Call rec(arr, arr(i, 2), sum, arr(i, 3))
    End If
  Next
End Function

fhb197999

一把小刀闯天下 发表于 2018-8-17 13:49
'假设Q列有序，级联数量相乘，并联相加

Option Explicit

谢谢小刀老师，我调试下先！非常感谢！