|
本帖最后由 hfwufanhf2006 于 2020-1-12 19:24 编辑
汇总表1的代码,放在工作表“汇总表1”中执行:
If [b10000].End(3).Row >= 4 Then
Range(Cells(4, 1), Cells([b10000].End(3).Row, 5)).ClearContents
End If
Dim arr
js = 0
hs = 4
For i = 4 To Worksheets("2018秋").[b1000].End(3).Row
s1 = Worksheets("2018秋").Cells(i, 2)
bz = False
For k = 1 To js
If arr(k) = s1 Then
bz = True
Exit For
End If
Next k
If Not bz Then
js = js + 1
If js = 1 Then
ReDim arr(1)
Else
ReDim Preserve arr(js)
End If
arr(js) = s1
Cells(hs, 1) = hs
Cells(hs, 2) = s1
Cells(hs, 3) = Worksheets("2018秋").Cells(i, 6)
hs = hs + 1
Else
Cells(k + 3, 3) = Worksheets("2018秋").Cells(i, 6)
End If
Next i
For i = 4 To Worksheets("2019春").[b1000].End(3).Row
s1 = Worksheets("2019春").Cells(i, 2)
bz = False
For k = 1 To js
If arr(k) = s1 Then
bz = True
Exit For
End If
Next k
If Not bz Then
js = js + 1
If js = 1 Then
ReDim arr(1)
Else
ReDim Preserve arr(js)
End If
arr(js) = s1
Cells(hs, 1) = hs
Cells(hs, 2) = s1
Cells(hs, 4) = Worksheets("2019春").Cells(i, 6)
hs = hs + 1
Else
Cells(k + 3, 4) = Worksheets("2019春").Cells(i, 6)
End If
Next i
For i = 4 To [b10000].End(3).Row
Cells(i, 5) = Cells(i, 3) + Cells(i, 4)
Next i
汇总表2的代码,需要放在工作表“汇总表2”中执行:
If [b10000].End(3).Row >= 4 Then
Range(Cells(4, 1), Cells([b10000].End(3).Row, 6)).ClearContents
End If
Dim arr
js = 0
hs = 4
For i = 4 To Worksheets("2018秋").[b1000].End(3).Row
s1 = Worksheets("2018秋").Cells(i, 2)
bz = False
For k = 1 To js
If arr(k) = s1 Then
bz = True
Exit For
End If
Next k
If Not bz Then
js = js + 1
If js = 1 Then
ReDim arr(1)
Else
ReDim Preserve arr(js)
End If
arr(js) = s1
Cells(hs, 1) = hs
Cells(hs, 2) = s1
Cells(hs, 3) = Worksheets("2018秋").Cells(i, 3)
Cells(hs, 4) = Worksheets("2018秋").Cells(i, 4)
Cells(hs, 5) = Worksheets("2018秋").Cells(i, 5)
hs = hs + 1
Else
Cells(k + 3, 3) = Worksheets("2018秋").Cells(i, 3)
Cells(k + 3, 4) = Worksheets("2018秋").Cells(i, 4)
Cells(k + 3, 5) = Worksheets("2018秋").Cells(i, 5)
End If
Next i
For i = 4 To Worksheets("2019春").[b1000].End(3).Row
s1 = Worksheets("2019春").Cells(i, 2)
bz = False
For k = 1 To js
If arr(k) = s1 Then
bz = True
Exit For
End If
Next k
If Not bz Then
js = js + 1
If js = 1 Then
ReDim arr(1)
Else
ReDim Preserve arr(js)
End If
arr(js) = s1
Cells(hs, 1) = hs
Cells(hs, 2) = s1
Cells(hs, 3) = Worksheets("2019春").Cells(i, 3)
Cells(hs, 4) = Worksheets("2019春").Cells(i, 4)
Cells(hs, 5) = Worksheets("2019春").Cells(i, 5)
hs = hs + 1
Else
Cells(k + 3, 3) = Cells(k + 3, 3) + Worksheets("2019春").Cells(i, 3)
Cells(k + 3, 4) = Cells(k + 3, 4) + Worksheets("2019春").Cells(i, 4)
Cells(k + 3, 5) = Cells(k + 3, 5) + Worksheets("2019春").Cells(i, 5)
End If
Next i
For i = 4 To [b10000].End(3).Row
Cells(i, 6) = Cells(i, 3) + Cells(i, 4) + Cells(i, 5)
Next i
1、这两个表的汇总很相似,结构都是一样的,只有个别细节有修改;
2、两个汇总表的最后合计使用了合并单元格,这个需要特别注意。代码没有对合并单元格做处理,在汇总前需要确保汇总的行数是足够用的,否则会发生错误,因为代码是不能对合并单元格写入数据的。作为建议,我建议删掉合并单元格,没有合并单元格并不影响美观;
3、最后的合计行以及其中的sum公式,代码没有做任何修改,这个公式也建议保留,否则最后的总合计就没有数据了;
|
评分
-
查看全部评分
|