• ベストアンサー

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

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

質問者が選んだベストアンサー

  • ベストアンサー
  • end-u
  • ベストアンサー率79% (496/625)
回答No.1

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)"       '念のため1行スキップ       i = i + 1       '次の起点を記憶       k = i     End If   Next End Sub こんな感じです。 ちなみに、この前削除されたご質問のケースは、 結合セルがある場合の、行挿入とSUM式セットでしたね。 ついでにそれも含めて書いておきますと、 基本的に結合セルに関係なく、3行目から下にLoopしていって 店舗名が空白でなければ1行挿入してSUM式を入れる。 さらにエリアが空白でなければ1行挿入して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

maki06
質問者

お礼

end-u さん 回答ありがとうございます。 列は固定だからそのままで行は起点を記憶させて そこからカウントさせたものを行とさせれば数式にあてはめることが できるんですね。。 なるほどです。この起点の部分がわからなかったんです! しかもに私の説明通りだと数式が被り循環してしまうのに ちゃんと循環させないようにスキップも入れてくださってますね。 本当に何から何まですみません(T0T) まさにやりたかったことはこれです! >念のため1行スキップ > i = i + 1 そして削除した質問。。 すごいですね。。 まさに完璧です!!! ちなみに削除した理由は丸投げすぎて失礼にあたると思ったからです。 少しでも考えたプログラムを載せようか迷ったのですが、返って混乱を 招くのでは?と検討し図を載せた次第だったのですが。。 今回教えて頂いた事を活かし今後に繋げていきたいと思います。 誠にありがとうございました! 感謝です!!!

その他の回答 (1)

  • fujillin
  • ベストアンサー率61% (1594/2576)
回答No.2

>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

maki06
質問者

お礼

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

関連するQ&A

専門家に質問してみよう