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

 

[Peter_SSs]

Is there anything in column O?

 

[Peter_SSs]

Is there anything in column O?

Never mind, it doesn't matter. Try this.
I'm assuming that, because you are still looking at col B when processing P:AA that the blanks are on the same rows in each section. If that is not the case, don't run this code but give more details or sample.
My code still uses the dynamic last row rather than your hard-coded row 50. Of course if there is further data below row 50 that might impact my code, a change would be needed.

Sub Fill_Blanks_Multi_Column_v2()
  Dim rCell As Range
  
  For Each rCell In Range("C3:C" & Range("B" & Rows.Count).End(xlUp).Row).SpecialCells(xlBlanks)
    With Intersect(rCell.EntireRow, Union(Columns("C:N"), Columns("P:AA")))
      Select Case Cells(.Row, "B").Value
        Case "ss": .FormulaR1C1 = "=SUM(INDEX(R2C:R[-1]C,AGGREGATE(14,6,(ROW(R2C:R[-1]C)-ROW(R2C)+1)/(R2C2:R[-1]C2<>""cc""),1)+1):R[-1]C)"
        Case "s": .FormulaR1C1 = "=SUMIF(R3C2:R[-1]C2,""cc"",R3C:R[-1]C)-SUMIF(R3C2:R[-1]C2,""s"",R3C:R[-1]C)"
        Case "TOTAL": .FormulaR1C1 = "=SUMIF(R3C2:R[-1]C2,""s"",R3C:R[-1]C)"
      End Select
    End With
  Next rCell
End Sub

 

[glynn1969]

Hi, sorry for delay in reply, i assume its a global timing thing (I'm in uk).

There is data in column O and data below row 50 which i do not want formukas entered into. The row 50 might alter slightly is it possible for me to enter the number of rows the formula is rewuired in somewhere?

 

[Peter_SSs]

is it possible for me to enter the number of rows the formula is rewuired in somewhere?

There would be several ways to do that, but is my thinking right here? It looks like column B has no empty cells from the heading down to the TOTAL row. If that is always the case and that determines the range or rows, then the code can figure that out.

I'm assuming that, because you are still looking at col B when processing P:AA that the blanks are on the same rows in each section.

You didn't mention this assumption that I made before so I'm sticking with that for now.

So, if both of the above are correct, just change this line in the post 22 code

For Each rCell In Range("C3:C" & Range("B2").End(xlDown).Row).SpecialCells(xlBlanks)

 

[glynn1969]

Hi sorry - i have tried to adapt the macro that was supplied on here but seem to be getting nowhere. The book i am looking to use it in is (Columns E to AY) with the case looking in column A (looking for CC, Service, Sub Service and Total)... i would think it is just (easy for me to say) a couple of changes to the code but can't figure out which ones. In the code i would like to define the range that the formulae are applied as being E17:AY534.

	Function by Service			col E	col F	col G	col H	col I
cc		C10389407		245.5​	2.2​	2.2​	234.3​	2.2​
cc		C10389408		245.5​	2.2​	2.2​	2.2​	234.3​
Service
cc		C10389372		33.3​	5.6​	5.6​	5.6​	5.6​
cc		C10389144		33.3​	5.6​	5.6​	5.6​	5.6​
cc		C10389040		33.3​	5.6​	5.6​	5.6​	5.6​
Service
cc		C10389070		7.9​	1.3​	1.3​	1.3​	1.3​
cc		C10389073		7.9​	1.3​	1.3​	1.3​	1.3​
cc		C10389074		482.4​	234.3​	5.6​	5.6​	234.3​
cc		C10389076		20.6​	5.6​	5.6​	5.6​	1.3​
cc		C10389077		29.1​	5.6​	5.6​	5.6​	5.6​
cc		C10389137		249.4​	1.3​	234.3​	1.3​	5.6​
cc		C10389081		7.9​	1.3​	1.3​	1.3​	1.3​
Sub Service
Service
cc		C10389392		205.9​	34.3​	34.3​	34.3​	34.3​
Service
cc		C10389093		30.3​	4.5​	4.5​	5.6​	5.6​
cc		C10389625		26.3​	3.2​	3.2​	5.6​	5.6​
cc		C10389420		35.7​	6.3​	6.3​	5.6​	5.6​
Service
Total

 

[Peter_SSs]

Hi sorry - i have tried to adapt the macro that was supplied on here but seem to be getting nowhere. The book i am looking to use it in is (Columns E to AY) with the case looking in column A (looking for CC, Service, Sub Service and Total)... i would think it is just (easy for me to say) a couple of changes to the code but can't figure out which ones. In the code i would like to define the range that the formulae are applied as being E17:AY534.

That's good that you are trying to adapt yourself - best way to learn.

One of the problems we are facing here is that things keep changing all the time.
- The columns the data is in and the column with 'cc' etc are now different.
- Now there is no mention of any column(s) to skip like last time (was column O)
- In your related thread you talked about ".. anything containing sub service" and " .. anything containing service" as if there was other text in those cells too but now it seems that is the only thing in those cells. There was also mention of ".. starting with .." but I don't see that here. Perhaps that other wasn't actually related to this question?
- Perhaps I'm wrong but I assume 'Service' in this latest sample was 's' in your original description in post 1 and 'Sub Service' was 'ss'. If so the way the data is structured in this latest example seems quite different. In post 1 we had a series of 'ss' sections followed by an 's' and that was repeated down the data. This latest one seems to have a series of 's' sections followed by an 'ss' (although there is no 'ss' after the last few 's' sections

Assuming this latest layout is in fact the correct layout, can you describe again what should happen?
As part of that description, can you also give the expected results for each of the 7 blank cells in column E

 

[glynn1969]

Hello really sorry for changing parameters.

Firstly i really appreciate your help with this.

If im honest, in my original request i created my own book to seek an answer along the lines that i wanted in order that i could adapt this myself to suit my final workbook - not realising how compliacted this would be.

An extract of my final workbook is as below:-

• All the rows that have a "C10389XXX" reference in will be autopopulated with data
• Rows with Service, Sub Service and Total will all be blank and it is these rows that i need to populate with formulas - i have entered the required returns
• Sub Service will sum all the "C10389XXX"s that sit above this line
• Service will sum up all Sub Services above, may also include any "C10389XXX" which sits directly in the Service
• The Total will always be the sum of the Service lines above ( but below any previous Total line) - effectively Total ends the cummulative sum for that section.
• A "C10389XXX" could sit a Sub Service, which then sits in a Service.
• A "C10389XXX" data could sit direct in a Service
• A Service could be made up of single "C10389XXX" line of data and/or 1 or more Service lines
• The Total will always be the sum of the Service lines above ( but below any previous Total line) - effectively Total ends the cummulative sum for that section.

I need this to apply to 12 sections with the following column references ( my thought was to create a single macro for columns F to AA, then adapt this 11 further times to run one macro for each section ( i know very long winded) - but these ranges may change and i though it may be easy to keep them separate.

• F to AA
• AC To AX
• BB to BW
• BY to CT
• CX to DS
• DU to EP
• ET to FO
• FQ to GL
• GP to HK
• HM to IH
• IL to JG
• JI to KD

	Function by Service			col E	col F	col G	col H	col I
cc		C10389407		245.5​	2.2​	2.2​	234.3​	2.2​
cc		C10389408		245.5​	2.2​	2.2​	2.2​	234.3​
Service				490.9​	4.4​	4.4​	236.6​	236.6​
cc		C10389372		33.3​	5.6​	5.6​	5.6​	5.6​
cc		C10389144		33.3​	5.6​	5.6​	5.6​	5.6​
cc		C10389040		33.3​	5.6​	5.6​	5.6​	5.6​
Service				100.0​	16.7​	16.7​	16.7​	16.7​
cc		C10389070		7.9​	1.3​	1.3​	1.3​	1.3​
cc		C10389073		7.9​	1.3​	1.3​	1.3​	1.3​
Sub Service				15.9​	2.6​	2.6​	2.6​	2.6​
cc		C10389076		20.6​	5.6​	5.6​	5.6​	1.3​
cc		C10389077		29.1​	5.6​	5.6​	5.6​	5.6​
cc		C10389137		249.4​	1.3​	234.3​	1.3​	5.6​
cc		C10389081		7.9​	1.3​	1.3​	1.3​	1.3​
Sub Service				307.1​	13.8​	246.8​	13.8​	13.8​
Service				323.0​	16.4​	249.4​	16.4​	16.4​
cc		C10389392		205.9​	34.3​	34.3​	34.3​	34.3​
Service				205.9​	34.3​	34.3​	34.3​	34.3​
cc		C10389093		30.3​	4.5​	4.5​	5.6​	5.6​
cc		C10389625		26.3​	3.2​	3.2​	5.6​	5.6​
cc		C10389420		35.7​	6.3​	6.3​	5.6​	5.6​
Service				92.3​	14.1​	14.1​	16.7​	16.7​
Total				1,212.2​	86.0​	319.0​	320.6​	320.6​

 

[glynn1969]

sorry (edit)


I need this to apply to 12 sections with the following column references ( my thought was to create a single macro for columns E to AA, then adapt this 11 further times to run one macro for each section ( i know very long winded) - but these ranges may change and i though it may be easy to keep them separate.

• E to AA

 

[glynn1969]

2nd sorry - this is currently 534 rows down (from 17 to 534)

 

[Peter_SSs]

A few more clarifications (might be more later )

1. You say from row 17. Is row 17 a header row or the first row of cc?

2. Can you confirm that all sections are still looking at column A for 'service', 'sub service' etc?

3. You referred to "C10389XXX " quite a bit. Does the code need to consider that or do those rows actually already have 'cc' in column A and the code could just use that?

4. Suppose row 25 is blank in columns F:AA. Can you confirm or clarify if row 25 will also be blank in AC:AX, BB:BW, etc?

5. Can you clarify if any other rows in those other sections can be blank that are not blank in F:AA?