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

- Thread starter Inun
- Start date

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

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
```

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.

If this was helpful, please click 'Like'![/quoteCode:`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`

Hi Stevie

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.

If this was helpful, please click 'Like'!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`

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")

HiIf, inStevie's code, you replace both instances of .formula with .formular1c1 the F6 formula's absolute/relative references will be respected.

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 ?

Without a complete explanation of the need neither an attachmentHow do I get round this ?

(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 …

The formula is in F6 and that needs going into F20 , F34 up to F2533Hi 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

Thanks Stevie

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

Better is you just first operate manually.

As if it can't work manually so it can't neither work by code …

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 calCode:`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`

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

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
```

This is likely due to not using R1C1 as P45cal previously suggested. try my response above.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

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.

This is the formula in F6 that needs copied into F20 , F33 up to F2533Code:

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'!

If you replace both instances of .formula with .formular1c1 the F6 formula's absolute/relative references will be respected.

F2533 actually contain the last formula

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:

If this helped, please click 'Like'!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`

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

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

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

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 initiallyCode:`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`

many thanks

Q1. In which column might we find "direct works"?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

Q2. Is "direct works" the

HiQ1. In which column might we find "direct works"?

Q2. Is "direct works" theonlything in the cell?

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 ?

I'll come up with something later…Hi

direct works is in C6 and it is the only thing in the cell

With a formula like: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 ?

=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 thanksI'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).