VBA ふたつおきに加算した時の合計の表示

質問

EXCELのVBAで下記のような表に対し、合計を求めるコードを作成しました。

　　　A　　　　　B　　　　C　　　　　D　　　E　　　～O P
1　　　　　　　　　　　　表題
2　　　　　　　　　　　小見出し
4 　　　　　　　　　　　　　4月　　　5月　　　6月　～3月　計
5　　　　　　　予約　　　100　　　200　　　100　　　
6　佐藤　　　手数　　　　30　　　20　　　　20　　
7　　　　　　　金額　　　130　　　220　　　120
8　　　　　　　予約　　　150　　　100　　　100
9　斉藤　　　手数　　　30　　　　20　　　　20
10　　　　　　金額　　　180　　　120　　　120
11　　　　　　予約　　　100　　　100　　　100
12加藤　　　手数　　　　20　　　30　　　　20
13　　　　　　金額　　　120　　　130　　　120
　　　・
　　　・
　　　・
　　合計　　　　　　　　　430　　　470　　　　360　　←それぞれの金額だけを合計した値

※人が増えたり減ったりしますので行数が３行づつ変化します。
　入力する人が限定されないため、セルへの計算式入力（消されてしまう可能性があるため）と、
　EXCELの機能（フィルター）などを使用して計算する方法がとれません。

この、合計の部分を求めるため、

Dim stRow As Long
Dim lastRow As Long

stRow = 5 lastRow = Range("F" & stRow).End(xlDown).Row

For i = 4 To lastRow
For J = 4 To 15
Cells(lastRow + 1, J).Select
Cells(lastRow + 1, J).Value = Application.WorksheetFunction.SumIf(Range(Cells(stRow, "F"), _
Cells(lastRow, "F")), "金額", Range(Cells(stRow, J), Cells(lastRow, J)))
Next
Next

これで、合計の欄にそれぞれの月の合計が出るようにはなったのですが、
入力値に変更を加えて再度計算をしたときに、
『一番最後のセルのしたに合計値を出す』としたため、
下に計算結果が（それも、すでに出ていた合計値を加算して）表示されてしまいます。
これを必ず合計の行に出していくには、合計の行が変化するため、
どのようなコードで書けば良いのかがわかりません。

よろしくお願いします。

回答No.3

mu2011

一例です。
合計行をデータ行の直後ではなく、データ最終行＋２行目（空行を挟む）に設定しては駄目でしょうか。

回答No.2

keithin

方法１：素直に一般的な方法
合計の行には素直にSUMIFの式を最初から入れておきます。
ただし素直で一般的じゃない方法をひとつ加えて，今仮に５行目から１３行まで３行組のデータと，１４行目に「合計」の行があるとすると
Ｃ１４：
=SUMIF($B$5:$B14,"金額",C$5:C14)
と式を入れて右にコピーしておきます。（念のため：数式は間違いでも循環参照でもありません）
３行組の行を行挿入したり行削除したり合計行を移動しても，問題なく計算できます。
（ついでに言うと，３行ずつデータ行を挿入する操作の方を「データ行追加」とか「データ行削除」のようなマクロで行った方が安全は安全です）

数式を守るためには，シートを保護しておきます。
シートの保護が出来ない，シートの保護の方法が判らなくて出来ないとき，やってみたけど「具体的にこういう問題が起きていて使えなかった」ときは，ご利用のエクセルのバージョンと具体的な状況を添えて別途ご相談を投稿して解決してください。

方法２：どうしてもマクロを使いたい場合。ただしあまり安全ではない。
「合計」の行が，どこにあるかはともかくどこかの一番下の行に「必ずある」という前提にするのなら，マクロでその行に所定の数値を計算して入れ直すだけです。

sub macro1()
　dim lastRow as long
　lastrow = range("A65536").end(xlup).row　’「合計」行
　with cells(lastrow, "C").resize(1, 12)
　　.formular1c1 = "=SUMIF(R5C2:R[-1]C2,""金額"",R5C:R[-1]C)"
　　.value = .value
　end with
end sub

＃ただし一番下の行には「合計」とＡ列に記入されているとする

＃余談
ご質問で掲示されたマクロのiのfor nextループは，何の役も果たさずに無意味に行数を回しているだけです。

回答No.1

imogasi

合計もデータとして、最終行に影響してしまうから、始に合計行を探知し（End(xlDown).RowやＸｌｕｐを使う、後者をお奨め）
、その行を削除して、質問のコードとと同じ処理をしたらどうなのですか？
簡単な話ではない？

お礼

発想を変えれば良いということですね。
確かに、最初に合計の行を探して削除してしまえばそこが一番下のセルとはならない。

頭が凝り固まっていました。
ありがとうございました。