|
各階層のブレークを判断させれば良いようです。
[例]
Sub TEST()
Const cnsX1 = "X1"
Dim GYO As Long
Dim GYOMAX As Long
Dim GYO1 As Long
Dim GYO2 As Long
Dim GYO3 As Long
Dim GYO4 As Long
Dim strFormula As String
GYOMAX = Range("A65536").End(xlUp).Row
GYO = 1
Do While GYO <= GYOMAX
If Cells(GYO, 1).Value = cnsX1 Then GYO = GYO + 1
GYO = GYO + 1
GYO1 = GYO
Do While ((Cells(GYO, 1).Value > Cells(GYO1 - 1, 1).Value) And _
(Cells(GYO, 1).Value <> cnsX1) And _
(GYO <= GYOMAX))
GYO = GYO + 1
GYO2 = GYO
Do While ((Cells(GYO, 1).Value > Cells(GYO2 - 1, 1).Value) And _
(Cells(GYO, 1).Value <> cnsX1) And _
(GYO <= GYOMAX))
GYO = GYO + 1
GYO3 = GYO
Do While ((Cells(GYO, 1).Value > Cells(GYO3 - 1, 1).Value) And _
(Cells(GYO, 1).Value <> cnsX1) And _
(GYO <= GYOMAX))
GYO4 = GYO
GYO = GYO + 1
Loop
strFormula = "=SUMIF(R" & GYO3 & "C1:R" & GYO4 & "C1,4,R" & GYO3 & "C:R" & GYO4 & "C)"
Cells(GYO3 - 1, 2).FormulaR1C1 = strFormula
Loop
strFormula = "=SUMIF(R" & GYO2 & "C1:R" & GYO4 & "C1,3,R" & GYO2 & "C:R" & GYO4 & "C)"
Cells(GYO2 - 1, 2).FormulaR1C1 = strFormula
Loop
strFormula = "=SUMIF(R" & GYO1 & "C1:R" & GYO4 & "C1,2,R" & GYO1 & "C:R" & GYO4 & "C)"
Cells(GYO1 - 1, 2).FormulaR1C1 = strFormula
Loop
End Sub
|
|