Copy a formula down in VBA

D

dragontbone

New Member
Joined
Sep 28, 2022
Messages
35
Office Version
  1. 365
Platform
  1. Windows
Im trying to copy down a formula down in VBA using the following code.

VBA 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.
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Sort by date Sort by votes
Taking a few guesses here but maybe...


VBA Code: 
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(row - 1, 4)
  
    Range(MinusStart, Cells(DateCountCalculator, MinusStart.Column)).FormulaR1C1 = _
          "=CONCAT(Start_Date_Calculator&ConcatenateDateStart&TEXT(RC[-3],""MM/DD/YYYY""))"
  
End Sub
 
Upvote 0
MARK858 said:
Taking a few guesses here but maybe...


VBA Code: 
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(row - 1, 4)
 
    Range(MinusStart, Cells(DateCountCalculator, MinusStart.Column)).FormulaR1C1 = _
          "=CONCAT(Start_Date_Calculator&ConcatenateDateStart&TEXT(RC[-3],""MM/DD/YYYY""))"
 
End Sub
Click to expand...
No, all it did was copy the formula up, kept the same data from the first cell, and didn't format the date right.

1664405946375.png
 
Upvote 0
dragontbone said:
kept the same data from the first cell, and didn't format the date right.
Click to expand...

Hmm, increases the cell references fine for me (see table below), can you post a screenshot of your sheet using XL2BB please and include in the post what the ranges are for DateCountCalculator and Start_Date_Calculator (you aren't doing anything in your code to increment Start_Date_Calculator).

Are the cells where you get the end date text or actual dates?

Also tell me if MinusStart in
VBA Code: 
MinusStart = Range("Start_Date_Calculator").offset(row - 1, 4).Select
is a cell or the contents of the cell.

Book1.xlsb
F
3=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C3,"MM/DD/YYYY"))
4=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C4,"MM/DD/YYYY"))
5=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C5,"MM/DD/YYYY"))
6=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C6,"MM/DD/YYYY"))
7=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C7,"MM/DD/YYYY"))
8=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C8,"MM/DD/YYYY"))
9=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C9,"MM/DD/YYYY"))
10=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C10,"MM/DD/YYYY"))
11=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C11,"MM/DD/YYYY"))
12=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C12,"MM/DD/YYYY"))
13=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C13,"MM/DD/YYYY"))
14=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C14,"MM/DD/YYYY"))
15=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C15,"MM/DD/YYYY"))
16=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C16,"MM/DD/YYYY"))
17=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C17,"MM/DD/YYYY"))
18=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C18,"MM/DD/YYYY"))
19=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C19,"MM/DD/YYYY"))
20=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C20,"MM/DD/YYYY"))
21=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C21,"MM/DD/YYYY"))
22=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C22,"MM/DD/YYYY"))
23=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C23,"MM/DD/YYYY"))
24=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C24,"MM/DD/YYYY"))
25=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C25,"MM/DD/YYYY"))
26=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C26,"MM/DD/YYYY"))
27=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C27,"MM/DD/YYYY"))
28=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C28,"MM/DD/YYYY"))
29=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C29,"MM/DD/YYYY"))
30=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C30,"MM/DD/YYYY"))
31=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C31,"MM/DD/YYYY"))
32=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C32,"MM/DD/YYYY"))
33=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C33,"MM/DD/YYYY"))
34=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C34,"MM/DD/YYYY"))
Sheet1
Cell Formulas
RangeFormula
F3:F34F3=CONCAT(Start_Date_Calculator&ConcatenateDateStart&TEXT(C3,"MM/DD/YYYY"))
Named Ranges
NameRefers ToCells
Start_Date_Calculator=Sheet1!$B$4F3:F34
 
Last edited:
Upvote 0
MARK858 said:
Hmm, increases the cell references fine for me (see table below), can you post a screenshot of your sheet using XL2BB please and include in the post what the ranges are for DateCountCalculator and Start_Date_Calculator (you aren't doing anything in your code to increment Start_Date_Calculator).

Are the cells where you get the end date text or actual dates?

Also tell me if MinusStart in
VBA Code: 
MinusStart = Range("Start_Date_Calculator").offset(row - 1, 4).Select
is a cell or the contents of the cell.

Book1.xlsb
F
3=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C3,"MM/DD/YYYY"))
4=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C4,"MM/DD/YYYY"))
5=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C5,"MM/DD/YYYY"))
6=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C6,"MM/DD/YYYY"))
7=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C7,"MM/DD/YYYY"))
8=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C8,"MM/DD/YYYY"))
9=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C9,"MM/DD/YYYY"))
10=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C10,"MM/DD/YYYY"))
11=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C11,"MM/DD/YYYY"))
12=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C12,"MM/DD/YYYY"))
13=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C13,"MM/DD/YYYY"))
14=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C14,"MM/DD/YYYY"))
15=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C15,"MM/DD/YYYY"))
16=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C16,"MM/DD/YYYY"))
17=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C17,"MM/DD/YYYY"))
18=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C18,"MM/DD/YYYY"))
19=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C19,"MM/DD/YYYY"))
20=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C20,"MM/DD/YYYY"))
21=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C21,"MM/DD/YYYY"))
22=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C22,"MM/DD/YYYY"))
23=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C23,"MM/DD/YYYY"))
24=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C24,"MM/DD/YYYY"))
25=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C25,"MM/DD/YYYY"))
26=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C26,"MM/DD/YYYY"))
27=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C27,"MM/DD/YYYY"))
28=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C28,"MM/DD/YYYY"))
29=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C29,"MM/DD/YYYY"))
30=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C30,"MM/DD/YYYY"))
31=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C31,"MM/DD/YYYY"))
32=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C32,"MM/DD/YYYY"))
33=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C33,"MM/DD/YYYY"))
34=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C34,"MM/DD/YYYY"))
Sheet1
Cell Formulas
RangeFormula
F3:F34F3=CONCAT(Start_Date_Calculator&ConcatenateDateStart&TEXT(C3,"MM/DD/YYYY"))
Named Ranges
NameRefers ToCells
Start_Date_Calculator=Sheet1!$B$4F3:F34
Click to expand...




Im not sure what XL2BB is, but below is the range names along with where they are at on the spreadsheet:

Start_Date_Calculator - B8 (Yellow Cell)
ConcatenateDateStart- D8 (Green striped Cell)
DateCalculatorRange-B8:B1000(List import list)
DateCountCalculator- Currently is I2, but all it is is a count if to count all the cells with stuff in them in the DateCalculatorRange (Dark blue striped cell)

1664410215620.png
 
Upvote 0
MARK858 said:
Hmm, increases the cell references fine for me (see table below), can you post a screenshot of your sheet using XL2BB please and include in the post what the ranges are for DateCountCalculator and Start_Date_Calculator (you aren't doing anything in your code to increment Start_Date_Calculator).

Are the cells where you get the end date text or actual dates?

Also tell me if MinusStart in
VBA Code: 
MinusStart = Range("Start_Date_Calculator").offset(row - 1, 4).Select
is a cell or the contents of the cell.

Book1.xlsb
F
3=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C3,"MM/DD/YYYY"))
4=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C4,"MM/DD/YYYY"))
5=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C5,"MM/DD/YYYY"))
6=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C6,"MM/DD/YYYY"))
7=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C7,"MM/DD/YYYY"))
8=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C8,"MM/DD/YYYY"))
9=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C9,"MM/DD/YYYY"))
10=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C10,"MM/DD/YYYY"))
11=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C11,"MM/DD/YYYY"))
12=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C12,"MM/DD/YYYY"))
13=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C13,"MM/DD/YYYY"))
14=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C14,"MM/DD/YYYY"))
15=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C15,"MM/DD/YYYY"))
16=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C16,"MM/DD/YYYY"))
17=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C17,"MM/DD/YYYY"))
18=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C18,"MM/DD/YYYY"))
19=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C19,"MM/DD/YYYY"))
20=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C20,"MM/DD/YYYY"))
21=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C21,"MM/DD/YYYY"))
22=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C22,"MM/DD/YYYY"))
23=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C23,"MM/DD/YYYY"))
24=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C24,"MM/DD/YYYY"))
25=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C25,"MM/DD/YYYY"))
26=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C26,"MM/DD/YYYY"))
27=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C27,"MM/DD/YYYY"))
28=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C28,"MM/DD/YYYY"))
29=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C29,"MM/DD/YYYY"))
30=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C30,"MM/DD/YYYY"))
31=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C31,"MM/DD/YYYY"))
32=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C32,"MM/DD/YYYY"))
33=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C33,"MM/DD/YYYY"))
34=CONCAT(Start_Date_Calculator&@ConcatenateDateStart&TEXT(C34,"MM/DD/YYYY"))
Sheet1
Cell Formulas
RangeFormula
F3:F34F3=CONCAT(Start_Date_Calculator&ConcatenateDateStart&TEXT(C3,"MM/DD/YYYY"))
Named Ranges
NameRefers ToCells
Start_Date_Calculator=Sheet1!$B$4F3:F34
Click to expand...

dragontbone said:
Im not sure what XL2BB is, but below is the range names along with where they are at on the spreadsheet:

Start_Date_Calculator - B8 (Yellow Cell)
ConcatenateDateStart- D8 (Green striped Cell)
DateCalculatorRange-B8:B1000(List import list)
DateCountCalculator- Currently is I2, but all it is is a count if to count all the cells with stuff in them in the DateCalculatorRange (Dark blue striped cell)

View attachment 74992
Click to expand...
I figured out what XL2BB is. Sorry for the confusion.

SFBillingSuperToolv7.V5.xlsm
F
8#VALUE!
Date Calculator
Cell Formulas
RangeFormula
F8F8=CONCAT(Start_Date_Calculator+row&ConcatenateDateStart&TEXT(C8,"MM/DD/YYYY"))
Named Ranges
NameRefers ToCells
ConcatenateDateStart='Date Calculator'!$D$8F8
DateCalculatorRange='Date Calculator'!$B$8:$B$1006F8
Start_Date_Calculator='Date Calculator'!$B$8F8
 
Upvote 0
Sorry for the delay as I was getting something to eat, you need to select the full range (A1:I11 by the look of it) you want copied before using XL2BB (which is why it is showing only F8) so that I can copy and paste it all into Excel
 
Upvote 0
What happens with the code below?

VBA Code: 
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") + 1).FormulaR1C1 = _
          "=OFFSET(Start_Date_Calculator,ROWS(R1C1:R[-6]C1)-1,0)&TEXT(R[1]C[-3],""MM/DD/YYYY"")"
    
End Sub
 
Upvote 0
MARK858 said:
What happens with the code below?

VBA Code: 
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") + 1).FormulaR1C1 = _
          "=OFFSET(Start_Date_Calculator,ROWS(R1C1:R[-6]C1)-1,0)&TEXT(R[1]C[-3],""MM/DD/YYYY"")"
   
End Sub
Click to expand...
No worries. I just got done with dinner myself.

The code you gave me ran slightly better. It's missing the second section. so basically I'm trying to combine 3 things together. So it'll say "Water For 1/1/2022" then "-" then "1/31/2022". It should combine to come out to be "Water for 01/01/2022-01/31/2022". Also, the dates are weird. Like it shows "Water for 6/11/2207/13/2022". Or another says "Water for 07/14/2208/09/2022". So its missing the "-" mark, and the dates run together and some of the dates show a few hundred years into the future.


SFBillingSuperToolv7.V5.xlsm
BCDEFGHI
44
5
6List Input
7Water for 06/11/2207/13/2022
8Water for 06/11/227/13/2022-Sewer for 06/11/2207/13/2022$277.75
9Sewer for 06/11/227/13/2022-Water for 07/14/2208/09/2022$261.17
10Water for 07/14/228/9/2022-Sewer for 07/14/2208/09/2022$417.43
11Sewer for 07/14/228/9/2022-01/00/1900$369.19
Date Calculator
Cell Formulas
RangeFormula
I4I4=COUNTIF(DateCalculatorRange,"*")
F7:F11F7=OFFSET(Start_Date_Calculator,ROWS($A$1:$A1)-1,0)&TEXT(C8,"MM/DD/YYYY")
Named Ranges
NameRefers ToCells
DateCalculatorRange='Date Calculator'!$B$8:$B$1006F7:F11, I4
Start_Date_Calculator='Date Calculator'!$B$8F7:F11, I4



I hope I got everything this time? Sorry for this being so weird. I appreciate the help. If anything I am learning new things.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Need help Copying down a formula to the bottom of a list in VBA
Replies
3
Views
539
Fluff
Fluff
Guzzlr
  • Solved
Not Pasting to LastRow
2
Replies
16
Views
362
MARK858
MARK858
S
  • Solved
VBA Increasing formula by 1 each time it is added
Replies
1
Views
169
silentcarl
S
S
  • Solved
Copying Formulas with Formula.R1C1 Results in Rows and Columns Incrementing by 2
Replies
5
Views
167
Alex Blakenburg
A
B
  • Question
Copy, paste based on excel sheet name
Replies
1
Views
89
eiloken
E

Forum statistics

Threads
1,217,114
Messages
6,134,722
Members
449,886
Latest member
MD SHIBLI NOMAN NEWTON

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top