|
这样会快一点,但也花了2分26秒
Sub text()
t = Time
Application.ScreenUpdating = False
Dim rng As Range, xh As Byte, hh As Long, xx As Byte, dd As Variant
dd = Array(1, 3, 5, 7, 9, 11, 13)
For xh = 1 To Worksheets.Count - 5 Step 1
For Each rng In Worksheets("产品" & xh).Range _
(Worksheets("产品" & xh).Range("a:A").Find("工段").Offset(1, 1), _
Worksheets("产品" & xh).Cells(Rows.Count, "B").End(3))
If rng.Value = "" Or rng.Value = "部件" Then
GoTo tz
Else
With Sheet2
hh = .Cells(Rows.Count, 1).End(3).Row + 1
.Cells(hh, 1).Value = Worksheets("产品" & xh).Cells(1, "c").Value
.Cells(hh, 2).Value = Worksheets("产品" & xh).Cells(1, "h").Value
.Cells(hh, 3).Value = IIf(rng.Offset(, -1).Value = "", rng.Offset(, -1).End(xlUp).Value, rng.Offset(, -1).Value)
.Cells(hh, 4).Value = rng.Value
For xx = 5 To 11 Step 1
.Cells(hh, xx).Value = rng.Offset(, dd(xx - 5)).Value
Next
rng.Offset(, 15).Resize(1, 7).Copy
.Cells(hh, 12).PasteSpecial Paste:=xlPasteValues
End With
End If
tz: Next
Next
Application.ScreenUpdating = True
MsgBox Format(Time - t, "hh:mm:ss")
End Sub
|
|