Help with formula for dynamic value instead of static value

[MyFace2]

Hello all, first time here. I'm working on a macro to export a report from a large and varying raw data set and am stuck on this current step. I'm on Office 16, self taught, and I'm sure this is probably something fairly easy but I just can't seem to wrap my head around it. At the point I'm at, the data is collated/filtered and ready to be subtotaled. I have a piece of code written that will put a static subtotal value for the columns I need, but I would prefer a formula that sums the columns in case manual alterations need to be made after the reports are ran. I have a rough idea of what the code needs to be, but just can't quite get there. I am trying to subtotal columns H & J. I'm on a work computer and therefore can't install the mini sheet so I will just be posting screen shots of my sheet and the codes below.

The code that gives me the static values is:

'subtotal

Dim N As Long, NN As Long
N = Cells(Rows.Count, "A").End(xlUp).Row + 1
cSum = 0
DSum = 0
For NN = 1 To N
If Cells(NN, "H").Value = "" Then
Cells(NN, "H").Value = HSum
HSum = 0
Else
HSum = HSum + Cells(NN, "H").Value
End If
If Cells(NN, "J").Value = "" Then
Cells(NN, "J").Value = JSum
JSum = 0
Else
JSum = JSum + Cells(NN, "J").Value
End If

Next NN

What I'm trying to work through to give me a formula based subtotal is:

Dim Lrow As Long
Lrow = Cells(Rows.Count, "A").End(xlUp).Row + 1

X = 1

For i = 1 to Lrow

If Cells("A").Value = "Subtotal" Then
Cells("H").Formula = "=SUM(H&X:H&(i-1))
End if
X = Subtotal Row +1
Loop

Any help would be greatly appreciated! Thanks!!

 

[shknbk2]

It looks like you had the majority of the code in place. Just a few tweaks. X goes inside the IF statement so that it resets to the next section's top row only after the Subtotal cell has been found.

Dim Lrow As Long
Dim X As Integer
Dim i As Integer

Lrow = Cells(Rows.Count, "A").End(xlUp).Row + 1
X = 1
For i = 1 To Lrow
    If Cells(i, "A").Value = "Subtotal" Then
        Cells(i, "H").Formula = "=SUM(H" & X & ":H" & i - 1 & ")"
        X = i + 1
    End If
Next

 

[MyFace2]

thank you so much! worked perfectly.