[SOLVED] Copy a formula down a dynamic column (without header)

Copy a formula down a dynamic column (without header)

I have table which will be updated on a regular basis. As there will be columns inserted, I don't have a fixed range.

I found a way to look for a certain Value ("x") and select the cell below. I managed to update all necessary formulas and print them in cell below the header.
It works until the point of copying.

I tried to copy the formula down the column:

Sub Sum_three_months()

    Set Three_months = Range("A1:ZZ10000").Find("x")
    Three_months.Select
    FormularCell = ActiveCell.Offset(1, 0).Select
    Selection.Resize(Selection.Rows.Count, _
    Selection.Columns.Count).Select

    ActiveCell.FormulaR1C1 = "=SUM(R[-0]C[-4]:R[-0]C[-2])"
    
    Sum_six_months ' starts the next update, but has the same format as above
End Sub

I tried Autofill and FillDown but with Autofill I get an error with the range/selected cell and with Filldown it just copies the header.

Solution

Try this code, please:

Sub Sum_three_months()
  Dim sh As Worksheet, Three_months As Range, rngLast As Range, x As String, lastCol As Long
 
  Set sh = ActiveSheet 'use here your sheet
  lastCol = sh.Cells(1, Columns.Count).End(xlToLeft).Column
  x = "x" 'use here your search string...
  Set Three_months = sh.Range(sh.Cells(1, 1), sh.Cells(1, lastCol)).Find(x)
  If Not Three_months Is Nothing Then
      Set rngLast = sh.Cells(sh.Cells(Rows.count, Three_months.Offset(0, -4).Column).End(xlUp).row, Three_months.Column)
    
      sh.Range(Three_months.Offset(1, 0), rngLast).formula = _
                   "=SUM(" & sh.Range(Three_months.Offset(1, -4), Three_months.Offset(1, -2)).address(0, 0) & ")"
  Else
     MsgBox "Not a ""x"" range could be found...": Exit Sub
  End If
    
 Sum_six_months ' starts the next update, but has the same format as above
End Sub