Macro to continue looping of formula.

Alan, Sorry I am new to this and still learning. If I put code in I will highlight all and use the # in future I am still learning about VBA very slowly. This forum is great.

Hello Sintek Thank you very much for the quick response. This macro has worked fine. I am just wondering if there is any way that my formula = OFFSET('% SPLIT'!$I30,COLUMNS(C33:$D33)-1,0), could change to -2, - 3 and so when for every time it performed the macro and then it would pick up my data. Cell C33 contained this as a formula and for every week I am trying to use this macro to achieve the results. Any suggestions. I really appreciate the help.

Hello I have now attached the spreadsheet. I am trying to use the offset formula =OFFSET('% SPLIT'!$I3,COLUMNS($D6:D6)-1,0) and would like the -1 to change to -2, -3 for each week using a macro.

I am trying to put the totals from the split spreadsheet into each day for column I and then total each week, but want it to be able to read this for 52 weeks.

So in the summary I am trying to put all the planned volume totals from the %Split overview and total each week. I planned to use the Macro to fill in each week the data. I thought if I used the offset formula to obtain this which is located in summary cell33, then it would read that and it would work. Maybe the formula is incorrect.

Hello Sorry for the late response I finish work at 2pm.

As I am new to this. I have now attached an excel sheet again with some data, how I would like it to format. I am referring to the Summary sheet, which I want to pick the data up for column I in %Split.

I have the formula in C33 which is =OFFSET('% SPLIT'!$I3,COLUMNS(C33:$D33)-1,0) on the summary sheet. When it is copied into planned volume cell D6 it shows as = OFFSET('% SPLIT'!$I3,COLUMNS(D6:$D6)-1,0). When I copy it to E6 it shows as = OFFSET('% SPLIT'!$I3,COLUMNS($D6:E6)-1,0). When I copy it to F6 it shows = OFFSET('% SPLIT'!$I3,COLUMNS($D6:F6)-1,0).

Week 2 for Sunday in cell L6 it shows as = OFFSET('% SPLIT'!$I3,COLUMNS($D6:L6)- 2,0) and so on.

I think it is the way it copies to start as you can see its different in C33 =OFFSET('% SPLIT'!$I3,COLUMNS(C33:$D33)-1,0) and then when it transfers it in D6 shows it as = OFFSET('% SPLIT'!$I3,COLUMNS($D6:D6)-1,0), however this is how I want the pattern to be.

I basically want the first cell to be D6 with = OFFSET('% SPLIT'!$I3,COLUMNS(D6:$D6)-1,0), E6 to be = OFFSET('% SPLIT'!$I3,COLUMNS($D6:E6)-1,0), F6 to be = OFFSET('% SPLIT'!$I3,COLUMNS($D6:F6)-1,0).

Week 2 in L6 to be = OFFSET('% SPLIT'!$I3,COLUMNS($D6:L6)- 2,0) and so on.

I hope this makes sense

P.S your coding is great I think it is just I am probably not explaining myself correctly. I have put the formula into the cell so you can see the data and how I want it to be )

Absolutely brilliant!!!! I am sorry if I have wasted your time but I can use some of the other code above in other parts.

Id love to be able to write this code in matter of minutes. you are a star thank you ever so much. I really appreciate your help. Thank you.

Just a question if I wanted the same effects but for actual volume which is located in H on %split. How would the code change, using the same formula at effects?

Great thank you I will look at this code later and try and understand it. I will now mark this as complete as you have been more than enough help today. Not sure how to mark as solved yet though. 2nd day and all that.
Again thank you. I will never be as good as you at coding. If I have any more problems I will be sure to look out for you on here.

Sorry I have just seen this. Yep have to start some where. I do no know anything about coding or vba really. It just makes my work easier.

Sorry to ask this, if I wanted to use this below and when I ran the macro and it copied into all the cells, how would I keep the same cell formatting ie the colour in the background.

Please Login or Register  to view this content.

Hello Sintek Thank you very much I tried a few things but kept getting an error. Thats worked perfect like normal. I am picking up it up slowly. I am just trying to understand the code first. While you are reading the forum what exactly does FormulaR1C1 = "=SUM(RC[-7]:RC[-1])" mean exactly. What is RC referring to? I know its the sum of the totals but I just wanted to understand what RC in the formula actually means.