- Sub Macro1()
- On Error Resume Next
- Dim arr, brr, crr(1 To 16, 1 To 8)
- Dim n&, k%, j&, i&, s&
- Range("a3:h65536").ClearContents
- n = 3
- For k = 1 To 2
- arr = Sheets(k).UsedRange
- For i = 2 To UBound(arr)
- If InStr(arr(i, 1), "对应单号") And Len(arr(i, 1)) > 5 Then
- brr = Sheets(k).Cells(i, 1).Resize(16, 8)
- y = Val(brr(1, 6)): m = Val(brr(1, 7)): r = Val(brr(1, 8))
- rq = DateSerial(y, m, r)
- dh = Mid(brr(1, 1), 6)
- mc = Mid(brr(2, 1), 4)
- s = 0
- For j = 4 To UBound(brr)
- If brr(j, 2) <> "" Then
- s = s + 1
- crr(s, 1) = rq
- crr(s, 2) = dh
- crr(s, 3) = mc
- crr(s, 4) = brr(j, 2)
- crr(s, 5) = brr(j, 5)
- crr(s, 6) = brr(j, 6)
- crr(s, 7) = brr(j, 7)
- crr(s, 8) = brr(j, 3)
- End If
- Next
- Cells(n, 1).Resize(s, UBound(crr, 2)) = crr
- n = n + s
- End If
- Next
- Next
- x = Sheet3.Range("a65536").End(xlUp).Row
- If x < 3 Then Exit Sub
- Sheet3.Range("a3:h" & x).Copy Cells(n, 1)
- End Sub
复制代码 增加纠错处理和判断
|