|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
Option Explicit
Sub test()
Dim ar, br(), cr, i&, j&, k&, r&, iRowCount&, y&, x&, Rng As Range
Application.ScreenUpdating = False
cr = Array("No.", "国际条码", "型号", "名称", "数量")
Set Rng = Sheets(2).[A1:E12]
With Range([C2], Cells(Rows.Count, "F").End(3))
ar = .Value
For i = 1 To UBound(ar)
If InStr(ar(i, 1), "BOX") Then
r = r + 1
ReDim Preserve br(1 To r)
br(r) = i
End If
Next i
r = UBound(ar)
For i = 1 To UBound(br)
If i = UBound(br) Then
iRowCount = r - br(i) + 1
Else
iRowCount = br(i + 1) - br(i)
End If
br(i) = .Cells(br(i), 1).Resize(iRowCount, 4)
ReDim ar(1 To iRowCount + 1, 1 To 5)
ar(1, 1) = br(i)(1, 1)
For j = 0 To UBound(cr)
ar(2, j + 1) = cr(j)
Next j
For j = 2 To UBound(br(i))
ar(j + 1, 1) = j - 1
For k = 1 To UBound(br(i), 2)
ar(j + 1, k + 1) = br(i)(j, k)
Next k
Next j
br(i) = ar
br(i) = cutArray1(br(i), 10, 2)
Next i
End With
r = 0
With Workbooks.Add
With .Sheets(1)
For i = 1 To UBound(br)
For j = 1 To UBound(br(i))
r = r + 1
y = ((-Int(-r / 2)) - 1) * 13 + 1
x = IIf(r Mod 2 = 0, 7, 1)
Rng.Copy .Cells(y, x)
With .Cells(y, x).Resize(12, 5)
.HorizontalAlignment = xlCenter
.Borders.LineStyle = xlContinuous
.Font.Bold = True
.RowHeight = 42.5
.EntireColumn.AutoFit
.Columns(4).ColumnWidth = 19
End With
With .Cells(y, x).Resize(UBound(br(i)(j)), 5)
.Value = br(i)(j)
End With
Next j
Next i
End With
End With
Set Rng = Nothing
Application.ScreenUpdating = True
Beep
End Sub
Function cutArray1(ar, iCutNum&, Optional iHeader& = 1) As Variant
Dim brr(), crr, i&, j&, iPosRow&, r&, k&
ar = Application.Transpose(Application.Transpose(ar))
For i = iHeader + 1 To UBound(ar) Step iCutNum
iPosRow = IIf((i + iCutNum - 1) > UBound(ar), (UBound(ar) - iHeader) Mod iCutNum, iCutNum)
ReDim crr(1 To iPosRow + iHeader, 1 To UBound(ar, 2))
For j = iHeader + 1 To UBound(crr)
For k = 1 To UBound(crr, 2)
crr(j, k) = ar(i - 1 + j - iHeader, k)
Next k
Next j
For j = 1 To iHeader
For k = 1 To UBound(crr, 2)
crr(j, k) = ar(j, k)
Next k
Next j
r = r + 1
ReDim Preserve brr(1 To r)
brr(r) = crr
Next i
cutArray1 = brr
End Function
|
评分
-
1
查看全部评分
-
|