VBA: adding irregular ranges

Question

Hi guys,

I have been using Stackoverflow for several months and, as a rule, I managed to find a solution for all my VBA tasks so far:

I need help creating a macro that adds all the values ​​in column E between the lines with the word "avg". the result should be displayed in the cells where the label "Amount is here" is displayed. Both the texts "avg" and "sum here" simply illustrate an example: "avg" can be replaced by any other word, and "sum here" should actually be an aggregation of values ​​above it.

The real problem is that the number of ranges in column E is variable, so I would like to find a macro that can handle the "n" number of ranges in column E.

, D " ".

:

        Sub Macro1()
'
' Macro1 Macro
'
Dim sumhere As Range
Dim startingpoint As Range
Dim endingpoint As Range

'
    Range("C17").Select
    Selection.End(xlDown).Select
    If ActiveCell = "avg" Then
        ActiveCell.Offset(rowoffset:=0, columnoffset:=2).Select


        Set sumhere = ActiveCell
        Set startingpoint = ActiveCell.Offset(rowoffset:=-1, columnoffset:=0)


        Selection.End(xlUp).Select
        If (ActiveCell.Value) = "Sum here" Then
        Set endingpoint = ActiveCell.Offset(rowoffset:=1, columnoffset:=0)
        sumhere.Formula = "=sum(range(startingpoint:endingpoint)"



        Else
        Selection.End(xlUp).Select
        If (ActiveCell.Value) = "Sum here" Then
        Set endingpoint = ActiveCell.Offset(rowoffset:=1, columnoffset:=0)
        sumhere.Formula = "=Sum(Range(startingpoint.adress:endingpoint.adress))"


        Else: End If
        End If

    End If

End Sub

, , , . , - " " "" = 1 x "" "... , .

!

Answer 1

, - :

Sub sums()
Dim i As Integer, j As Integer, k As Integer

j = Range("C1048576").End(xlUp).Row
k = 1

For i = 1 To j
    If Range("C" & i).Value <> "" Then
        Range("E" & i).Value = "=Sum(E" & k & ":E" & i - 1 & ")"
        k = i + 1
    End If
Next i

End Sub

Answer 2

, avg ( ) .

- ( , - ), ( , ).

6 - $E6, .

()

=SUM(OFFSET($E6,IFERROR(LOOKUP(2,1/($A$1:INDEX($A:$A,ROW()-1)<>""),ROW($A$1:INDEX($A:$A,ROW()-1))),0)-ROW()+1,,ROW()-1-IFERROR(LOOKUP(2,1/($A$1:INDEX($A:$A,ROW()-1)<>""),ROW($A$1:INDEX($A:$A,ROW()-1))),0)))

():

=SUM(INDEX($E:$E,IFERROR(LOOKUP(2,1/($A$1:INDEX($A:$A,ROW()-1)<>""),ROW($A$1:INDEX($A:$A,ROW()-1))),0)+1):INDEX($E:$E,ROW()-1)) 

---

:

B6:

=IFERROR(LOOKUP(2,1/($A$1:INDEX($A:$A,ROW()-1)<>""),ROW($A$1:INDEX($A:$A,ROW()-1))),0)

E6: (volatile)

=SUM(OFFSET($E6,$B6-ROW()+1,,ROW()-1-$B6))

():

=SUM(INDEX($E:$E,$B6):INDEX($E:$E,ROW()-1))

Edit: , UDF, , VBA.

=AddSubTotal() , , =AddSubTotal("pop",6), F (col 6), "pop", "avg".

Public Function AddSubTotal(Optional Delim As String = "avg", Optional ColNumber = 5) As Double

    Dim rCaller As Range
    Dim rPrevious As Range
    Dim rSumRange As Range

    Set rCaller = Application.Caller

    With rCaller.Parent
        Set rPrevious = .Range(.Cells(1, 1), .Cells(rCaller.Row - 1, 1)).Find(Delim, , , , , xlPrevious)
        If Not rPrevious Is Nothing Then
            Set rSumRange = rPrevious.Offset(1, ColNumber - 1).Resize(rCaller.Row - rPrevious.Row - 1)
        Else
            Set rSumRange = .Range(.Cells(1, ColNumber), .Cells(rCaller.Row - 1, ColNumber))
        End If
    End With

    AddSubTotal = WorksheetFunction.Sum(rSumRange)

End Function

Answer 3

VBA ,

• C: E
• ( )
• " ", , avg
• avg ( )

You can easily modify this procedure to also perform the average of these values, and place these results, for example, in column D

Any of the above is easy to modify.

---

Option Explicit
Sub TotalSubRanges()
    Dim vSrc As Variant, rSrc As Range
    Dim dAdd As Double
    Dim I As Long
    Const sKey As String = "avg"

Set rSrc = Range(Cells(1, "C"), Cells(Rows.Count, "C").End(xlUp)).Resize(columnsize:=3)
vSrc = rSrc

'Do the "work" in a VBA array, as this will
'  execute much faster than working directly
'  on the worksheet

For I = 1 To UBound(vSrc, 1)
    If vSrc(I, 1) = sKey Then
        vSrc(I, 3) = dAdd
        dAdd = 0
    Else
        If IsNumeric(vSrc(I, 3)) Then dAdd = dAdd + vSrc(I, 3)
    End If
Next I

'write the results back to the worksheet
' and conditionally format the "sum" cells
With rSrc
    .EntireColumn.Clear
    .Value = vSrc
    .Columns(3).AutoFit
    .EntireColumn.ColumnWidth = .Columns(3).ColumnWidth
    .FormatConditions.Delete
    .FormatConditions.Add _
        Type:=xlExpression, _
        Formula1:="=" & .Item(1, 1).Address(False, True) & "=""" & sKey & """"
        With .FormatConditions(1)
            .Interior.ColorIndex = 6
        End With
End With 

End Sub

---

Answer 4

Edit: Exceptional starting point As Range Dim Ending point as Range

To: Dim starting point as an option Dim End point as an option

Since the starting point and end point are used in the formula, you cannot define them as a range.