エクセル２００７ 行を挿入すると式の一部がずれる

質問

エクセル２００７の行挿入の件で困っています。
行の挿入をすると式がずれてしまいます。

式はＨの列の下記の式が入っています。

インターネットで検索をしてＶＢＡに追加をして行挿入しても
式が反映されるようになりましたが行挿入すると式がずれて
しまいます。

どなたかお力をお貸しください。

H4=IF(B4="","",H3+D4-F4)
H5=IF(B5="","",H4+D5-F5)
H6=IF(B6="","",H5+D6-F6)
↓
H4=IF(B4="","",H3+D4-F4)
H5=IF(B5="","",H4+D5-F5)
H6=IF(B6="","",H4+D6-F6)←H4になって、H5にならない。
H7=IF(B7="","",H6+D7-F7)

ＶＢＡで設定した項目
------------------------------------------------------
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Rem 挿入行に数式のみコピー
Rem 変数宣言
Dim c As Range
Rem 対象範囲のセル数チェック
If Target.Columns.Count < 256 Then Exit Sub
Rem 対象範囲の列数をチェック
If Target.Rows.Count > 1 Then Exit Sub
Rem 対象範囲の各セルに対しループ
For Each c In Target
Rem 列番号が２及び４のとき一つ上の行の内容を複写
If c.Column = 2 Or c.Column = 4 Then c.Value = c.Offset(-1, 0).Value
Rem 上の行の内容が数式っぽいときはその内容を複写
If c.Offset(-1, 0).Formula Like "=*" Then
c.Offset(-1, 0).Resize(2, 1).Formula = c.Offset(-1, 0).Formula
End If
Next c
End Sub
------------------------------------------------------

回答No.2

chie65536（@chie65535）

やりたいのは「行挿入して、空白になった行に、自動的に式を入れたい」と言う事ですね？

H4=IF(B4="","",H3+D4-F4)
H5=IF(B5="","",H4+D5-F5)
H6=IF(B6="","",H5+D6-F6)

となっている時に、４行目と５行目の間に１行挿入すると

H4=IF(B4="","",H3+D4-F4)
空行空行空行空行空行空行
H6=IF(B6="","",H4+D5-F5)
H7=IF(B7="","",H6+D7-F7)

と言う状態になります。

これは「列挿入や行挿入をしても、セル参照の依存関係は変化しない」ようになっているからです。

H4を参照している式は、何をどう弄り回しても「元のH4だったセルを参照する」のです。

これを回避して「物理的に１行上を見に行く」には、式そのものを変えないといけません。

式そのものを変えて

H4=IF(B4="","",OFFSET(H4,-1,0)+D4-F4)
H5=IF(B5="","",OFFSET(H5,-1,0)+D5-F5)
H6=IF(B6="","",OFFSET(H6,-1,0)+D6-F6)

とすれば、行挿入しても

H4=IF(B4="","",OFFSET(H4,-1,0)+D4-F4)
空行空行空行空行空行空行空行空行空行
H6=IF(B6="","",OFFSET(H6,-1,0)+D6-F6)
H7=IF(B7="","",OFFSET(H7,-1,0)+D7-F7)

となります。

この時、OFFSET(H6,-1,0)は「挿入された空行のセル」を参照します。H4は参照しません。

なお、マクロは単純に

Private Sub Worksheet_Change(ByVal Target As Range)

Rem 挿入行に数式のみコピー
Rem 変数宣言
Dim c As Range

Rem 対象範囲のセル数チェック
If Target.Columns.Count < 256 Then Exit Sub
Rem 対象範囲の列数をチェック
If Target.Rows.Count > 1 Then Exit Sub
Rem 先頭行だったら無視
If Target.Row = 1 Then Exit Sub
Rem 対象範囲の各セルに対しループ
For Each c In Target
If IsEmpty(c) Then c.Formula = c.Offset(-1, 0).Formula
Next c

End Sub

で構いません。

式じゃない値がセルに入っている場合、FormulaプロパティはValueプロパティと同じ動作をします。

つまり「式だろうが何だろうが、１行上のFormulaをコピーしてくれば良い」のです。

回答No.1

Cupper-2

それ、根本的に間違ってないかい。

行挿入しても式が成り立つように関数式を作れば問題は解決すると思うんだ。
INDIRECT関数が見た目で分かりやすいと思うのでこれで説明してみます。
INDIRECT関数は文字列で与えられた引数を参照するセルに置き換える関数です。
具体的には

　　H4=IF(B4="","",H3+D4-F4)
　　　↓
　　H4=IF(INDIRECT("B4")="","",INDIRECT("H3")+INDIRECT("D4")-INDIRECT("F4"))

こうなります。
この関数式はコピーをしたときに参照するセルが変わりません。

　　　え？どういうこと？意味ないじゃん。それ。

…と思われるかもしれませんが、
ダブルクォーテーションで囲ったセルを示す文字列を、ちょっと変えるだけでうまく動作するようになります。
ROW関数はご存じですか。
指定したセルが何行目にあるのかを返してくれる関数です。
この関数の引数を指定せずに使用すると、関数式が入力された行番号が返ってくるんです。

　　H4=ROW()

とすると、４が返ってくると言うことです。
…コレ、使えますよね？

　　H4=IF(INDIRECT("B"&ROW())="","",INDIRECT("H"&ROW()-1)+INDIRECT("D"&ROW())-INDIRECT("F"&ROW()))

こうすればOK。
ポイントは２つ目の "H3" を返すために "H"&ROW()-1 としているところ。
ちょっと見づらい関数になりましたが、これでVBAを使わなくてもよくなります。

行挿入したときに数式が反映されなければ、数式を反映させたいセルを選択して

　　Ctrl + D

を押してください。
上のセルの内容をそっくりそのままコピーしてくれます。

※ INDIRECT関数以外にもOFFSET関数でも同様なことができますが、
　　元の関数式とちょっとかけ離れた式になるので、理解が難しいとか…。
　　でもそっちのほうがスッキリするんですけどね。