Copy a formula down in VBA

[dragontbone]

Im trying to copy down a formula down in VBA using the following code.

Sub offsetConcatenate()

' This will add the concatenate formula


Dim row As Integer
    Dim DateCountCalculator As Integer
    Dim MinusStart As String

DateCountCalculator = Range("DateCountCalculator").Value

For row = 1 To DateCountCalculator
    
   
    
    MinusStart = Range("Start_Date_Calculator").offset(row - 1, 4).Select
    
    ActiveCell.FormulaR1C1 = _
        "=CONCAT(Start_Date_Calculator&ConcatenateDateStart&TEXT(RC[-3],""MM/DD/YYYY""))"
    
 Next row


End Sub

I want it to be dynamic enough to know when to stop copying down the formula, but I can't get the formula to copy down the list of data I have without just copying the first line all the way down the list.

 

[MARK858]

Try either

Sub offsetConcatenate()

    ' This will add the concatenate formula

    Dim DateCountCalculator As Integer
    Dim MinusStart As Range

    DateCountCalculator = Range("DateCountCalculator").Value


    
    Set MinusStart = Range("Start_Date_Calculator").Offset(-1, 4)
    
    MinusStart.Resize(Range("DateCountCalculator")).FormulaR1C1 = _
          "=OFFSET(Start_Date_Calculator,ROWS(R1C1:R[-6]C1)-1,0) & "" - "" & TEXT(R[1]C[-3],""MM/DD/YYYY"")"
    
End Sub

or

Sub offsetConcatenate2()

    ' This will add the concatenate formula

    Dim DateCountCalculator As Integer
    Dim MinusStart As Range

    DateCountCalculator = Range("DateCountCalculator").Value


    
    Set MinusStart = Range("Start_Date_Calculator").Offset(-1, 4)
    
    MinusStart.Resize(Range("DateCountCalculator")).FormulaR1C1 = _
           "=OFFSET(Start_Date_Calculator,ROWS(R1C1:R[-6]C1)-1,0) & ""  "" & R[1]C[-2] & "" "" & TEXT(R[1]C[-3],""MM/DD/YYYY"")"
    
End Sub

 

[dragontbone]

Try either

Sub offsetConcatenate()

    ' This will add the concatenate formula

    Dim DateCountCalculator As Integer
    Dim MinusStart As Range

    DateCountCalculator = Range("DateCountCalculator").Value


   
    Set MinusStart = Range("Start_Date_Calculator").Offset(-1, 4)
   
    MinusStart.Resize(Range("DateCountCalculator")).FormulaR1C1 = _
          "=OFFSET(Start_Date_Calculator,ROWS(R1C1:R[-6]C1)-1,0) & "" - "" & TEXT(R[1]C[-3],""MM/DD/YYYY"")"
   
End Sub

or

Sub offsetConcatenate2()

    ' This will add the concatenate formula

    Dim DateCountCalculator As Integer
    Dim MinusStart As Range

    DateCountCalculator = Range("DateCountCalculator").Value


   
    Set MinusStart = Range("Start_Date_Calculator").Offset(-1, 4)
   
    MinusStart.Resize(Range("DateCountCalculator")).FormulaR1C1 = _
           "=OFFSET(Start_Date_Calculator,ROWS(R1C1:R[-6]C1)-1,0) & ""  "" & R[1]C[-2] & "" "" & TEXT(R[1]C[-3],""MM/DD/YYYY"")"
   
End Sub

Both of those worked for me! Thank you so much for the help. I really appreciate it!

 

[MARK858]

You're welcome