EXCELVBA SUM関数の計算範囲の取得方法について

Question

前提・実現したいこと

EXCELVBAのシステム開発に携わる事になったものです。
マクロで以下のような金額の集計表を作成しており、表の集計行にSUM関数を設定したいのですが、
SUM関数で足しこむ範囲をどうやって取得したらよいのか、思いつかず詰まってしまっている状態です。

大変申し訳ございませんが、
範囲を取得する為のアイデアをいただきたいと思っております。

集計表のイメージ

発生している問題・エラーメッセージ

value likeを使用して集計行に計算式をセットする事はできるのですが、
足す範囲の取得が出来ず困っております。

入れたい数式
1111の場合:コード1111の金額を足し算するものを集計行に入れたい。
※集計コードの種類や数は毎回異なるが、必ず上から昇順となっており、
集計行が各コードの一番下になるようになっている。

Comments

[hatena19]

> value likeを使用して集計行に計算式をセットする事はできるのですが、

その現状のコードを提示てください。

[masu06]

Sub keisan()

Dim ws As Worksheet
Dim hikaku1 As String
Dim hikaku2 As String
Dim counta As Long
Dim mainrow As Long

Set ws = ThisWorkbook.Sheets("sheet1")

mainrow = ws.Cells(ws.Rows.Count, 2).End(xlUp).Row

For counta = 3 To mainrow

If ws.Range("b" & counta).Value Like "*集計*" Then

　'以下で数式をセットしているのですが、範囲の取得方法が分からない状態です。
　'一旦、=SUM(E3:E4)"の数式を入れている状態です。
ws.Range("e" & counta).Formula = "=SUM(E3:E4)"

End If

Next

End Sub

Answer 1

例えば、

Sub keisan()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("sheet1")

    Dim c As Range, topRow As Long
    topRow = 3
    For Each c In ws.Range("B3:B" & ws.Cells(ws.Rows.Count, 2).End(xlUp).Row)
        If c.Value Like "*集計*" Then
            c.Offset(, 1).Resize(, 3).Formula = "=SUM(C" & topRow & ":C" & c.Row - 1 & ")"
            topRow = c.Row + 1
        End If
    Next
End Sub

Comments

[masu06]

ご回答いただきありがとうございます。
提示していただいたコードを確認させていただきました。

このような考え方で計算する方法もあったのかととても驚いております。
早速、この考え方を業務の方で活かそうと思います。

この度は本当にありがとうございました。

Answer 2

ちょうど似たようなVBAを以前作ったので、参考までに。
もっとかっこいいやり方はある気がしますが。

    StartRow = 3   'お好みの開始行
    EndRow = 10000 'お好みの最終行

    Lower = StartRow
    Upper = StartRow

For rw = StartRow To EndRow

    If Cells(rw,1).value like "*集計*" Then

            Target2Low = Cells(Lower, 2).Address
            Target3Low = Cells(Lower, 3).Address
                       
            Target2Up = Cells(Upper, 2).Address
            Target3Up = Cells(Upper, 3).Address
            
            Target2 = "=sum(" & Target2Low & ":" & Target2up & ")"
            Target3 = "=sum(" & Target3Low & ":" & Target3Up & ")"
            
            Cells(rw,2) = Target2
            Cells(rw.3) = Target3
        
        Lower = rw + 1

    End If

    Upper = rw
    
Next

Comments

[masu06]

ご回答ありがとうございます。
コードを確認させていただきました。

サンプル版までご提示してくださりありがとうございます。
合計の範囲を取得する部分はとても参考になりました。