VBAによる行追加時の

質問

VBAで行を追加するのは問題がないのですが、行を挿入するとき、挿入行の数式を含め追加すると、
絶対参照、複合参照にしている数式の一部が相対参照のように変わってしまいます。
また、挿入行以降の数式も全て変わってしまいます。
色々調べてみましたが、原因が分かりません。

G列1O行目以降に以下の数式
G10セル =IF(OR(E10<>"",F10<>""),$G$9+SUM($E$10:$E10)-SUM($F$10:$F10),"")
G11セル =IF(OR(E11<>"",F11<>""),$G$9+SUM($E$10:$E11)-SUM($F$10:$F11),"")
G12セル =IF(OR(E12<>"",F12<>""),$G$9+SUM($E$10:$E12)-SUM($F$10:$F12),"")

同じ数式で100行程度あります。
絶対参照以外は各行アドレスになります。

例えば、G10に行挿入すると、上記のG10はG11（以降も全て）にずれますますので、
G10セル =IF(OR(E10<>"",F10<>""),$G$9+SUM($E10:$E$10)-SUM($F10:$F$10),"")
G11セル =IF(OR(E11<>"",F11<>""),$G$9+SUM($E$10:$E11)-SUM($F$10:$F11),"")
G12セル =IF(OR(E12<>"",F12<>""),$G$9+SUM($E$10:$E12)-SUM($F$10:$F12),"")

となると考えていますが、

実際は、
G10セル =IF(OR(E10<>"",F10<>""),$G$9+SUM($E10:$E$11)-SUM($F10:$F$11),"")
G11セル =IF(OR(E11<>"",F11<>""),$G$9+SUM($E$11:$E11)-SUM($F$11:$F11),"")
G12セル =IF(OR(E12<>"",F12<>""),$G$9+SUM($E$12:$E12)-SUM($F$12:$F12),"")

と、SUM関数の絶対参照、複合参照の部分が相対参照と同じようにアドレスが変わってしまいます。

VBAでのコピーは行列範囲を指定してコピー、行列範囲の先頭を指定して貼付しています。

（行追加、コピー、貼付部分のみ抜粋）

'事前にコピーや切取りの操作を取り消す
Application.CutCopyMode = False

'選択された行の必要範囲を選択
Range(Cells(選択行, 選択開始列), Cells(選択行, 選択終了列)).Select

'選択された範囲をコピー
Selection.Copy

'選択行の選択開始先頭を選択
Range(Cells(選択行, 選択開始先頭).Address).Select

'貼付（行挿入での貼付）
Selection.Insert Shift:=xlDown

'コピーを取り消す
Application.CutCopyMode = False

なぜこのような挙動になるのか、また対策方法はあるのでしょうか？

なお、シートの上下の関係で、選択されている行を基準にコピー貼付を行う必要があるため、
最初の行、途中、最終行に関係なく正しく行追加する方法がありましたらお教え下さい

よろしくお願いします。

Win XP(SP3)
Excel2003,Excel2007

（基本、Excel2003で使用する予定です）

回答No.1

keithin · ベストアンサー

＞上記のG10はG11（以降も全て）にずれますます

まさにあなたがご自分で言ってるとおりなので，１０行目に行挿入したらG10を参照している数式はG11を参照する数式に書き換わります。

簡易な回避策としては，いま９行目（E9およびF9）は通常「タイトル行」として何か「言葉」が書かれているのが常なので，通常はＧ１０の数式を
G10セル =IF(OR(E10<>"",F10<>""),$G$9+SUM($E$9:$E10)-SUM($F$9:$F10),"")
以下コピー
としておくことで対策できます。

そういう細工をどうしてもしたくない場合
＞最初の行、途中、最終行に関係なく正しく行追加する方法がありましたらお教え下さい

Ｇ１０：
=IF(COUNT(INDIRECT("RC5:RC6",FALSE)),SUM(INDIRECT("G9"),INDIRECT("E10:E"&ROW()))-SUM(INDIRECT("F10:F"&ROW())),"")
とか
=IF(COUNT(INDIRECT("RC5:RC6",FALSE)),SUM(INDIRECT("G9"),INDIRECT("R10C5:RC5",FALSE))-SUM(INDIRECT("R10C6:RC6",FALSE)),"")
などのように，INDEX関数，OFFSET関数，INDIRECT関数などを駆使して行（セル）に全く依存しない数式を用意する事もできます。

お礼

>以下コピー
>としておくことで対策できます。

これは試したのですが、絶対参照、複合参照の部分も変わってしまいます。
コピー方法を変えてみたりと、色々試してみたのですが駄目でした。

で、結局は数式自体を変える事にしました。
元々は、行毎に範囲が変わって行くのですが、
範囲を無くし、全て同じ行で処理出来るように変更しました。

=IF(AND(E10="",F10=""),"",OFFSET(G10,-1,0)+E10-F10)

この数式の欠点は途中に入力値が無い場合、
次行以降が全てエラーになる事です。
まぁ、歯抜けになる使い方をしないので問題はなさそうです。

数式については、もっと勉強しなければいけないようです。

ありがとうございました。

回答No.2

Nouble

恐らくの話ですが、
関数式でない実データがあって、
その並びは行挿入でコントロールしてやる必要があるが、
行挿入をすると、
関数式の参照関係が挿入行を無視してしまう。

と、言うことのように思います。
これはＶＢＡ側の問題でないように思います。

シートで「コピーしたものを挿入」した時も
似たようになりますから…

ところで
シートで空行を作り、関数式をコピペする場合は
必要範囲を見極め
フィルして参照関係を整えるのですが、

ＶＢＡでこの「必要範囲を見極める」というのは、
人が介在しない以上、難しいと思います。
（※注：今の私の力量の範囲での話しですが）

しかしＶＢＡ、
多少量があってもへっちゃらですから、
データ挿入のたびに
式の部分全てに対して、
一番上のデータ行をコピーして全行に貼り付ければ
式が不必要に絶対参照で書かれていない限り
参照関係は整うはずです。

如何でしょうか？
参考程度にもなってないかも知れませんが、
これでも尚、お役に立てていたなら幸いです。

お礼

ＶＢＡで必要とする範囲を調べるのは、
セルに名前定義をしてる部分がありますので、
そのセルのアドレスから範囲を調べる事は出来ます。
実際、行数は増減するので、
開始、終了位置が分からないと全体の処理が出来ません。

ＶＢ６を色々と使っていましたので、
ＶＢＡもそんなに違和感はありません。
（それでも独特なもので苦労はしますけど）

ただ、エクセルの数式は、ほとんど使った事が無くて、
見よう見まねで四苦八苦している状態です。

>不必要に絶対参照で書かれていない限り
エクセル自体のコピーでも変わってしまうので、
ＶＢＡの問題ではないようですね。

全行コピーも考えたのですが、
結局絶対参照の計算を除く事にしました。
試してみて問題が無いのを何度も何度も確認しました。

まだまだ努力が足りないようです。

ありがとうございました。