Welcome to Chandoo.org Forums. Short message for you

Hi Guest,

Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide.
When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing
Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

Yours,
Chandoo

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

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 .

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

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.

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

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

Click to expand...

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

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

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:

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

Click to expand...

This is likely due to not using R1C1 as P45cal previously suggested. try my response above.

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:

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

This is likely due to not using R1C1 as P45cal previously suggested. try my response above.

Click to expand...

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

Q1. In which column might we find "direct works"?
Q2. Is "direct works" the only thing in the cell?

Click to expand...

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 ?

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 ?

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

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

Click to expand...

Ok will narrow the range then and make sure columns same size in SUMIFS thanks