Mayanwolfe
New Member
- Joined
- Jun 5, 2013
- Messages
- 27
Hello,
I am trying to write a macro that sums several layers of nested subtotals. The lowest level subtotals (subtotal 2) are already calculated and pulled from another tab. Subtotal 1 needs to ONLY be the sum of the individual Subtotal 2 lines. In my example below, the first subtotal 2 field should be equal to 1350. Grand total should only be the sum of the Subtotal 1 lines. The challenge is that all of the individual amounts and subtotals are all within the same column. So far, I've tried to sum by picking out the subtotal lines by color. I've indicated the colors of the various lines in parenthesis below. However, in the real version, there are even more nested subtotals, and most of them are the same color grey. Also, I'm not sure how to get it to stop when it reaches the prior group of subtotals that have already been summed.
It would seem that I need to loop outward from the value column, scanning down until "total" text is identified, then sum the values of the appropriate color above that line, but stop when the prior total line is reached. After this is complete for the range, the loop would continue outward until the Grand Total. However, this is above my current skillset. Does anyone have any ideas on how to proceed? Thanks in advance for any thoughts.
<tbody>
</tbody>
I've found a function that I've been trying to modify to make this work, but I'm not sure how to make the range dynamic enough to exclude groups of values that have been previously summed.
Option Explicit
Function SumByColor(DefinedColorRange As Range, SumRange As Range)
Application.Volatile
'Declaring variables
Dim ICol As Integer
Dim GCell As Range
'Getting the interior color of the cell
ICol = DefinedColorRange.Interior.ColorIndex
'Looping through the defined range
For Each GCell In SumRange
If ICol = GCell.Interior.ColorIndex Then
'Getting the sum of matching colored cell
SumByColor = SumByColor + GCell.Value
End If
Next GCell
End Function
I am trying to write a macro that sums several layers of nested subtotals. The lowest level subtotals (subtotal 2) are already calculated and pulled from another tab. Subtotal 1 needs to ONLY be the sum of the individual Subtotal 2 lines. In my example below, the first subtotal 2 field should be equal to 1350. Grand total should only be the sum of the Subtotal 1 lines. The challenge is that all of the individual amounts and subtotals are all within the same column. So far, I've tried to sum by picking out the subtotal lines by color. I've indicated the colors of the various lines in parenthesis below. However, in the real version, there are even more nested subtotals, and most of them are the same color grey. Also, I'm not sure how to get it to stop when it reaches the prior group of subtotals that have already been summed.
It would seem that I need to loop outward from the value column, scanning down until "total" text is identified, then sum the values of the appropriate color above that line, but stop when the prior total line is reached. After this is complete for the range, the loop would continue outward until the Grand Total. However, this is above my current skillset. Does anyone have any ideas on how to proceed? Thanks in advance for any thoughts.
| Grand Total | Subtotal 1 | Subtotal 2 | Values |
| 100 | |||
| 150 | |||
| Total (grey) | 250 (grey) | ||
| 500 | |||
| 600 | |||
| Total (grey) | 1100 (grey) | ||
| Total (brown) | (brown) | ||
| 50 | |||
| 30 | |||
| Total (grey) | 80 (grey) | ||
| 100 | |||
| Total (grey) | 100 (grey) | ||
| Total (brown) | (brown) | ||
| 150 | |||
| 150 | |||
| 0 | |||
| Total (grey) | 300 (grey) | ||
| Total (brown) | (brown) | ||
| Grand Total (dk. grey) | (dk. grey) | (dk. grey) |
<tbody>
</tbody>
I've found a function that I've been trying to modify to make this work, but I'm not sure how to make the range dynamic enough to exclude groups of values that have been previously summed.
Option Explicit
Function SumByColor(DefinedColorRange As Range, SumRange As Range)
Application.Volatile
'Declaring variables
Dim ICol As Integer
Dim GCell As Range
'Getting the interior color of the cell
ICol = DefinedColorRange.Interior.ColorIndex
'Looping through the defined range
For Each GCell In SumRange
If ICol = GCell.Interior.ColorIndex Then
'Getting the sum of matching colored cell
SumByColor = SumByColor + GCell.Value
End If
Next GCell
End Function
Last edited:
