エクセルVBAで関数を入力するには

質問

A列に小計と入力されていれば、その隣のB列にSUM関数を入れたいのですが
どうすればいいのでしょうか？
例えばA2からA20までの中でA5に小計と入力されていれば、隣のB列に=SUM(B2:B5)
と入力、A10に入力とあれば隣のB列に=SUM(B6:B9)と入力されるようにしたいのですが。。

回答No.1

end-u · ベストアンサー

Sub test1()
　　Dim i As Long
　　Dim k As Long 'SUM式の起点を記憶させるため

　　k = 2 '起点初期値
　　For i = 3 To 20 'A3からA20までLoop
　　　　If Cells(i, 1).Value = "小計" Then
　　　　　　Cells(i, 2).FormulaR1C1 = "=SUM(R[-1]C:R[" & k - i & "]C)"
　　　　　　'念のため１行スキップ
　　　　　　i = i + 1
　　　　　　'次の起点を記憶
　　　　　　k = i
　　　　End If
　　Next
End Sub
こんな感じです。

ちなみに、この前削除されたご質問のケースは、
結合セルがある場合の、行挿入とSUM式セットでしたね。
ついでにそれも含めて書いておきますと、

基本的に結合セルに関係なく、３行目から下にLoopしていって
店舗名が空白でなければ１行挿入してSUM式を入れる。
さらにエリアが空白でなければ１行挿入してSUM式を入れる。

店舗計については、最初に店舗計の起点となるセルを変数に覚えておいて、
SUM式を入れる時に使います。

エリア計については、Unionを使って店舗計を入れたセルを変数に覚えて、
それをSUM式に使います。
さらにエリアの起点を覚えておけば、後でエリアを再結合する時に使えます。

【※集計対象表がA1セルから始まっている場合のサンプル】
Sub test2()
　　Dim r　As Range 'エリア計用
　　Dim rr As Range '総計用
　　Dim i　As Long　'Loopカウンタ
　　Dim j　As Long　'店計起点
　　Dim k　As Long　'エリア起点

　　i = Cells(1).CurrentRegion.Rows.Count
　　Cells(i + 1, 1).Resize(, 2).Value = "end"
　　Cells(i + 2, 3).Value = "end"
　　i = 3
　　j = 2
　　k = 2
　　Do Until Cells(i, 3).Value = "end"
　　　　If Cells(i, 2).Value <> "" Then
　　　　　　Rows(i).Insert
　　　　　　Cells(i, 3).FormulaR1C1 = "=SUM(R[-1]C:R[" & j - i & "]C)"
　　　　　　Cells(i, 2).Value = "店計"
　　　　　　If r Is Nothing Then
　　　　　　　　Set r = Cells(i, 3)
　　　　　　Else
　　　　　　　　Set r = Union(r, Cells(i, 3))
　　　　　　End If
　　　　　　i = i + 1
　　　　　　j = i
　　　　End If
　　　　If Cells(i, 1).Value <> "" Then
　　　　　　Rows(i).Insert
　　　　　　Cells(i, 3).Formula = "=SUM(" & r.Address(0, 0) & ")"
　　　　　　Cells(i, 2).Value = "エリア計"
　　　　　　Range(Cells(i, 1), Cells(k, 1)).Merge
　　　　　　Set r = Nothing
　　　　　　If rr Is Nothing Then
　　　　　　　　Set rr = Cells(i, 3)
　　　　　　Else
　　　　　　　　Set rr = Union(rr, Cells(i, 3))
　　　　　　End If
　　　　　　i = i + 1
　　　　　　j = i
　　　　　　k = i
　　　　End If
　　　　i = i + 1
　　Loop
　　With Cells(i - 1, 1)
　　　　.Resize(2, 3).ClearContents
　　　　.Offset(, 2).Formula = "=SUM(" & rr.Address(0, 0) & ")"
　　　　.Value = "総計"
　　End With
　　Set rr = Nothing
End Sub

お礼

end-u さん
回答ありがとうございます。
列は固定だからそのままで行は起点を記憶させて
そこからカウントさせたものを行とさせれば数式にあてはめることが
できるんですね。。
なるほどです。この起点の部分がわからなかったんです！

しかもに私の説明通りだと数式が被り循環してしまうのに
ちゃんと循環させないようにスキップも入れてくださってますね。
本当に何から何まですみません(T0T)
まさにやりたかったことはこれです！
>念のため１行スキップ
>　i = i + 1

そして削除した質問。。
すごいですね。。
まさに完璧です！！！
ちなみに削除した理由は丸投げすぎて失礼にあたると思ったからです。
少しでも考えたプログラムを載せようか迷ったのですが、返って混乱を
招くのでは？と検討し図を載せた次第だったのですが。。
今回教えて頂いた事を活かし今後に繋げていきたいと思います。
誠にありがとうございました！
感謝です！！！

回答No.2

fujillin

＞A5に小計と入力されていれば、隣のB列に=SUM(B2:B5)
これって循環参照（B5セルが自分自身を参照している）になっていませんか？
多分、式の内容も変化させていくのだろうと想像しますが、そのあたりが不明なので要領のみです。

Dim c As Range
For Each c In Range("A2:A20")
If c.Value = "小計" Then
c.Offset(0, 1).Formula = "=SUM(B2:B5)"
ElseIf c.Value = "入力" Then
c.Offset(0, 1).Formula = "=SUM(B6:B9)"
End If
Next c

お礼

fujillin さん
回答ありがとうございます。
確かに循環させてしまっていますね。
しかも私の説明不足で申し訳ないです。
＞A10に入力　とかいてしまっていますが入力は小計の
誤りです。小計毎にB列に数式を入力させたかったんです。。