'F列路线没看懂,另外第7行示例可能有问题,C2并不是区间顶级物料,,,
Option Explicit
Sub test()
Dim arr, i, j, k, p, s, cnt, m, n
arr = Range("b5:c" & [b5].End(xlDown).Row + 1)
ReDim brr(1 To 10 ^ 4) As String, crr(1 To UBound(arr, 1), 20) As String
For i = 1 To UBound(arr, 1) - 1
If arr(i + 1, 2) = "物料" Or i = UBound(arr, 1) - 1 Then
For j = p + 1 To i
For k = p + 1 To i
If arr(j, 2) = arr(k, 1) Then Exit For
Next
If k = i + 1 Then
s = arr(j, 1): m = m + 1
n = n + 1: crr(m, 0) = arr(j, 2): crr(m, 1) = s
Do
For k = p + 1 To i
If s = arr(k, 2) Then
cnt = cnt + 1: brr(cnt) = arr(k, 1)
n = n + 1: crr(m, n) = arr(k, 1)
End If
Next
If cnt = 0 Then n = 0: Exit Do
s = brr(cnt): cnt = cnt - 1
Loop
End If
Next
i = i + 1: p = i
End If
Next
If m > 0 Then [g4].Resize(UBound(crr, 1), UBound(crr, 2) + 1) = crr
End Sub |