|
发表于 2017-6-23 16:21
|
显示全部楼层
本楼为最佳答案
- Private Sub Worksheet_Activate()
- Call tt
- End Sub
-
- Sub tt()
- Dim arr, brr, crr(), r%, r2%, i%, tr%
- With Sheets("成品编码")
- r = .Range("b65536").End(3).Row
- arr = .Range("a3:e" & r)
- End With
- With Sheets("原料编码")
- r2 = .Range("b65536").End(3).Row
- brr = .Range("a3:e" & r2)
- End With
-
- On Error Resume Next
- ReDim crr(1 To Application.Max(UBound(arr), UBound(brr)) * 2, 1 To 5)
- For i = 1 To UBound(crr) Step 2
- k = (i + 1) / 2
- If k <= UBound(arr) Then
- n = n + 1
- crr(n, 1) = n
- For j = 2 To 5
- crr(n, j) = arr(k, j)
- Next
- End If
- If k <= UBound(brr) Then
- n = n + 1
- crr(n, 1) = n
- crr(n, 2) = brr(k, 2)
- crr(n, 4) = brr(k, 3)
- crr(n, 5) = brr(k, 4)
- End If
- Next
- [a3:e1000] = ""
- [a3].Resize(UBound(crr), 5) = crr
- [b3].Resize(UBound(crr)).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
- End Sub
复制代码 |
|