VBA Loop Using Specific Range with Formula to Every Nth Column

[kenkwang]

Hello,

Please see my novice VBA approach (disclaimer: I found a similar post and use the VBA script)
Trying to copy an existing range set based on a set of formula calculation. On screen shot below, see Column C which is the desired copy range.
Cell C5 is by default a value cell of 1 only while in Cell C6 there is a formula which is adding C5+J6, Cell C7 formula is C6+J7, etc.

Would like to paste that range to corresponding columns with same size and formula set up.
So when pasting to Column M, for Cell M6 the formula in the cell should be M5+T6, Cell M7 formula should be M6+T7.
Unfortunately, my VBA script only copy/paste just the value.

Advanced apologies if my above query is unclear.

And advanced thanks for anyone who can help me.

Sub PCB_BOM_Column()
'
' Macro4 Macro
'

Worksheets("TT PCB BOM").Activate
Range("C6").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=R[-1]C+RC[7]"
Range("C6").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.FillDown
Calculate

Dim cpval As Range
Dim lastRow As Long

With Worksheets("TT PCB BOM")
lastRow = .Cells(Rows.Count, "C").End(xlUp).Row
Set cpval = .Range("C5:C" & lastRow)
For colx = 13 To 3550 Step 10
.Range(.Cells(5, colx), .Cells(lastRow, colx)).Value = cpval.Value
Next

End With
Calculate
End Sub

 

[DanteAmor]

Try this:

Sub PCB_BOM_Column()
  Dim sh As Worksheet
  Dim lr As Long, j As Long
  
  Set sh = Sheets("TT PCB BOM")
  lr = sh.Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row

  For j = 3 To 3550 Step 10
    With sh.Range(sh.Cells(6, j), sh.Cells(lr, j))
      .Formula = "=" & .Cells(1).Offset(-1).Address(0, 0) & "+" & .Cells(1).Offset(, 7).Address(0, 0)
    End With
  Next
End Sub

If you want to replace the formulas with the values use this:

Sub PCB_BOM_Column()
  Dim sh As Worksheet
  Dim lr As Long, j As Long
  
  Set sh = Sheets("TT PCB BOM")
  lr = sh.Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row

  For j = 3 To 3550 Step 10
    With sh.Range(sh.Cells(6, j), sh.Cells(lr, j))
      .Formula = "=" & .Cells(1).Offset(-1).Address(0, 0) & "+" & .Cells(1).Offset(, 7).Address(0, 0)
      .Value = .Value
    End With
  Next
End Sub

 

[kenkwang]

Dear Mr. DanteAmor,

Wow! Thank you. Much appreciated. Let me try to understand your coding methodology.
Definitely saved me a lot of headache! Very appreciative of your help.

Regards,

Ken