VBA to fill the different range with formula depends upon row of header value ends

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

Use a pivot table

You are welcome. Thanks for marking the thread as solved.


Regards