Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where.
.I have a set of data (more rows than the example shows) where by I am looking for a macro that can enter a formulae to sum up ranges of cells dependant on column B entry. In my example below I have a data extract, however the data will not always be in the same order.
I am looking for VBA to enter formulas in the blank cells of column C.
Where the entry in column B is an "SS" I need to sum up all the column B "CC" entries immediately above ( not to include any CC entries already summed.
Then when there is an "S" in column B I need a formulae to sum up ALL the SS values above PLUS ANY CCs which have not been summed to a SS. (see formula in E14 of my data)
Once an "S" is summed then that is the end of that section and I then need to repeat the formula entries in the next section until an S is reached again.
Finally ending up with a formula to sum up all the "S" entries.
I have made entries in Col D and Col E of my data example to try to help with my requirements. Any help gratefully received.
.I have a set of data (more rows than the example shows) where by I am looking for a macro that can enter a formulae to sum up ranges of cells dependant on column B entry. In my example below I have a data extract, however the data will not always be in the same order.
I am looking for VBA to enter formulas in the blank cells of column C.
Where the entry in column B is an "SS" I need to sum up all the column B "CC" entries immediately above ( not to include any CC entries already summed.
Then when there is an "S" in column B I need a formulae to sum up ALL the SS values above PLUS ANY CCs which have not been summed to a SS. (see formula in E14 of my data)
Once an "S" is summed then that is the end of that section and I then need to repeat the formula entries in the next section until an S is reached again.
Finally ending up with a formula to sum up all the "S" entries.
I have made entries in Col D and Col E of my data example to try to help with my requirements. Any help gratefully received.
1 | required result | basic Formula | ||
2 | COL B | Col C | Col D | Col E |
3 | cc | 1 | ||
4 | cc | 12 | ||
5 | ss | 13 | =SUM(C3:C4) | |
6 | cc | 3 | ||
7 | cc | 5 | ||
8 | cc | 3 | ||
9 | ss | 11 | =SUM(C6:C8) | |
10 | cc | 2 | ||
11 | ss | 2 | =SUM(C10) | |
12 | cc | 5 | ||
13 | s | 31 | =SUM(D5,D9,D11,C12) | |
14 | cc | 3 | ||
15 | cc | 8 | ||
16 | cc | 9 | ||
17 | cc | 12 | ||
18 | ss | 32 | =SUM(C14:C17) | |
19 | cc | 4 | ||
20 | cc | 63 | ||
21 | ss | 67 | =SUM(C19:C20) | |
22 | s | 99 | =D18+D21 | |
23 | TOTAL | 130 | =D13+D22 |