CODEWITHSUNDEEP
vbaexcel
PlatiNUM
PlatiNUM

Reputation: 99

Excel VBA - Sum between two values

I have a report where I'm trying to get the sum of a dynamic number of rows in order to produce a subtotal.

If Cells(s, 1).Value = "start" Then
   If Cells(r, 1).Value = "subtotal" Then
    'Set the Monthly Subtotal Formulas
     Cells(r, 44) = "=SUM(AR" & Trim(Str(s)) & ":AR" & Trim(Str(r - 1)) & ")"
     Cells(r, 46) = "=SUM(AT" & Trim(Str(s)) & ":AT" & Trim(Str(r - 1)) & ")"
    'Set the Weekly Subtotal Formulas
     Cells(r, 48) = "=SUM(AV" & Trim(Str(s)) & ":AV" & Trim(Str(r - 1)) & ")"
     Cells(r, 52) = "=SUM(AZ" & Trim(Str(s)) & ":AZ" & Trim(Str(r - 1)) & ")"
     'Set the Daily Subtotal Formulas
     Cells(r, 54) = "=SUM(BB" & Trim(Str(s)) & ":BB" & Trim(Str(r - 1)) & ")"
     Cells(r, 56) = "=SUM(BD" & Trim(Str(s)) & ":BD" & Trim(Str(r - 1)) & ")"
     'Set the Hourly Formulas
     Cells(r, 60) = "=SUM(BH" & Trim(Str(s)) & ":BH" & Trim(Str(r - 1)) & ")"
     Cells(r, 62) = "=SUM(BJ" & Trim(Str(s)) & ":BJ" & Trim(Str(r - 1)) & ")"
     Cells(r, 1) = ""
    End If
    Cells(s, 1) = ""
End If

Basically, each work group is within the cell values "start" and "subtotal". How can I find the 's' or row number and use that in the formula?

Upvotes: 3

Views: 1423

Answers (1)

Aprillion
Aprillion

Reputation: 22340

most of the time, built-in subtotals feature of Excel should be sufficient

in case you really need to use VBA solution and don't know how to iterate it over all "subtotal" tags already present in the data, place your code inside a loop like this:

header_column = Intersect(ActiveSheet.Range("A:A"), ActiveSheet.UsedRange).Value2
s = 1
For r = 1 To UBound(header_column)
    If header_column(r, 1) = "start" Then
        s = r
    End If
    If header_column(r, 1) = "subtotal" Then
        ' ... do your stuff here ... '
        ' s = r ' if the next "start" tag always follows a subtotal tag, no need for the "start" tags at all, just uncomment this line just before End If
    End If
Next

P.S.: no need for "string" & Trim(Str(integer)), use "string" & integer instead

Upvotes: 2

Related Questions