分表中含空行的汇总

翁知江安

Sub huizong()
Dim i, j As Integer
Dim ar, br, tepar As Variant
Dim d As Object
Set d = CreateObject("scripting.dictionary")
Dim p As String
p = ThisWorkbook.Path & "\"
Dim fso As Object
Set fso = CreateObject("scripting.filesystemobject")
Dim f
Dim wb As Workbook
ar = ThisWorkbook.Sheets("汇总").UsedRange
For i = 3 To UBound(ar)
   d(ar(i, 1)) = i - 2
Next
ReDim br(1 To UBound(ar) - 2, 1 To 5)
For Each f In fso.getfolder(p).Files
   If InStr(f.Name, "月") > 0 Then
   Set wb = Workbooks.Open(f, 0)
   tepar = wb.Sheets("工资").UsedRange
   For i = 3 To UBound(tepar)
   If tepar(i, 3) <> "" Then
   If d.exists(tepar(i, 1)) Then
      br(d(tepar(i, 1)), 1) = tepar(i, 3)
       For j = 4 To UBound(tepar, 2)
        br(d(tepar(i, 1)), j - 1) = tepar(i, j) + br(d(tepar(i, 1)), j - 1)
        Next
     End If
     End If
     Next
     wb.Close False
     End If
Next
ThisWorkbook.Sheets("汇总").[c3].Resize(100, 5).ClearContents
ThisWorkbook.Sheets("汇总").[c3].Resize(UBound(br), 5) = br
MsgBox "ok"
End Sub

翁知江安

翁知江安

供参考，欢迎批评指正

shiruiqiang

翁知江安 发表于 2025-3-15 09:32
供参考，欢迎批评指正

翁老师，代码或附件都没上传

翁知江安

供参考，欢迎批评指正

ykcbf1100

shiruiqiang 发表于 2025-3-15 09:21
速度快的话，应该是sql语句

嗯，数据多的话用sql应该快一些。

hhxq001

opel-wong 发表于 2022-4-28 08:38
这样试试看，

你这个编辑界面方式是怎么弄的？

opel-wong

hhxq001 发表于 2025-3-21 08:48
你这个编辑界面方式是怎么弄的？

sublime_text