[已解决]求助恳请各位高手大婶帮忙关于合计累计的问题

qing009 · 发表于 2010-9-26 09:58

请教诸位高手大婶, 怎么样才能使黄色区域如同绿色区域那样如果选择"本月合计"或"本年累计 "就自动计算出来啊????谁有空帮我改一下,谢谢

==============

这个原来是学良的日记账

uMEGFjDk.rar (35.33 KB, 下载次数: 2)

开辆小富康 · 发表于 2010-9-26 10:19

按ALT+F11，原来的宏，替换成这一个。

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Row < 5 Then Exit Sub
If Target.Column <> 7 Then Exit Sub
If Target.Value = "本月合计" Then
m = Target.Row - 1
i = Range("C" & m + 1).End(xlUp).Row
Range("H" & m + 1) = Evaluate("SUMPRODUCT(($B$7:$B" & i & "=$B" & i & ")*H$7:H" & i & ")")
Range("M" & m + 1) = Evaluate("SUMPRODUCT(($B$7:$B" & i & "=$B" & i & ")*M$7:M" & i & ")")
Range("N" & m + 1) = Evaluate("SUMPRODUCT(($B$7:$B" & i & "=$B" & i & ")*N$7:N" & i & ")")
Range("P" & m + 1) = Evaluate("SUMPRODUCT(($B$7:$B" & i & "=$B" & i & ")*P$7:P" & i & ")")
Range("Q" & m + 1) = Evaluate("SUMPRODUCT(($B$7:$B" & i & "=$B" & i & ")*Q$7:Q" & i & ")")
Range("J" & m + 1) = Evaluate("SUMPRODUCT(($B$7:$B" & i & "=$B" & i & ")*J$7:J" & i & ")")
ElseIf Target.Value = "本年累计" Then
n = Target.Row - 2
Range("H" & n + 2) = Evaluate("SUMPRODUCT(($B$7:$B" & n & ">0)*H$7:H" & n & ")")
Range("J" & n + 2) = Evaluate("SUMPRODUCT(($B$7:$B" & n & ">0)*J$7:J" & n & ")")
Range("M" & n + 2) = Evaluate("SUMPRODUCT(($B$7:$B" & n & ">0)*M$7:M" & n & ")")
Range("N" & n + 2) = Evaluate("SUMPRODUCT(($B$7:$B" & n & ">0)*N$7:N" & n & ")")
Range("P" & n + 2) = Evaluate("SUMPRODUCT(($B$7:$B" & n & ">0)*P$7:P" & n & ")")
Range("Q" & n + 2) = Evaluate("SUMPRODUCT(($B$7:$B" & n & ">0)*Q$7:Q" & n & ")")
End If
End Sub

qing009 · 发表于 2010-9-26 10:47

问题解决谢谢富康

还有个问题俺是小白莫怪

那个

Range("H" & m + 1) = Evaluate("SUMPRODUCT(($B$7:$B" & i & "=$B" & i & ")*H$7:H" & i & ")")

和原来的

Range("H" & m + 1) = Evaluate("SUMPRODUCT(($B$6:$B" & i & "=$B" & i & ")*H$6:H" & i & ")")

有什么不同怎么理解啊

开辆小富康 · 发表于 2010-9-26 10:52

不同啊，因为你数据是从第七行开始的，前面的有合并单元格，没影响，后面几列，你第六行有文字，不能合计。

		自动登录	找回密码
密码			注册

[已解决]求助恳请各位高手大婶帮忙 关于合计 累计的问题

[已解决]求助恳请各位高手大婶帮忙关于合计累计的问题