dynamic range summation

M

mr.nikon

New Member
Joined
Sep 9, 2011
Messages
6
Hello,

I have a report with dynamic ranges and I'm trying to subtotal the ranges. Similar to the table below.
Code: 
         AQ	AR	AS	AT
1	Job 1231			
2	start			
3		11	13	33
4		-1	0	129
5		97	45	96
6	subtotal			
7	start			
8		45	55	-15
9		0	0	1
10	subtotal



Here is an example equation:
where s should equal "start" and r should equal "subtotal"

=SUM(AR" & Trim(Str(s)) & ":AR" & Trim(Str(r - 1)) & ")

However, when I run the report, the equation will provides me with: =SUM(AR2:AR5) and =SUM(AR2:AR9). How can I get the equation to begin with each "start"?
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
Can you post the rest of your code?

You can loop through your range looking for instances of start. Take the row number of this instance for the formula and then move onto the next etc etc
 
Upvote 0
Welcome to MrExcel.

Try:

Code: 
Sub Test()
    Dim Rng As Range
    Dim Cell As Range
    Dim s As Long
    Dim r As Long
    Set Rng = Range("AQ1:AQ" & Range("AQ" & Rows.Count).End(xlUp).Row)
    For Each Cell In Rng
        If Cell.Value = "subtotal" Then
            s = Cell.End(xlUp).Row + 1
            r = Cell.Row - 1
            Cell.Offset(, 1).Resize(, 3).Formula = "=SUM(AR" & s & ":AR" & r & ")"
        End If
    Next Cell
End Sub
 
Upvote 0
Thanks for the quick response! Below is my code.
Code: 
Option Explicit

Private Sub Workbook_Open()
    'This routine prepares the work sheet for data entry
    
    Const cTemplateDelimiter = "{*"
    Const cFormulaDelimiter = "="
    Const cMinCols = 4
    Const cMaxCols = 62
    Const cFormulaRow = "19"
    Const cMaxRows = 20000
    
    Dim r As Integer
    Dim c As Integer
    Dim s As Integer
    Dim d As Integer
    Dim temp As Variant
    Dim semp As Variant
    Dim bPrep As Boolean
    
    'Assume preparation is not needed
    bPrep = False
    
    'Check to see if preparation is required
    s = 1
    Do
        For d = 1 To cMinCols
            If InStr(1, Cells(s, d), cTemplateDelimiter, vbTextCompare) > 1 Then
                'This is the template so do not run preparation routine
                Exit Do
            End If
            If Cells(s, 1) = "start" Then
                bPrep = True
                Exit Do
            End If
        Next
        s = s + 1
    Loop Until s > cMaxRows
    
    r = 1
    Do
        For c = 1 To cMinCols
            If InStr(1, Cells(r, c), cTemplateDelimiter, vbTextCompare) > 1 Then
                'This is the template so do not run preparation routine
                Exit Do
            End If
            If Cells(r, 1) = "prep_required" Then
                bPrep = True
                Exit Do
            End If
            If Cells(r, 1) = "subtotal" Then
                bPrep = True
                Exit Do
            End If
        Next
        r = r + 1
    Loop Until r > cMaxRows
            
    'This routine (if required) prepares the sheet for editing
    If bPrep Then
    s = 1
        Do
            For d = 1 To cMaxCols
                semp = Cells(s, d)
                If Left(semp, 1) = cFormulaDelimiter Then
                    'This cell contains a formula that may need to be converted
                    semp = Replace(semp, cFormulaRow, Trim(Str(s)), , , vbTextCompare)
                End If
                Cells(s, d) = semp
            Next
            If Cells(s, 1) = "start" Then
                Cells(s, 1) = ""
                Exit Do
            End If
            s = s + 1
        Loop Until s > cMaxRows
            
    r = 1
        Do
            For c = 1 To cMaxCols
                temp = Cells(r, c)
                If Left(temp, 1) = cFormulaDelimiter Then
                    'This cell contains a formula that may need to be converted
                    If InStr(1, temp, cFormulaRow, vbTextCompare) > 1 Then
                        'Replace the formula row with the current row
                        temp = Replace(temp, cFormulaRow, Trim(Str(r)), , , vbTextCompare)
                    End If
                End If
                Cells(r, c) = temp
            Next
            If Cells(r, 1) = "subtotal" Then
                Cells(r, 44) = "=SUM(AR" & Trim(Str(s)) & ":AR" & Trim(Str(r - 1)) & ")"
                Cells(r, 46) = "=SUM(AT13:AT" & Trim(Str(r - 1)) & ")"
                Cells(r, 48) = "=SUM(AV13:AV" & Trim(Str(r - 1)) & ")"
                Cells(r, 52) = "=SUM(AZ13:AZ" & Trim(Str(r - 1)) & ")"
                Cells(r, 54) = "=SUM(BB13:BB" & Trim(Str(r - 1)) & ")"
                Cells(r, 56) = "=SUM(BD13:BD" & Trim(Str(r - 1)) & ")"
                Cells(r, 60) = "=SUM(BH13:BH" & Trim(Str(r - 1)) & ")"
                Cells(r, 62) = "=SUM(BJ13:BJ" & Trim(Str(r - 1)) & ")"
                Cells(r, 1) = ""
            End If
            If Cells(r, 1) = "prep_required" Then
                'Set the Earned Revenue formula
                Cells(5, 22) = "=(Z" & Trim(Str(r)) & "-" & "F" & Trim(Str(r)) & "-" & "H" & Trim(Str(r)) & ")/" & "BD" & Trim(Str(r))
                'Set the Variance formulas
                Cells(2, 46) = "=SUM(AR13:AR" & Trim(Str(r - 1)) & ")"
                Cells(3, 46) = "=SUM(AT13:AT" & Trim(Str(r - 1)) & ")"
                'Set the Forecast to Complete formulas
                Cells(2, 50) = "=SUM(AW13:AW" & Trim(Str(r - 1)) & ")"
                Cells(4, 50) = "=SUM(AY13:AY" & Trim(Str(r - 1)) & ")"
                Cells(5, 50) = "=SUM(AZ13:AZ" & Trim(Str(r - 1)) & ")"
                'Set the New Forecast at Completion formulas
                Cells(2, 56) = "=SUM(BD13:BD" & Trim(Str(r - 1)) & ")"
                Cells(4, 56) = "=BD3/L4"
                Cells(6, 56) = "=Z" & Trim(Str(r)) & "/" & "BD" & Trim(Str(r))
                'Set the Change in Forecast formulas
                Cells(2, 62) = "=SUM(BH13:BH" & Trim(Str(r - 1)) & ")"
                Cells(3, 62) = "=SUM(BJ13:BJ" & Trim(Str(r - 1)) & ")"
                'Set the Total formulas
                Cells(r, 32) = "=SUM(AF13:AF" & Trim(Str(r - 1)) & ")/2 "
                Cells(r, 36) = "=SUM(AJ13:AJ" & Trim(Str(r - 1)) & ")/2"
                Cells(r, 38) = "=SUM(AL13:AL" & Trim(Str(r - 1)) & ")/2"
                Cells(r, 42) = "=SUM(AP13:AP" & Trim(Str(r - 1)) & ")/2"
                Cells(r, 44) = "=SUM(AR13:AR" & Trim(Str(r - 1)) & ")"
                Cells(r, 46) = "=SUM(AT13:AT" & Trim(Str(r - 1)) & ")"
                Cells(r, 48) = "=SUM(AV13:AV" & Trim(Str(r - 1)) & ")"
                Cells(r, 52) = "=SUM(AZ13:AZ" & Trim(Str(r - 1)) & ")"
                Cells(r, 54) = "=SUM(BB13:BB" & Trim(Str(r - 1)) & ")"
                Cells(r, 56) = "=SUM(BD13:BD" & Trim(Str(r - 1)) & ")"
                Cells(r, 58) = "=Z" & Trim(Str(r)) & "/" & "BD" & Trim(Str(r))
                Cells(r, 60) = "=SUM(BH13:BH" & Trim(Str(r - 1)) & ")"
                Cells(r, 62) = "=SUM(BJ13:BJ" & Trim(Str(r - 1)) & ")"
                Cells(r, 1) = ""
                Exit Do
            End If
            r = r + 1
        Loop Until r > cMaxRows
    End If
    
End Sub
 
Upvote 0
Andrew-

I've tried to modify my code to match what you've posted but I'm still having some trouble.
I haven't done much with VBA, so this is new to me.

If anyone can provide any more input that would be great.

Thanks!
 
Upvote 0
Ok. That's weird.

When I run my report each section still starts at AR1 instead of ex. Section 1 AR1, Section 2 AR15, etc.
However, the sections end at the correct value...
 
Upvote 0
This is what I get:

<TABLE style="WIDTH: 192pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=256 x:str><COLGROUP><COL style="WIDTH: 48pt" span=4 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17 width=64>Job 1231</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 width=64> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17>start</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23 height=20> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num>11</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num>13</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num>33</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23 height=20> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num>-1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num>129</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23 height=20> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num>97</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num>45</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num>96</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17>subtotal</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num x:fmla="=SUM(B3:B5)">107</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num x:fmla="=SUM(C3:C5)">58</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num x:fmla="=SUM(D3:D5)">258</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17>start</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23 height=20> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num>45</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num>55</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num>-15</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23 height=20> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num>1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17>subtotal</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num x:fmla="=SUM(B8:B9)">45</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num x:fmla="=SUM(C8:C9)">55</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num x:fmla="=SUM(D8:D9)">-14</TD></TR></TBODY></TABLE>
 
Upvote 0
Ok.

This is what I'm getting:
Code: 
Job 1231			
start			
                11	 13      33
                -1	   0	 129
                97	 45	   96
subtotal     107	 58	  258
start			
                45	55	-15
                 0	0	1
subtotal	259	171	502
I'll run the report for a different job and see what happens. But I keep seeing the same outcome.
 
Last edited:
Upvote 0
Looking at my existing code I posted above, what changes should I make to provide with the output that you are getting?
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Excel VBA - Sort & Get Totals - Logic
Replies
4
Views
418
STARL0RD
S
gtd526
  • Solved
Move selected row to top of range
Replies
6
Views
367
gtd526
gtd526
K
  • Solved
Dynamic Sum Range Addresses...(?)
Replies
2
Views
504
Kenny_D
K
S
  • Solved
Worksheet Change Event
Replies
3
Views
585
sspatriots
S
C
  • Question Question
SUMIFS EOM
Replies
2
Views
283
ChetanPuri
C

Forum statistics

Threads
1,224,552
Messages
6,179,486
Members
452,917
Latest member
MrsMSalt

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top