|
要解决的问题:按月记录,多次点汇总,前面的各月会多次出现“本月合计”“本年累计”,例如:当一月记录完后,点“汇总”,出现“本月合计”“本年累计”,这是对的,但当把二月的数据记录完后,再点“汇总”时,则在一月的后面又出现了一次“本月合计”“本年累计”,显然重复了,只要保留一次怎么办?请帮忙修改代码,先谢了!!- Sub 汇总()
- Dim i As Integer
- Dim mjj As Double, yjj As Double, mdj As Double, ydj As Double
- For i = 6 To 999
- mjj = Range("e" & i).Value + mjj
- mdj = Range("f" & i).Value + mdj
- yjj = yjj + Range("e" & i).Value
- ydj = ydj + Range("f" & i).Value
- If Month(Range("a" & i)) <> Month(Range("a" & i + 1)) And Range("d" & i + 2) <> "本年累计" Then
- Rows(i + 1 & ":" & i + 2).Insert
- Range("d" & i + 1) = "本月合计"
- Range("d" & i + 2) = "本年累计"
- Range("e" & i + 1) = mjj
- Range("f" & i + 1) = mdj
- Range("e" & i + 2) = yjj
- Range("f" & i + 2) = ydj
- mjj = 0: mdj = 0
- i = i + 2
- End If
- Next i
- End Sub
复制代码
本帖最后由 qh8600 于 2014-7-12 15:57 编辑
- Sub 汇总()
- Dim i As Integer
- Dim mjj As Double, yjj As Double, mdj As Double, ydj As Double
- For i = 6 To 999
- mjj = Range("e" & i).Value + mjj
- mdj = Range("f" & i).Value + mdj
- yjj = yjj + Range("e" & i).Value
- ydj = ydj + Range("f" & i).Value
- If Month(Range("a" & i)) <> Month(Range("a" & i + 1)) And Range("d" & i + 2) <> "本年累计" And Range("a" & i) <> "" Then
- Rows(i + 1 & ":" & i + 2).Insert
- Range("d" & i + 1) = "本月合计"
- Range("d" & i + 2) = "本年累计"
- Range("e" & i + 1) = mjj
- Range("f" & i + 1) = mdj
- Range("e" & i + 2) = yjj
- Range("f" & i + 2) = ydj
- mjj = 0: mdj = 0
- i = i + 2
- End If
- Next i
- End Sub
复制代码加个判断试试
按月汇总.rar
(22.35 KB, 下载次数: 15)
|
|