VBAを使ってエクセル上の表の集計を出したいのですが。

質問

初心者ゆえ稚拙な質問をお許しください。
A B C D E　　　　C＝引出額
1 * * * * 5　　　　D＝預入額
2 * * 1 * 4　　　　E＝合計額
3 * * * 1 5
エクセルでデーターを随時入力していくための上記のような表を多数のシートに作成したのですが、各シートに関数を入れていると容量的に重たくなってしまうので、VBAを使って下記のようにマクロを組んでみたのですが、引出額もしくは預入額に間違った値を入力した際削除すると、ひとつ上のレコードの値になってしまいます。
要望を言えば、引出額、預入額のセルに間違った値を入力した際削除しても余計な値が入らなく、該当のセルが空白の時は、その合計額のセルも空白にさせたいのですが、何かいい方法はないでしょうか？

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 6 Then
ActiveCell.Offset(columnoffset:=1).Value = ActiveCell.Offset(rowoffset:=-1, columnoffset:=1).Value - ActiveCell.Offset(columnoffset:=-1).Value
End If
If Target.Column = 7 Then
ActiveCell.Value = ActiveCell.Offset(rowoffset:=-1).Value + ActiveCell.Offset(columnoffset:=-1).Value
End If
End Sub
Worksheet_Changeにこだわっているわけではありません。
よろしくお願いします。

回答No.1

TTak · ベストアンサー

普通にワークシート関数や式を直接入れて計算させ、最後に値のみ張り付ければ良いと思われますが。
ActiveCell.Offset～で行うと、カット＆ペースト等、エラー処理まで含めればとんでもないコードになりそうです。

試してませんが、思いつきでは次のとおりです。適宜変更してみてください。

Private Sub Worksheet_Change(ByVal Target As Range)
　a = Range(Cells(Rows.Count, 3).End(xlUp).Address).Row
　b = Range(Cells(Rows.Count, 4).End(xlUp).Address).Row
　If a > b Then X = a Else X = b
　Application.EnableEvents = False
　Range("E3:E" & X + 1).ClearContents
　Range("E2").Formula = "=D2-C2"
　Range("E3").Formula = "=E2+D3-C3"
　Range("E3").AutoFill Destination:=Range("E3:E" & X), Type:=xlFillDefault
　Range("E3:E" & X).Value = Range("E3:E" & X).Value
　Application.EnableEvents = True
End Sub

お礼

お礼が大変遅くなって申し訳ございません。
非常に勉強になりました。結局のところはシート上に関数を入れたのですが、TTakさんの回答を参考に、アレンジを加え、他のことにまで利用できました。
本当にありがとうございます。

回答No.2

imogasi

●エクセルVBAの欠点ですね。もともとその時点の値で処理して終わりがプログラムの基本でした。イベントドリブンなどの仕組みが出たので、エクセルがはやっているので、値を変えると自動的に関係箇所を変えてくれるのが当たり前のように思っている方が増えた。値が変わった（訂正・変更など）とき、再実行をしてくれるようにするには、セルの値のCHANGEイベントなどを使わざるを得ないと思います。
●そのほかにバッチ処理（指示したときまとめて処理）的に処理して
、それを工夫して、できるだけほころびを出さない方法もありそう。
ブックを保存するとき、コマンドボタンを押したときなど再計算させるとか。
●そのとき、質問例題のコード方法でなく、上の行から順次処理をしていくコードにしたらどうですか。
多分Offsetなど不要ではないかと思います。
Cells(i,"C")の"C"の部分と、i　を繰り回せば使わなくて済むのでは。
またActiveCellも使う必要がないのではと思います。
.Valueも略すことができます。これは異論のある方もいるでしょうが
文字数が少なくなり、見通しが良くなりすっきりします。
質問の例のコードの意味が説明されていないので、見本例が挙げられませんが。

お礼

勝手な都合によりお礼を申し上げるのが遅くなったのをお許しください。質問自体が不充分のため、意味がわかりづらかったのではと反省していますが、回答につきましては非常に勉強になりました。
結局のところ、まだ私が勉強不足というのもあり、解決策として、各シート上に関数を入れてとりあえずは、使ってます。
ありがとうございました。