Sum dynamic range from different tables

user_18300452 0
Viewed 55

I was wondering if you could please help me find a way to write a vba code that sums dynamic ranges in different tables.

I'm trying to sum values from different tables (whose quantity of rows will change).

The tables are all on the same sheet and look like this:

enter image description here

I've tried to write the code via macro recorder (please see below), however, I'm struggling to find as to how I can make it dynamic.

Range("B3").Select
ActiveCell.FormulaR1C1 = "=SUM(R[6]C:R[9]C)"
Range("B4").Select
ActiveCell.FormulaR1C1 = "=SUM(R[12]C:R[14]C)"
Range("B5").Select
ActiveCell.FormulaR1C1 = "=SUM(R[17]C:R[19]C)"

Thanks for your help!

dynamic vba excel sum
1 Answers

Sum Up Ranges

Option Explicit

Sub SumUpWeeks()
    
    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
    
    Dim dlCell As Range: Set dlCell = ws.Cells(ws.Rows.Count, "B").End(xlUp)
    Dim srg As Range: Set srg = ws.Range("A7", ws.Cells(dlCell.Row, "A"))
    
    Dim drg As Range: Set drg = ws.Range("A3:B5")
    
    Dim drrg As Range
    Dim fCell As Range
    Dim lCell As Range
    Dim sString As String
    Dim sIndex As Variant
    
    For Each drrg In drg.Rows
        sString = CStr(drrg.Cells(1).Value)
        ' Get week index.
        sIndex = Application.Match(sString, srg, 0)
        If IsNumeric(sIndex) Then ' week found
            ' In column 'B' skip 'Money Spent'.
            Set fCell = srg.Cells(sIndex).Offset(2, 1)
            Set lCell = ws.Range(fCell, dlCell) _
                .Find("", dlCell, xlValues, xlWhole)
            If lCell Is Nothing Then ' last week
                If dlCell.Row = fCell.Row Then ' one entry
                    drrg.Cells(2).Formula = "=" & fCell.Address
                Else ' multiple entries
                    drrg.Cells(2).Formula = "=SUM(" & fCell.Address _
                        & ":" & dlCell.Address & ")"
                End If
            Else ' not last week
                If lCell.Row = fCell.Row Then ' one entry
                    drrg.Cells(2).Formula = "=" & fCell.Address
                Else ' multiple entries
                    drrg.Cells(2).Formula = "=SUM(" & fCell.Address _
                        & ":" & lCell.Offset(-1).Address & ")"
                End If
            End If
        Else ' week not found
            drrg.Cells(2).Value = vbNullString
        End If
    Next drrg
    
    MsgBox "Weeks summed up.", vbInformation
    
End Sub
user_98140690
Related