• 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

    Hui...

  • 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

Inun

New Member
Please help with VBA excel code .I have formula in excel sheet in cell F6 want to copy same formula down that same F column after every 14th row the last formula will be inserted in in cell F2533 .Thanks for your help in advance .


▬▬▬▬▬▬▬▬▬ Mod edit : thread moved to appropriate forum !
 

Stevie

Active Member
Hi Inun,
I have written some code to do this, but it seems starting at 6 and going every 14 rows (6,20,34...etc) ends at 2526
or going every 13 rows (6,19,32...etc) ends at 2528.

Please clarify what you want as 2533-6 is not a multiple of 14.

Either way, here is the code if you wish to modify it for your use.

Code:
Sub copyFormula()
    Application.Calculation = xlManual
    Dim ws As Worksheet
    Set ws = Sheets("Sheet1")
    Dim form1 As String
    Dim row As Double
    form1 = ws.Range("F6").formula
    For row = 6 To 2533 Step 13
        ws.Cells(row, 6).Formula = form1
    Next
    Application.Calculation = xlAutomatic
End Sub
If this was helpful, please click 'Like'!
 

p45cal

Well-Known Member
If, in Stevie's code, you replace both instances of .formula with .formular1c1 the F6 formula's absolute/relative references will be respected.
 

Inun

New Member
Hi Inun,
I have written some code to do this, but it seems starting at 6 and going every 14 rows (6,20,34...etc) ends at 2526
or going every 13 rows (6,19,32...etc) ends at 2528.

Please clarify what you want as 2533-6 is not a multiple of 14.

Either way, here is the code if you wish to modify it for your use.

Code:
Sub copyFormula()
    Application.Calculation = xlManual
    Dim ws As Worksheet
    Set ws = Sheets("Sheet1")
    Dim form1 As String
    Dim row As Double
    form1 = ws.Range("F6").formula
    For row = 6 To 2533 Step 13
        ws.Cells(row, 6).Formula = form1
    Next
    Application.Calculation = xlAutomatic
End Sub
If this was helpful, please click 'Like'![/quote
Hi Inun,
I have written some code to do this, but it seems starting at 6 and going every 14 rows (6,20,34...etc) ends at 2526
or going every 13 rows (6,19,32...etc) ends at 2528.

Please clarify what you want as 2533-6 is not a multiple of 14.

Either way, here is the code if you wish to modify it for your use.

Code:
Sub copyFormula()
    Application.Calculation = xlManual
    Dim ws As Worksheet
    Set ws = Sheets("Sheet1")
    Dim form1 As String
    Dim row As Double
    form1 = ws.Range("F6").formula
    For row = 6 To 2533 Step 13
        ws.Cells(row, 6).Formula = form1
    Next
    Application.Calculation = xlAutomatic
End Sub
If this was helpful, please click 'Like'!
Hi Stevie
Sorry late response the steps is indeed 13 but the last cell where the formula needs to be inserted is 2533
I pasted the code and am getting run time error 9 saying subscript out of range
Thanks for a quick response

The other thing my sheet is sheet38 but the tab called FCW .Do I need to write FWC or sheet38 in in this -Set ws = Sheets("Sheet1")
 

Inun

New Member
If, in Stevie's code, you replace both instances of .formula with .formular1c1 the F6 formula's absolute/relative references will be respected.
Hi

Thanks for your response
I have tried that getting the same error message
run time error 9 saying subscript out of range
Thanks for a quick response
How do I get round this ?
 

Stevie

Active Member
Hi Inun,
I still don't understand which cells need the formula and I would imagine the error is to do with something about your sheet which I don't know about.
Please upload your workbook as per the forum suggested best practice.

Stevie
 

Marc L

Excel Ninja
How do I get round this ?
Without a complete explanation of the need neither an attachment
(as per forum rules), just activate the Macro recorder and operate
manually at least for a couple of destination cells then post here
your generated code using the appropriate code icon …
 

Inun

New Member
will try manual and if this do not works I may send the attachment ( sensitive info will see if this will be allowed )
Thanks Marc
 

Inun

New Member
Hi Inun,
I still don't understand which cells need the formula and I would imagine the error is to do with something about your sheet which I don't know about.
Please upload your workbook as per the forum suggested best practice.

Stevie
The formula is in F6 and that needs going into F20 , F34 up to F2533
Thanks Stevie
 

Marc L

Excel Ninja
will try manual and if this do not works I may send the attachment ( sensitive info will see if this will be allowed )
No sensitive data in any attachment !

Better is you just first operate manually.
As if it can't work manually so it can't neither work by code …
 

p45cal

Well-Known Member
Code:
Sub copyFormula()
    Application.Calculation = xlManual
    Dim ws As Worksheet
    Set ws = ActiveSheet 'Sheets("Sheet1") 'this change means it works on whichever sheet is the active sheet.
    Dim form1 As String
    Dim row As Long
    form1 = ws.Range("F6").Formula
    For row = 6 To 2533 Step 14
        ws.Cells(row, 6).Formula = form1
    Next
    'add the last formula (because the last cell with formula added will be on row 2526):
    ws.Cells(2533, 6).Formula = form1
  
    Application.Calculation = xlAutomatic
End Sub
 

Inun

New Member
Code:
Sub copyFormula()
    Application.Calculation = xlManual
    Dim ws As Worksheet
    Set ws = ActiveSheet 'Sheets("Sheet1") 'this change means it works on whichever sheet is the active sheet.
    Dim form1 As String
    Dim row As Long
    form1 = ws.Range("F6").Formula
    For row = 6 To 2533 Step 14
        ws.Cells(row, 6).Formula = form1
    Next
    'add the last formula (because the last cell with formula added will be on row 2526):
    ws.Cells(2533, 6).Formula = form1
 
    Application.Calculation = xlAutomatic
End Sub
P45 cal
This is running the code and looks magic but strange enough the code is not putting the right answers to F20 , F34 etc don't know why this is a very good starting point and much appreciated thanks
 

Stevie

Active Member
Hi Inun,
collating your responses and making some guesses, I am going to assume that cell F2533 doesn't actually contain the formula, it's just the end of the range you are specifying.
Also, adding P45cal's R1C1 suggestion, try this:
Code:
Sub copyFormula()
   Application.Calculation = xlManual
   Dim ws As Worksheet
   Set ws = Sheets("FCW")
   Dim form1 AsString
   Dim row AsDouble
   form1 = ws.Range("F6").formulaR1C1
   For row = 6 To 2533 Step 14
        ws.Cells(row, 6).FormulaR1C1 = form1
   Next
    Application.Calculation = xlAutomatic
End Sub
If this helped, please click 'Like'!
 

Stevie

Active Member
P45 cal
This is running the code and looks magic but strange enough the code is not putting the right answers to F20 , F34 etc don't know why this is a very good starting point and much appreciated thanks
This is likely due to not using R1C1 as P45cal previously suggested. try my response above.
 

Inun

New Member
Hi Inun,
I have written some code to do this, but it seems starting at 6 and going every 14 rows (6,20,34...etc) ends at 2526
or going every 13 rows (6,19,32...etc) ends at 2528.

Please clarify what you want as 2533-6 is not a multiple of 14.

Either way, here is the code if you wish to modify it for your use.

Code:
Sub copyFormula()
    Application.Calculation = xlManual
    Dim ws As Worksheet
    Set ws = Sheets("Sheet1")
    Dim form1 As String
    Dim row As Double
    form1 = ws.Range("F6").formula
    For row = 6 To 2533 Step 13
        ws.Cells(row, 6).Formula = form1
    Next
    Application.Calculation = xlAutomatic
End Sub
This is the formula in F6 that needs copied into F20 , F33 up to F2533
p45Cal's code worked but it is keeping that * A7 pinned down to A7 instead of moving down to A21 ,A35 up to A2534 thanks

=SUM(SUMIFS('Subcontractors Lia OnsiteMcA'!Q:Q,'Subcontractors Lia OnsiteMcA'!S:S,'FWC - Programme'!C:C,'Subcontractors Lia OnsiteMcA'!D:D,'FWC - Programme'!A:A),IF(SUMIFS('Raw Data'!O:O,'Raw Data'!AQ:AQ,"External construction")>SUMIFS('Subcontractors Lia OnsiteMcA'!Q:Q,'Subcontractors Lia OnsiteMcA'!S:S,'FWC - Programme'!C:C),(SUMIFS('Raw Data'!O:O,'Raw Data'!AQ:AQ,"External construction")-SUMIFS('Subcontractors Lia OnsiteMcA'!Q:Q,'Subcontractors Lia OnsiteMcA'!S:S,'FWC - Programme'!C:C))*A7,0),(SUMIF('Subcontractors Lia OnsiteMcA'!D:D,"Various",'Subcontractors Lia OnsiteMcA'!Q:Q)*A7))
If this was helpful, please click 'Like'!
 

Inun

New Member
Hi Inun,
collating your responses and making some guesses, I am going to assume that cell F2533 doesn't actually contain the formula, it's just the end of the range you are specifying.
Also, adding P45cal's R1C1 suggestion, try this:
Code:
Sub copyFormula()
   Application.Calculation = xlManual
   Dim ws As Worksheet
   Set ws = Sheets("FCW")
   Dim form1 AsString
   Dim row AsDouble
   form1 = ws.Range("F6").formulaR1C1
   For row = 6 To 2533 Step 14
        ws.Cells(row, 6).FormulaR1C1 = form1
   Next
    Application.Calculation = xlAutomatic
End Sub
If this helped, please click 'Like'!
F2533 actually contain the last formula
your code is bringing error and P45cal's is running the code and working but giving a difference in the totals I had before and this is what I am investigating thanks
 

Inun

New Member
This is likely due to not using R1C1 as P45cal previously suggested. try my response above.
yes the absolute and relative referencing was the issue with the numbers

I suspect I may have more spaces or less between where the formulas need to go so after every 14 may not be constant to get round this can we modify the code to insert the formula where the row is in the line with where it says "direct works" down the column -again the last cell where the formula will go is F2533 thanks
 

Inun

New Member
Code:
Sub copyFormula()
    Application.Calculation = xlManual
    Dim ws As Worksheet
    Set ws = ActiveSheet 'Sheets("Sheet1") 'this change means it works on whichever sheet is the active sheet.
    Dim form1 As String
    Dim row As Long
    form1 = ws.Range("F6").Formula
    For row = 6 To 2533 Step 14
        ws.Cells(row, 6).Formula = form1
    Next
    'add the last formula (because the last cell with formula added will be on row 2526):
    ws.Cells(2533, 6).Formula = form1
 
    Application.Calculation = xlAutomatic
End Sub
p45cal has done the magic that is brilliant the formula worked perfectly I had more space on 2 cases that is the reason why my total was different initially
many thanks
 

Inun

New Member
I want to say thank you to Stevie , p45cal , Marc L and all members in this forum who have taken time to read and respond to this request
That was a quick turnaround and it has made my life easier
 

p45cal

Well-Known Member
can we modify the code to insert the formula where the row is in the line with where it says "direct works" down the column
Q1. In which column might we find "direct works"?
Q2. Is "direct works" the only thing in the cell?
 

Inun

New Member
Q1. In which column might we find "direct works"?
Q2. Is "direct works" the only thing in the cell?
Hi
direct works is in C6 and it is the only thing in the cell
thanks

the other thing I appear to face all the time I run any code the excel spreadsheets are crashing and freezing and not responding .This has happened with other codes i have used in the past and is happening now again with the code you have just helped me with .What could be causing this ?
 

p45cal

Well-Known Member
Hi
direct works is in C6 and it is the only thing in the cell
I'll come up with something later…


the other thing I appear to face all the time I run any code the excel spreadsheets are crashing and freezing and not responding .This has happened with other codes i have used in the past and is happening now again with the code you have just helped me with .What could be causing this ?
With a formula like:

=SUM(SUMIFS('Subcontractors Lia OnsiteMcA'!Q:Q,'Subcontractors Lia OnsiteMcA'!S:S,'FWC - Programme'!C:C,'Subcontractors Lia OnsiteMcA'!D:D,'FWC - Programme'!A:A),IF(SUMIFS('Raw Data'!O:O,'Raw Data'!AQ:AQ,"External construction")>SUMIFS('Subcontractors Lia OnsiteMcA'!Q:Q,'Subcontractors Lia OnsiteMcA'!S:S,'FWC - Programme'!C:C),(SUMIFS('Raw Data'!O:O,'Raw Data'!AQ:AQ,"External construction")-SUMIFS('Subcontractors Lia OnsiteMcA'!Q:Q,'Subcontractors Lia OnsiteMcA'!S:S,'FWC - Programme'!C:C))*A7,0),(SUMIF('Subcontractors Lia OnsiteMcA'!D:D,"Various",'Subcontractors Lia OnsiteMcA'!Q:Q)*A7))

where there are many references to entire columns (red highlighting), this means that for each column 1 million+ cells are being evaluated, which takes time. Try to reduce them to the likes of $A$1:$A$1000, and $AQ$1:$AQ$1000, where you change the 1000 to the maximum number of rows ever likely to exist on the corresponding sheets (but bear in mind that all ranges referred to within a SUMIFS function should be the same size).
 

Inun

New Member
I'll come up with something later…


With a formula like:

=SUM(SUMIFS('Subcontractors Lia OnsiteMcA'!Q:Q,'Subcontractors Lia OnsiteMcA'!S:S,'FWC - Programme'!C:C,'Subcontractors Lia OnsiteMcA'!D:D,'FWC - Programme'!A:A),IF(SUMIFS('Raw Data'!O:O,'Raw Data'!AQ:AQ,"External construction")>SUMIFS('Subcontractors Lia OnsiteMcA'!Q:Q,'Subcontractors Lia OnsiteMcA'!S:S,'FWC - Programme'!C:C),(SUMIFS('Raw Data'!O:O,'Raw Data'!AQ:AQ,"External construction")-SUMIFS('Subcontractors Lia OnsiteMcA'!Q:Q,'Subcontractors Lia OnsiteMcA'!S:S,'FWC - Programme'!C:C))*A7,0),(SUMIF('Subcontractors Lia OnsiteMcA'!D:D,"Various",'Subcontractors Lia OnsiteMcA'!Q:Q)*A7))

where there are many references to entire columns (red highlighting), this means that for each column 1 million+ cells are being evaluated, which takes time. Try to reduce them to the likes of $A$1:$A$1000, and $AQ$1:$AQ$1000, where you change the 1000 to the maximum number of rows ever likely to exist on the corresponding sheets (but bear in mind that all ranges referred to within a SUMIFS function should be the same size).
Ok will narrow the range then and make sure columns same size in SUMIFS thanks
 
Top