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

前提・実現したいこと

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

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

集計表のイメージ

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

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

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

hatena19

2021/10/30 01:46

> value likeを使用して集計行に計算式をセットする事はできるのですが、その現状のコードを提示てください。

masu06

2021/10/30 01:52 編集

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

行動規範の内容に同意します

回答2件

ベストアンサー

例えば、

vba
1Sub keisan()
2    Dim ws As Worksheet
3    Set ws = ThisWorkbook.Sheets("sheet1")
4
5    Dim c As Range, topRow As Long
6    topRow = 3
7    For Each c In ws.Range("B3:B" & ws.Cells(ws.Rows.Count, 2).End(xlUp).Row)
8        If c.Value Like "*集計*" Then
9            c.Offset(, 1).Resize(, 3).Formula = "=SUM(C" & topRow & ":C" & c.Row - 1 & ")"
10            topRow = c.Row + 1
11        End If
12    Next
13End Sub

投稿2021/10/30 02:19

hatena19

総合スコア34084

masu06

2021/10/30 02:38

ご回答いただきありがとうございます。提示していただいたコードを確認させていただきました。このような考え方で計算する方法もあったのかととても驚いております。早速、この考え方を業務の方で活かそうと思います。この度は本当にありがとうございました。

行動規範の内容に同意します

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

VBA
1    StartRow = 3   'お好みの開始行
2    EndRow = 10000 'お好みの最終行
3
4    Lower = StartRow
5    Upper = StartRow
6
7For rw = StartRow To EndRow
8
9    If Cells(rw,1).value like "*集計*" Then
10
11            Target2Low = Cells(Lower, 2).Address
12            Target3Low = Cells(Lower, 3).Address
13                       
14            Target2Up = Cells(Upper, 2).Address
15            Target3Up = Cells(Upper, 3).Address
16            
17            Target2 = "=sum(" & Target2Low & ":" & Target2up & ")"
18            Target3 = "=sum(" & Target3Low & ":" & Target3Up & ")"
19            
20            Cells(rw,2) = Target2
21            Cells(rw.3) = Target3
22        
23        Lower = rw + 1
24
25    End If
26
27    Upper = rw
28    
29Next