|
Sub 填充PackageDetails()
Dim ar As Variant
Dim i As Long
Dim arr()
With Sheets("sheet1")
r = .Cells(Rows.Count, 1).End(xlUp).Row
ar = .Range("a1:b" & r)
cs = .[c1]
If cs = "" Then MsgBox "请输入填充行数!": End
If Not IsNumeric(cs) Then MsgBox "请输入数值!": End
End With
ReDim arr(1 To UBound(ar) * cs, 1 To 4)
For i = 2 To UBound(ar)
If Trim(ar(i, 1)) <> "" And Trim(ar(i, 2)) <> "" Then
n = n + 1
arr(n, 1) = n
arr(n, 2) = ar(i, 1)
arr(n, 4) = ar(i, 2)
For s = 1 To cs - 1
n = n + 1
arr(n, 1) = n
arr(n, 2) = ar(i, 1)
arr(n, 4) = 8
Next s
End If
Next i
With Sheets("Package Details")
rs = .Cells(Rows.Count, 1).End(xlUp).Row
If rs > 1 Then .Range("a2:d" & rs) = Empty
.[a2].Resize(n, 4) = arr
End With
MsgBox "ok!"
End Sub
|
|