First, you must clear content cell : S4, V19, H30, AI3 ("It might get extent...").
The code is :
Private Function GetRange(param As Range) As Range
Dim lastCol As Long, lastRow As Long
If Len(param.Offset(, 1)) = 0 Then
lastCol = param.Column
Else
With param.End(xlToRight)
If InStr(1, UCase(.Value), "TOTAL") Then lastCol = .Column - 1 Else lastCol = .Column
End With
End If
With param.Offset(, -1).End(xlDown)
If InStr(1, UCase(.Value), "TOTAL") Then lastRow = .Row - 1 Else lastRow = .Row
End With
Set GetRange = Range(param.Offset(1), param.Parent.Cells(lastRow, lastCol))
End Function
Private Sub CreateTotal(param As Range)
With param.Resize(, 1).Offset(, param.Columns.Count)
.Resize(1).Offset(-1).Value = "Total"
.Formula = "=SUM(" & param.Rows(1).Address(0, 0) & ")"
.Resize(1).Offset(.Rows.Count).Formula = "=SUM(" & .Address(0, 0) & ")"
End With
param.Resize(1, 1).Offset(param.Rows.Count, -1).Value = "Grand Total"
End Sub
Sub Test()
Dim rng As Range
Sheets("Dashboard-beforerun").Select
Set rng = GetRange(Range("C4"))
rng.Formula = "=SUMIFS(Rawdata!$G:$G,Rawdata!$D:$D,$B5,Rawdata!$A:$A,C$4)-SUMIFS(Rawdata!$I:$I,Rawdata!$C:$C,$A5,Rawdata!$A:$A,C$4)"
CreateTotal rng
Set rng = GetRange(Range("C19"))
rng.Formula = "=SUMIFS(Rawdata!$H:$H,Rawdata!$C:$C,$B20)"
CreateTotal rng
Set rng = GetRange(Range("C30"))
rng.Formula = "=SUMIFS(Rawdata!$I:$I,Rawdata!$E:$E,$B31,Rawdata!$C:$C,C$30)"
CreateTotal rng
Set rng = GetRange(Range("W3"))
rng.Formula = "=COUNTIFS(Rawdata!$J:$J,$V4,Rawdata!$B:$B,W$3)"
End Sub
Bookmarks