'根据规则输出应该少了3个,否则结果不确定,,,
Option Explicit
Sub test()
Dim arr, i, j, m, cnt, p
arr = Sheets("明细表").[a1].CurrentRegion.Offset(1).Value
ReDim brr(1 To 10 * UBound(arr, 1), 1 To 11) As String
For i = 1 To UBound(arr, 1) - 1
For j = i + 1 To UBound(arr, 1)
If Len(arr(i, 1)) + 2 = Len(arr(j, 1)) Then
m = m + 1: cnt = cnt + 10
brr(m, 1) = arr(i, 2): brr(m, 2) = arr(i, 7): brr(m, 3) = arr(i, 8)
brr(m, 4) = 1: brr(m, 5) = "TAO"
brr(m, 6) = cnt: brr(m, 7) = "L": brr(m, 8) = arr(j, 2)
brr(m, 9) = arr(j, 7): brr(m, 10) = arr(j, 6)
If i = 1 Then brr(m, 11) = "TAO" Else brr(m, 11) = "PC"
End If
If Len(arr(i, 1)) >= Len(arr(j, 1)) Then Exit For
Next
If m - p = 1 Then brr(m, 5) = "PC"
If j > i + 1 Then m = m + 1
cnt = 0: p = m
Next
Sheets("BOM收集模板").[m3].Resize(m, UBound(brr, 2)) = brr
End Sub |