Dynamic Formula entry - VBA code sought

[glynn1969]

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.

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

 

[glynn1969]

Peter_SSs - absolutely fantastic this works like a dream, will save me so much work manually inputting formula.

CSmth - thanks for your help too for kicking this off.

I am in awe of both your ability.

I just wish I understood the code - but believe me I will run through and try to gain an understanding.

 

[Peter_SSs]

You're very welcome.

I just wish I understood the code - but believe me I will run through and try to gain an understanding.

The code leaves formulas in the cells so they should help but in words it 'roughly' does this.
Works down each blank row and looks what is in column B

If it is "ss" (yellow rows) then it puts a formula that looks for the last row above the yellow row that contains anything other than "cc" and adds from the row below that to the row immediately above the formula itself.
So in row 9 the last row above without "cc" is row 5 so the sum is from row 6 to row 8

If it is "s" (green) then it sums all the rows above that have "cc" then subtracts any rows that have "s" (because they will have counted the "cc"s above themselves.
So in row 22 it sums all the "cc"s above row 22 (130) and subtracts the 31 from row 13 since that is the sum of all the "cc"s above that row, leaving us with 99, being the sum of "cc" rows from rows 14 to 21.

If it is "TOTAL" (amber) then it sums all the rows that have "s" in column B. (we could just as easily summed all the values that have "cc" in column B as that would have produced the same result.)

Hope that helps a bit.

 

[glynn1969]

That's a great help...thanks.i will now just need to look at the formulas themselves and wirk them out, there are some functions i have not come across before.

Thanks again.... you're really saving me time

 

[Peter_SSs]

Thanks again.... you're really saving me time

Cheers.

 

[glynn1969]

Peter, apologies if this is not the way things work on here,but could i ask.

I have looked up about the AGGREGATE FUNCTION and that the 14 returns the Largets value in the range, the 6 ignores error values and the 1 returns the highest value. However i cannot work out what properly what (ROW(D$2:D10)-ROW(D$2)+1)/($B$2:$B10<>"cc") is doing or what +1):D10) is doing. i just cant see how this managed to sum all the cc's in the range.

Could you please help?

=SUM(INDEX(D$2:D10,AGGREGATE(14,6,(ROW(D$2:D10)-ROW(D$2)+1)/($B$2:$B10<>"cc"),1)+1):D10)

 

[CSmith]

AGGREGATE in that example is finding the highest/last row where a "cc" does NOT exist and using that as the start for the SUM. soooo
=SUM(INDEX(D$2:D8,AGGREGATE(14,6,(ROW(D$2:D8)-ROW(D$2)+1)/($B$2:$B8<>"cc"),1)+1):D8)
evaluates to SUM($D$6:D8) ... Extremely clever!

 

[Fluff]

@glynn1969
If you select a cell containing that formula & on the Formula tab click Evaluate Formula, you can step through the calculation & see what each part is doing.
If you have any further queries, please post back.

 

[glynn1969]

Thanks for all your help people....one more question, i now want to apply this to specific columns, namely c3:n50 and p3:AA50. I have changed one line off the code from

For Each rA In Range("C3:C" & Range("B" & Rows.Count).End(xlUp).Row).Resize(, Cells(2, Columns.Count).End(xlToLeft).Column - 2).SpecialCells(xlBlanks).Areas

To

For Each rA In Range("C3:N50').......which seemed to work. I then tried to replicate changing the rang to ("P3:AA50") but this then does nothing...any clues?

 

[Peter_SSs]

what (ROW(D$2:D10)-ROW(D$2)+1)/($B$2:$B10<>"cc") is doing

Just expanding a little more on the already posted replies to this.

(ROW(D$2:D10)-ROW(D$2)+1)
This produces an array as follows
{2,3,4,5,6,7,8,9,10}-2+1 which becomes
{1,2,3,4,5,6,7,8,9}
being the position of each row in the range relative to the range itself.

Aside: You may see some people reduce that to (ROW(D$2:D10)-1)
which would produce the same final array. However, if a new row was inserted above row 2 then this reduced version will automatically adjust to (ROW(D$3:D11)-1) and then produce the array
{2,3,4,5,6,7,8,9,10} which will lead to incorrect results. So, the longer construction that I have used makes the formula more robust.

So, back to the formula, our array is then divided by an array of True/False values produced by ($B$2:$B10<>"cc")
Just making up some T/F values this could be
{1,2,3,4,5,6,7,8,9}/{F,F,T,T,F,F,T,F,F}
The corresponding values are divided with T being coerced to 1 and F coerced to 0 producing
{#DIV/0!,#DIV/0!,3,4,#DIV/0!,#DIV/0!,7,#DIV/0!,#DIV/0!}
This is when the AGGREGATE comes in and returns the largest ignoring errors -> 7

i now want to apply this to specific columns, namely c3:n50 and p3:AA50.

When you are processing the P3:AA50 range, are you still looking in column B for "cc" etc or are you then looking in column O for those values?

 

[glynn1969]

Ahhh brillient, i was wondering about the -2+1 but makes semse now.

For the P3:AA50 iwould still be using column B.

I so wish i had your brain amd knowledge.