VBAでのループ上書き処理について

前提・実現したいこと

資金繰りのVBAを作成しています。
知人から送られてきたプログラムの続きを作成しているのですが、理解が追い付いておらず作成が思うようにすすんでおりません。

実現したいこととしては、画像にあるHOMEシートの更新ボタンを押すと口座シートの科目に沿って値が反映されて計算してくれるというものです。
また、HOMEシートの支出・収益の項目は追加されることがあります。

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

HOMEシートの計算式を呼び出す文と、支出の中の項目数(通信費～消耗品費(動的にあとから追加される分含め))＋1行空けて収益の中の項目数(これもあとから追加されます)＋1行空けて集計というfor文の書き方がわかりません。

該当のソースコード

VBA
1Sub Auto_Open()
2    'Deleteキーを押したときに、特定の関数を実行
3    Application.OnKey "{DEL}", "PressDelKey"
4End Sub
5
6Sub Auto_Close()
7    'Deleteキーを押した際の関数実行を中止
8    Application.OnKey "{DEL}"
9End Sub
10
11Private Sub PressDelKey()
12
13    'セルを指定している時だけ警告を表示
14    If UCase$(TypeName(Selection)) = "RANGE" Then
15    End If
16    
17End Sub
18' 行を追加
19Sub InsertCategory(categoryName As String, categoryType As String)
20With Sheets("HOME")
21
22   Dim InsertRow As Integer
23   
24        Dim i As Integer
25        
26             For i = 7 To .Cells(Rows.Count, 1).End(xlUp).Row
27        
28             If .Cells(i, 1).Value = categoryType Then
29        
30                
31               InsertRow = .Cells(i + 1, 2).End(xlDown).Row + 1
32                    
33            Exit For
34        
35            End If
36        
37        Next
38        
39            
40      
41        .Range(.Cells(InsertRow, 1), .Cells(InsertRow, 14)).Insert CopyOrigin:=xlFormatFromLeftOrAbove
42        .Range(.Cells(InsertRow, 1), .Cells(InsertRow, 14)).Value(xlRangeValueXMLSpreadsheet) = .Range(.Cells(InsertRow - 1, 1), .Cells(InsertRow - 1, 14)).Value(xlRangeValueXMLSpreadsheet)
43        .Cells(InsertRow, 2).Value = categoryName
44         
45         
46    
47End With
48 
49End Sub
50
51Sub sample7()
52Call InsertCategory("テスト", "支出")
53End Sub
54
55Sub sample5(categoryRow As Integer)
56    
57    Dim calcstr As String
58    
59    Dim i As Integer
60    Dim j As Integer
61    
62    With Sheets("銀行情報")
63    For i = 1 To 12
64        
65              If i <= 2 Then
66              
67                    calcstr = "=SUMIFS(" & .Cells(4, 1).Value & "!" & Cells(5, 8).Address(False, False) & ":" & Cells(Rows.Count, 8).Address(False, False) & "," & _
68                    .Cells(4, 1).Value & "!" & Cells(5, 5).Address(False, False) & ":" & Cells(Rows.Count, 5).Address(False, False) & "," & _
69                    Sheets("HOME").Cells(categoryRow, 2).Address(False, False) & "," & _
70                    .Cells(4, 1).Value & "!" & Cells(5, 3).Address(False, False) & ":" & Cells(Rows.Count, 3).Address(False, False) & "," & _
71                    """>=" & 9 + i & "/01""," & _
72                    .Cells(4, 1).Value & "!" & Cells(5, 3).Address(False, False) & ":" & Cells(Rows.Count, 3).Address(False, False) & "," & _
73                    """<" & 10 + i & "/01"")"
74                
75                For j = 5 To .Cells(Rows.Count, 1).End(xlUp).Row
76                
77                    calcstr = calcstr & "+SUMIFS(" & .Cells(j, 1).Value & "!" & Cells(5, 8).Address(False, False) & ":" & Cells(Rows.Count, 8).Address(False, False) & "," & _
78                    .Cells(j, 1).Value & "!" & Cells(5, 5).Address(False, False) & ":" & Cells(Rows.Count, 5).Address(False, False) & "," & _
79                    Sheets("HOME").Cells(categoryRow, 2).Address(False, False) & "," & _
80                    .Cells(j, 1).Value & "!" & Cells(5, 3).Address(False, False) & ":" & Cells(Rows.Count, 3).Address(False, False) & "," & _
81                    """>=" & 9 + i & "/01""," & _
82                    .Cells(j, 1).Value & "!" & Cells(5, 3).Address(False, False) & ":" & Cells(Rows.Count, 3).Address(False, False) & "," & _
83                    """<" & 10 + i & "/01"")"
84                
85                Next
86                
87                
88            ActiveSheet.Cells(categoryRow, 2 + i).Value = calcstr
89                        
90               ElseIf i = 3 Then
91                 
92                     calcstr = "=SUMIFS(" & .Cells(4, 1).Value & "!" & Cells(5, 8).Address(False, False) & ":" & Cells(Rows.Count, 8).Address(False, False) & "," & _
93                     .Cells(4, 1).Value & "!" & Cells(5, 5).Address(False, False) & ":" & Cells(Rows.Count, 5).Address(False, False) & "," & _
94                      Sheets("HOME").Cells(categoryRow, 2).Address(False, False) & "," & _
95                    .Cells(4, 1).Value & "!" & Cells(5, 3).Address(False, False) & ":" & Cells(Rows.Count, 3).Address(False, False) & "," & _
96                    """>=" & 9 + i & "/01""," & _
97                    .Cells(4, 1).Value & "!" & Cells(5, 3).Address(False, False) & ":" & Cells(Rows.Count, 3).Address(False, False) & "," & _
98                      """<=" & 9 + i & "/31"")"
99                
100                For j = 5 To .Cells(Rows.Count, 1).End(xlUp).Row
101                
102                    calcstr = calcstr & "+SUMIFS(" & .Cells(j, 1).Value & "!" & Cells(5, 8).Address(False, False) & ":" & Cells(Rows.Count, 8).Address(False, False) & "," & _
103                    .Cells(j, 1).Value & "!" & Cells(5, 5).Address(False, False) & ":" & Cells(Rows.Count, 5).Address(False, False) & "," & _
104                    Sheets("HOME").Cells(categoryRow, 2).Address(False, False) & "," & _
105                    .Cells(j, 1).Value & "!" & Cells(5, 3).Address(False, False) & ":" & Cells(Rows.Count, 3).Address(False, False) & "," & _
106                       """>=" & 9 + i & "/01""," & _
107                     .Cells(j, 1).Value & "!" & Cells(5, 3).Address(False, False) & ":" & Cells(Rows.Count, 3).Address(False, False) & "," & _
108                     """<=" & 9 + i & "/31"")"
109                
110                Next
111                
112                
113              ActiveSheet.Cells(categoryRow, 2 + i).Value = calcstr
114                        
115                        
116             ElseIf i >= 4 Then
117             
118                      calcstr = "=SUMIFS(" & .Cells(4, 1).Value & "!" & Cells(5, 8).Address(False, False) & ":" & Cells(Rows.Count, 8).Address(False, False) & "," & _
119                      .Cells(4, 1).Value & "!" & Cells(5, 5).Address(False, False) & ":" & Cells(Rows.Count, 5).Address(False, False) & "," & _
120                     Sheets("HOME").Cells(categoryRow, 2).Address(False, False) & "," & _
121                     .Cells(4, 1).Value & "!" & Cells(5, 3).Address(False, False) & ":" & Cells(Rows.Count, 3).Address(False, False) & "," & _
122                     """>=" & i - 3 & "/01""," & _
123                     .Cells(4, 1).Value & "!" & Cells(5, 3).Address(False, False) & ":" & Cells(Rows.Count, 3).Address(False, False) & "," & _
124                      """<" & i - 2 & "/01"")"
125                
126                For j = 5 To .Cells(Rows.Count, 1).End(xlUp).Row
127                
128                    calcstr = calcstr & "+SUMIFS(" & .Cells(j, 1).Value & "!" & Cells(5, 8).Address(False, False) & ":" & Cells(Rows.Count, 8).Address(False, False) & "," & _
129                    .Cells(j, 1).Value & "!" & Cells(5, 5).Address(False, False) & ":" & Cells(Rows.Count, 5).Address(False, False) & "," & _
130                     Sheets("HOME").Cells(categoryRow, 2).Address(False, False) & "," & _
131                   .Cells(j, 1).Value & "!" & Cells(5, 3).Address(False, False) & ":" & Cells(Rows.Count, 3).Address(False, False) & "," & _
132                    """>=" & i - 3 & "/01""," & _
133                    .Cells(j, 1).Value & "!" & Cells(5, 3).Address(False, False) & ":" & Cells(Rows.Count, 3).Address(False, False) & "," & _
134                    """<" & i - 2 & "/01"")"
135                
136                Next
137                
138                
139                  ActiveSheet.Cells(categoryRow, 2 + i).Value = calcstr
140                        
141             End If
142             
143         Next
144         
145     End With
146      
147End Sub
148
149Sub sample6()
150
151    Call sample5(ActiveCell.Row)
152    
153End Sub
154 
155