• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA code copying formula down a column jumping predetermined rows


Well-Known Member
Works on the active sheet, so be careful:
Sub copyFormula()
Dim ws As Worksheet, cll As Range
Application.Calculation = xlManual
Set ws = ActiveSheet    'Sheets("Sheet1") 'this change means it works on whichever sheet is the active sheet.
Dim form1 As String
form1 = ws.Range("F6").FormulaR1C1
For Each cll In ws.Range("C6:C2533").Cells
  If cll.Value = "direct works" Then cll.Offset(, 3).FormulaR1C1 = form1
Next cll
Application.Calculation = xlAutomatic
End Sub

Peter Bartholomew

Well-Known Member
I do not wish to risk high-jacking this thread but I wonder whether anyone might like to comment on the pros and cons of this code?
Sub copyFormula()
    Dim rng As Range
    Dim c As Range
    Dim form1 As String
    Dim k As Long
    Set rng = Range("target")
    'Assumes F6 to be named "target"
    form1 = rng.FormulaR1C1
    Set c = rng
    For k = 1 To 180
        Set c = c.Offset(14)
        Set rng = Union(rng, c)
    rng.FormulaR1C1 = form1
End Sub