|
本帖最后由 cbtaja 于 2014-4-5 19:58 编辑
湘西农民 发表于 2014-4-4 22:03
若有51列数据,1-3行是表头,4行开始是数据,怎么改代码啊?
按7楼代码,作简化而得,并修正合并依据为姓名、部门的双条件。可根据情况修改代码中前3句的数值。 Const bths = 4 '表头行数
- Const bths = 4 '表头行数
- Const btls = 4 '表头列数
- Const bgzls = 16 '表格总列数
- Sub 求和()
- ReDim brr(1 To 10000, 1 To bgzls)
- Set d = CreateObject("scripting.dictionary")
- For Each sht In Worksheets
- If sht.Name <> "汇总" Then
- i = sht.Range("b65536").End(3).Row
- arr = sht.[a1].Offset(bths, 0).Resize(i - bths, bgzls)
- For y = 1 To UBound(arr)
- If d.Exists(arr(y, 2) & arr(y, 4)) Then
- Z = d(arr(y, 2) & arr(y, 4))
- For p = btls + 1 To bgzls
- brr(Z, p) = brr(Z, p) + arr(y, p)
- Next
- Else
- m = m + 1
- d(arr(y, 2) & arr(y, 4)) = m
- brr(m, 1) = m
- For p = 2 To bgzls
- brr(m, p) = arr(y, p)
- Next
- End If
- Next
- End If
- Next
- Me.UsedRange.Offset(bths, 0).Clear
- With [a1].Offset(bths, 0).Resize(m, bgzls)
- .Value = brr
- .Borders.LineStyle = xlContinuous
- End With
- End Sub
复制代码
|
|