|
楼主 |
发表于 2015-1-14 21:52
|
显示全部楼层
以下是代码,希望高手能优化:
Private Sub CommandButton1_Click() 'BOM表多级展开
Dim Arr, Brr, Crr, Trr, T
Dim lr, er, i, j, k, m, n, r1, r2, s, c, z1, z2
Dim dic, d
Set dic = CreateObject("Scripting.Dictionary")
'On Error Resume Next
Application.ScreenUpdating = False
With Sheet2
Trr = .Range("A1:AH6")
T = WorksheetFunction.Index(Trr, 6) '提取第6行
lr = .Range("a65536").End(xlUp).Row
Arr = .Range("A6:AH" & lr) '.CurrentRegion
End With
For k = 2 To UBound(Arr)
dic(Arr(k, 1)) = ""
Next
d = dic.keys
'一级BOM展开
ReDim Brr(1 To UBound(Arr), 1 To UBound(Arr, 2))
For i = 2 To UBound(Arr)
If Len(Arr(i, 1)) = 2 Then
z1 = z1 + 1
For j = 1 To UBound(Arr, 2)
Brr(z1, j) = Arr(i, j)
Next
End If
Next
With Sheet1
.Cells.ClearContents
.Range("A1").Resize(2, UBound(Trr, 2)) = Trr '写入表头
.Range("A65536").End(xlUp).Offset(2).Resize(1, UBound(T)) = T
.Range("A65536").End(xlUp).Offset(1).Resize(z1, UBound(Brr, 2)) = Brr '写入一级BOM
'多级BOM展开
ReDim Crr(1 To UBound(Arr), 1 To UBound(Arr, 2))
s = CDbl(InputBox(" 请输入要展开的层次数," & Chr(10) & Chr(10) & " 本BOM最大层次级别为:" & UBound(d) + 1 & " 层。", "多级BOM展开:", UBound(d) + 1))
If s + 1 >= 3 Then
For c = 3 To s + 1
For i = 2 To UBound(Arr)
If Len(Arr(i, 1)) = c And Len(Arr(i - 1, 1)) = c - 1 Then
r1 = i - 1
For j = i To UBound(Arr)
If Len(Arr(j, 1)) = c - 1 Then
r2 = j - 1
Exit For
End If
Next
z2 = 0
For m = r1 To r2
If Len(Arr(m, 1)) = c - 1 Or Len(Arr(m, 1)) = c Then
z2 = z2 + 1
For n = 1 To UBound(Arr, 2)
Crr(z2, n) = Arr(m, n)
Next
End If
Next
.Range("A65536").End(xlUp).Offset(2).Resize(z2, UBound(Crr, 2)) = Crr '也可数组合并,最后一次写入
i = r2 + 1
End If
Next
Next
End If
End With
Set d = Nothing
Application.ScreenUpdating = True
End Sub
|
|