|
在做一个表册汇总时,执行命令出错,点调试发现一行代码出黄色,但如果把分表中的记录字数减少又能正常汇总,但表中的数字又不能删减,应该如何改代码?具体请大家测试附件。
- Sub 汇总()
- Dim file$, path$
- Dim wb As Workbook
- Dim arr(1 To 5000, 1 To 3), lCount&
- Dim arrPos, j As Byte
- Application.ScreenUpdating = False
- arrPos = Array("a3", "b3")
- path = ThisWorkbook.path & Application.PathSeparator
- file = Dir(path & "*.xls", vbNormal + vbDirectory)
- Do While Len(file) > 0
- If file <> ThisWorkbook.Name Then
- lCount = lCount + 1
- Set wb = GetObject(path & file)
- With wb.Worksheets("sheet1")
- For j = 1 To UBound(arr, 2) - 1
- arr(lCount, j) = "'" & .Range(arrPos(j - 1))
- Next
- arr(lCount, 3) = .Range("c2") & ":" & .Range("c3") & "," & _
- .Range("d2") & ":" & .Range("d3") & "," & _
- .Range("a4") & ":" & .Range("b4").Text & "," & _
- .Range("c4") & ":" & .Range("d4") & "," & _
- .Range("a5") & ":" & .Range("b5") & "," & _
- .Range("a6") & ":" & .Range("b6") & "," & _
- .Range("a7") & ":" & .Range("b7") & "," & _
- .Range("a8") & ":" & .Range("b8") & "."
- End With
- wb.Close False
- End If
- file = Dir
- Loop
- Range("a" & Cells(Rows.Count, 1).End(xlUp).Row + 1).Resize(lCount, 3) = arr
- Range("a1").CurrentRegion.Borders.LineStyle = xlContinuous
- Application.ScreenUpdating = True
- MsgBox "汇总完成"
- End Sub
复制代码
|
|