EXCEL VBA 集計列のエラーを自動修正したい

テーブルを使って口座や財布の収支を管理していて、列としては
ナンバー、日付、口座1の増減、口座1の残高、口座2の…、内容、項目
としていて、残高列は単純な数式で計算させているのですが、ある項目（項目列ではなく、増減記載列で）を移動させたり、間に列を増やすと、
それにつられて集計列も変化してしまい、集計列のエラーが出てしまいます。
なので、vbaでエラーを検知したら上のからAutoFillでコピーしてとしたいのですが、
集計列のエラーはどうしたら検知できますか。

IsErrors()やSpecialCells(xlCellTypeFormulas, xlErrors)でも検知できませんでした。

なら、いっそのこと変わるたびにとイベント処理させようとしたら
今度はAutoFillが失敗したというエラーが出てしまいます。

vbscript
1Private Sub Worksheet_Change(ByVal Target As Range)
2'    With ListObjects(1)
3'            .ListColumns(1).DataBodyRange(1).AutoFill excludeHead(.ListColumns(1).DataBodyRange)
4'            DoEvents
5'            .ListColumns(4).DataBodyRange(1).AutoFill excludeHead(.ListColumns(4).DataBodyRange)
6'            DoEvents
7'            .ListColumns(6).DataBodyRange(1).AutoFill excludeHead(.ListColumns(6).DataBodyRange)
8'            DoEvents
9'            .ListColumns(8).DataBodyRange(1).AutoFill excludeHead(.ListColumns(8).DataBodyRange)
10'            DoEvents
11'            .ListColumns(10).DataBodyRange(1).AutoFill excludeHead(.ListColumns(10).DataBodyRange)
12'            DoEvents
13'            .ListColumns(12).DataBodyRange(1).AutoFill excludeHead(.ListColumns(12).DataBodyRange)
14'    End With
15End Sub
16
17Private Function excludeHead(ByVal rng As Range) As Range
18    With rng
19        Set excludeHead = rng.Resize(.Rows.Count - 1, .Columns.Count).Offset(1, 0)
20    End With
21End Function
22
23End Sub

『追記』
mattuwanさん、回答・追記依頼ありがとうございます。
シチュエーションはこうです。
列[A]の1番にある3000はA銀行に3000円預けたという意味です。なので、3000円残高が増えてます.
財布列の2番は-1000円の出費があったことを指します。

ですが、財布の中身がマイナスになってしまうため、その前に財布にお金を入れたことを記入しなきゃと思い、2番の収支を3番に移すと、こうなります。

このように参照セルがずれてしまい、エラーが出ます。
じゃあ、間に列を追加しようとしても同様にエラーが出ます。

これが毎回なるので、vbaで自動的に修正したいのが今回の質問内容です。よろしくお願いいたします。

mattuwan

2019/12/26 02:33

表をテーブルとして設定しているのですね？表のイメージを提示できませんか？間違いがあったら、お互いにめんどくさいやり取りが増えると思います。

mattuwan

2019/12/27 00:25

もう一点、数式はどのような数式を書いてますか？

snowshink

2019/12/28 00:02

画像のもの(一つ前の残高+今回の増減)と、番号用のrow()-2しかないです

mattuwan

2019/12/28 04:47

1文字でも違ったら意味がないので、ちゃんと、教えてください。回答側に推測させるようなことはやめましょう。

snowshink

2019/12/28 05:01

わかりませんか?なら教えてあげましょう。 A列には=ROW()-2 D,F,H,J,L列にはformulaR1C1形式で=IFERROR(R[-1]C-RC[-1],R[-1]C) ですよ。あとはいわゆる記入欄です。

行動規範の内容に同意します

回答2件

ベストアンサー

ExcelVBA
1Private Sub Worksheet_Change(ByVal Target As Range)
2
3    Application.EnableEvents = False
4    With Me.ListObjects(1).DataBodyRange
5        With .Columns(1)
6            .Cells(1).Value = 1
7            .DataSeries
8        End With
9
10
11        Intersect(.Cells, .Rows(1).SpecialCells(xlCellTypeFormulas).EntireColumn).Formula = "=SUM(offset(D2,-1,0),C2)"
12    End With
13    
14    Application.EnableEvents = True
15End Sub
16

こんな感じでしょうか？
行の挿入等に影響されないように相対位置を指定する場合は、
Offset関数を使って指定するようです。

あと、足し算するときにSum関数を使うと
エラー値や文字列を無視してくれると思います。

エクセルのことはエクセルの掲示板で聞いていただくと、
関数の達人がいると思います。

あと、Changeイベントでセルの中身を編集するように書くと、
無限にイベントが発生しますので、
イベントの発生を抑制するコードを書く必要があります。
あ、数式欄は、データ範囲の1行目に数式がある列と判断してます。
そういう判断基準が成立しない場合は、
別途工夫が必要になります。

最後になりましたが、
わかりきったことをグダグダ聞いて、回答を書かないのは、
いじわるしているわけではないです。
今回の件は数式の工夫で対応可能かも知れないというのが、
ありましたので、重なる部分があるかもしれなかったですが、
考える前にはっきりさせておきたかったです。
ご理解いただけると幸いです。

投稿2019/12/30 14:15

編集2019/12/30 14:17

mattuwan

総合スコア2163