How to insert dynamic formulas in between blank rows?

Question

*** Edit 7/19 @AcsErno The formula to sum works fine, but since there's 8 rows, it enters the sum in each blank row. Is there a way to enter more than 1 formula into the code?

I've tried to copy the formula (Cells(LastRow + 2, j).FormulaLocal...) and change the +1 to +2 (and so forth), but only the first blank row sums the range desired, the other ones either sum/equal to the row above it.

Updated Excel

'sum inbetween blanks
    finalRow = Cells(Worksheets("page1").Rows.Count, 1).End(xlUp).Row
    For Each j In Array(12, 13, 14) 'original: For j = 1 To finalCol

        For i = finalRow + 1 To 1 Step -1
            If IsEmpty(Cells(i, j)) Then
                If IsEmpty(Cells(i - 2, j)) Then
                    firstrow = i - 1
                    LastRow = firstrow
                Else
                    LastRow = i - 1
                    firstrow = Cells(i - 1, j).End(xlUp).Row
                End If
                Cells(LastRow + 1, j).FormulaLocal = _
                    "= sum(" & Range(Cells(firstrow, j), Cells(LastRow, j)).Address(False, False) & ")"
                Cells(LastRow + 2, j).FormulaLocal = _
                    "= sum(" & Range(Cells(firstrow, j), Cells(LastRow, j)).Address(False, False) & ")"
            End If
        Next i
    Next j

    Application.ScreenUpdating = True

If it helps, here are some of the formulas I'll be using:

=SUMIF(P138:P158,"<>*Hold*",L138:L158)
=SUM(SUMIF(H5:H21,{"China"},L5:L21)) 
=SUM(SUMIF(H5:H21,{"Other"},L5:L21)) 
=SUM(SUMIF(O12:O28,{"*H1*"},L12:L28))
=SUM(SUMIF(O12:O28,{"H2","H2-PRESSED"},L12:L28))

Link to Sum Code Link to Enter Blank Rows

My data is separated with 8 blank rows for each different week number with 8 blank rows. I need to insert formulas that sum specific things within each week/between the blanks.

The amount of rows will be dynamic, so the formulas need to be too. The only code I used to sum only works well if it has 1 blank row in between, (not 8), and I'm not sure how to add more rows/formulas with it.

Here is what the excel looks like (shortened version)

Here is what I'm trying to make it look like

'insert blank columns based on change in wk
    Dim X As Long, LastRow As Long
    Const DataCol As String = "A"
    Const StartRow = 2
    LastRow = Cells(Rows.Count, DataCol).End(xlUp).Row
    For X = LastRow To StartRow + 1 Step -1
        If Cells(X, DataCol).Value <> Cells(X - 1, DataCol) Then Rows(X).Resize(8).Insert
    Next

    finalRow = Cells(Worksheets("page1").Rows.Count, 1).End(xlUp).Row
    finalCol = Cells(1, Worksheets("page1").Columns.Count).End(xlToLeft).Column
    For j = 12 To 14 'original: For j = 1 To finalCol
        For i = finalRow + 1 To 1 Step -1
            If IsEmpty(Cells(i, j)) Then
                If IsEmpty(Cells(i - 2, j)) Then
                    firstrow = i - 1
                    LastRow = firstrow
                Else
                    LastRow = i - 1
                    firstrow = Cells(i - 1, j).End(xlUp).Row
                End If
                Cells(i, j) = Application.WorksheetFunction.Sum(Range(Cells(firstrow, j), Cells(LastRow, j)))
            End If
        Next i
    Next j

AcsErno AcsErno · Accepted Answer · 2019-07-19T06:56:11

It is not clear why you are changing firstrow in every loop. Also, why are you overwriting original values in the column. If you have a static table and want to summarize a column underneath, simply identify the first and the last row (as you correctly do), and

Cells(LastRow + 1, j) = Application.WorksheetFunction.Sum(Range(Cells(firstrow, j), Cells(LastRow, j)))

or you can insert a formula

Cells(LastRow + 1, j).FormulaLocal = _
          "=sum(" & Range(Cells(firstrow, j), Cells(LastRow, j)).Address(False, False) & ")"

You can also consider SUMIF to add H1 and H2 categories only.

It is also not clear why you are summarizing string columns. It makes no sense. You know exactly where the numeric columns are so you can specify the column numbers. I suggest option 1:

For j = 6 To 8

or option 2:

For Each j in Array (6,7,8)  ' it is more flexible

How to insert dynamic formulas in between blank rows?

1 Answers