Inserting formula into cell between ranges-CodePudding

My data is set up like this:

What I would like to do is to have formulas inserted between the ranges of data separated by 3 spaces. For example, in the date (second) column, I would like to insert the sum of the data range between the empty rows, and then copy that formula into the next date column (fifth).

I have been working on the VBA for this, but I am stuck on how to find the data ranges between the empty rows. What the code currently does is loop through the rows, starting with the second row down to the last row. Then, it checks to see if the current cell or next cell is empty, and will skip to the next iteration if the row it is checking is not immediately after a data row. So the only part I am missing is inserting the formula into the data ranges. The code should skip to every date column with the for step of 3.

I would like to apply the formula until the last column.

Any help is greatly appreciated.

    Sub WorkInProgress2()
    Dim lCol As Long
    Dim lRow As Long
    Dim ColNum As Integer
    Dim i As Integer
    Dim j As Integer
    Dim k As Integer

    lRow = Cells(Rows.Count, 1).End(xlUp).Row
    For i = 2 To lRow


    If Range("A" & i) = "" Then
        j = i   1
        If Range("A" & j) = "" Or Range("A" & j) <> "" Then
            GoTo NextIteration
        End If

    Else
        For ColNum = 16 To lCol = Cells(1, Columns.Count).End(xlToLeft).Column Step 3
            Range(Cells(i, ColNum)).Formula = "=sum(P... need help here"
    
            
    End If
NextIteration:
Next i

CodePudding user response：

Your attempt was too complicated with the ifs, try to minimize checks. Also, I removed several integer variables and replaced them with a TopRop integer variable to help define summing ranges. Last thing I did differently is changed the step 3 concept to a isdate(ref) check... I couldn't quite understand if you wanted to sum only the dates or only not the dates... but that's an easy fix either way.

Option Explicit
Sub Add_Subtotals()

    Dim lCol As Long
    Dim lRow As Long
    Dim ColNum As Long
    Dim TopRow As Long
    Dim I As Long

    lRow = Cells(Rows.Count, 1).End(xlUp).Row   1
    lCol = Cells(1, Columns.Count).End(xlToLeft).Column
    TopRow = 2
    
    For I = 2 To lRow
        If Range("A" & I) <> "" And Range("A" & I - 1) = "" Then
            TopRow = I
        End If
        If Range("A" & I) = "" And Range("A" & I - 1) <> "" Then
            For ColNum = 2 To lCol
                If Not IsDate(Cells(1, ColNum)) Then
                    Cells(I, ColNum).Formula = "=sum(" & Cells(I - 1, ColNum).Address & ":" & Cells(TopRow, ColNum).Address & ")"
                End If
            Next ColNum
        End If
    Next I

End Sub