|
本帖最后由 zjdh 于 2022-9-14 17:37 编辑
Sub 汇总666()
10 FLE = Dir(ThisWorkbook.Path & "\*.xls*")
If FIL = ThisWorkbook.Name Then GoTo 10
With Workbooks.Open(ThisWorkbook.Path & "\" & FLE)
ARR = .Sheets("装箱清单").Range("A1:O" & .Sheets("装箱清单").Range("B8000").End(3).Row + 1)
For I = 1 To UBound(ARR)
If Cells(I, "A").EntireRow.Hidden = True Then ARR(I, 7) = True
If ARR(I, 2) = "" Then ARR(I, 7) = True
Next
.Close False
End With
ReDim brr(1 To UBound(ARR), 1 To 9)
For I = 1 To UBound(ARR)
If Trim(ARR(I, 1)) = "设备号" Then
SBH = Trim(ARR(I, 2))
If InStr(ARR(I + 2, 2), " ") Then
XH = Split(ARR(I + 2, 2), " ")(0)
Else
XH = ARR(I + 2, 2)
End If
End If
If Trim(ARR(I, 1)) = "序号" Then
For j = 1 To 50
I = I + 1
If I > UBound(ARR) Then Exit For
If Trim(ARR(I, 1)) = "设备号" Then I = I - 1: Exit For
If ARR(I, 7) Then
GoTo 20
Else
k = k + 1
brr(k, 1) = SBH
brr(k, 2) = ARR(I, 2)
brr(k, 3) = ARR(I, 3)
brr(k, 4) = ARR(I, 6)
brr(k, 5) = ARR(I, 4)
brr(k, 7) = ARR(I, 5)
brr(k, 8) = ARR(I, 15)
brr(k, 9) = XH
End If
20 Next
End If
Next
Range("B2:J8000").ClearContents
Range("B2").Resize(k, 9) = brr
MsgBox "装箱单已汇总!"
End Sub
|
|