Modifying a macro generated formula

My VBA macro produces a formula and puts it into a cell. I want to fill the 5 cells below the cell that the formula is put in with the same formula, but with the row references increased by one. So that: the originally produced formula D15 = D24+D49+D82+D116+D144+D179 gets put into D16 as D16 = D25+D50+D83+D117+D145+D180 and so on. I am stuck.

The code below produces the formula D15 = D24+D49+D82+D116+D144+D179. I tried +1 to the func variable but that failed. I tried turning the function into a text string but only ended up with a "+1" at the end each reference in the string. I got the string split into array variables but got lost trying to modify them. I am probably missing the simple and obvious. Any pointers to push me in the right direction are greatly appreciated.

Sub AA()

Dim thej As Range

Dim result As String
result = ""
Dim vvv As String
vvv = "Other"


For Each thej In Range("A20:A200")    
    If thej.Value = vvv Then
    thej.Activate
    ActiveCell.Offset(0, 3).Activate
    
    func = ActiveCell.Address(RowAbsolute:=False, ColumnAbsolute:=False)
    

    If result = "" Then
            result = "=" & func
    Else
     result = result & "+" & func
    End If
    End If
    
Next thej

ActiveSheet.Range("D15").Formula = result

'figure out how to add 1 to cell references and put in next cell D# down
        
End Sub

Solution

Just allow Excel doing its job:

Sub AA()
  [D15:D19].Formula = "=SUMIF($A$20:$A$200,""=Other"",D20:D200)"
End Sub

I've squeezed the target range by the last cell (D20) to avoid the possible circular reference (it's OP's unintentional error I guess).

This formula dynamically sums the D range while the requested formula is static.

If you still want to get static formulas, use:

Sub AA()
  [D15:D19].Formula = "=" & Evaluate("TEXTJOIN(""+"",TRUE,IF(A20:A200=""Other"",ADDRESS(ROW(D20:D200),4,4),""""))")
End Sub

It does exactly that what you requested.