Dynamic Formula entry - VBA code sought

glynn1969

Board Regular
Joined
Nov 24, 2018
Messages
70
Office Version
  1. 2016
Platform
  1. Windows
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 resultbasic Formula
2​
COL BCol CCol DCol E
3​
cc
1​
4​
cc
12​
5​
ss13=SUM(C3:C4)
6​
cc
3​
7​
cc
5​
8​
cc
3​
9​
ss11=SUM(C6:C8)
10​
cc
2​
11​
ss2=SUM(C10)
12​
cc
5​
13​
s31=SUM(D5,D9,D11,C12)
14​
cc
3​
15​
cc
8​
16​
cc
9​
17​
cc
12​
18​
ss32=SUM(C14:C17)
19​
cc
4​
20​
cc
63​
21​
ss67=SUM(C19:C20)
22​
s99=D18+D21
23​
TOTAL130=D13+D22
 
1. You say from row 17. Is row 17 a header row or the first row of cc? 17 is first row of data

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

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? CC will be in column A and the data for each CC row will be populated already.

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? Yes a blank row in F:AA will be blank in other section but not in columns in between eg AB will have a formula in already as will other columns between each section range given.

5. Can you clarify if any other rows in those other sections can be blank that are not blank in F:AA? Think i answered this above now. The blank with be replicated scross the sections
 
Upvote 0

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
I have now had a chance to look more closely and it isn't getting much easier, but's push on.
The first 2 values, for example, shown in your latest column E do not add to 490.9 but to 491. I assume it is just a display or rounding issue, but it does make getting my head around what is required & checking results that little bit trickier. ;)

To the more important points (reference the screen shot below with a small portion of your sample data and results)..

  • Sub Service will sum all the "C10389XXX"s that sit above this line
1. If we apply this point literally to column E of the sample data then the result for row 26 would be 606.7 (sum of all blue rows), not 15.9 (or 15.8). If the correct result is the one you have shown then I'm struggling to differentiate what happens in that row from what happens in the 'Service' rows above it..

  • Service will sum up all Sub Services above, may also include any "C10389XXX" which sits directly in the Service
2. If we apply this point to column E then the result for row 38 would be 415.3 (sum of all the yellow rows), not 92.3

A Service could be made up of ... or 1 or more Service lines
3. I didn't understand that. Example or clarification?


Can you clarify? Sorry that I am not catching on more quickly. :(

Book1
AE
16Jan
17cc245.5
18cc245.5
19Service490.9
20cc33.3
21cc33.3
22cc33.3
23Service100
24cc7.9
25cc7.9
26Sub Service15.9
27cc20.6
28cc29.1
29cc249.4
30cc7.9
31Sub Service307.1
32Service323
33cc205.9
34Service205.9
35cc30.3
36cc26.3
37cc35.7
38Service92.3
39Total1,212.20
Insert Formulas (3)
 
Upvote 0
Really sorry for confusion i have caused....the data in here in in millions but formatted to show as displayed so 245,454,353, would show as 245.5 - hence the rounding issue you mentioned. I have now simplified the data to show just tens/hundres so easier to use and same principle applies. I tried to format my data with colours to help with explanation but these show on my excel doc but not when i copy into here.

with regards to grouping:-
  • A group is made up of CC entries (various numbers of entries)
  • Within that group all the CC entries could sit directly under a single Service - so in that case all CCs would sum up straight to the Service line
  • or the CCs could be sub-split in various Sub Services, then these sub Services sum up under the Service
  • effectively one CC can only sit in one Sub Service (or directly to a Service) and one Sub Service can only sit under one Service.
  • The total line will then sum up all the Service rows
  • Once a Total row has been reached/summed the data above is need to be ignored in the next section.
The data in column A is made up of 13 sections of CCs data (each summed in a Total row) - i have included 2 if that helps.

Function by Servicecol Ecol Fcol Gcol Hcol I
cc C10389407
10​
30​
10​
10​
10​
cc C10389408
20​
20​
20​
20​
20​
Servicesums just the 2 CC above
30​
50​
30​
30​
30​
cc C10389372
30​
60​
60​
30​
30​
cc C10389144
40​
40​
40​
20​
50​
cc C10389040
50​
20​
20​
50​
50​
Servicesums just the 3 CCs above
120​
120​
120​
100​
130​
cc C10389070
60​
60​
60​
60​
50​
cc C10389073
70​
80​
8​
20​
70​
Sub Servicesums just the 2 ccs above
130​
140​
68​
80​
120​
cc C10389076
80​
20​
80​
80​
50​
cc C10389077
90​
10​
30​
90​
90​
cc C10389137
100​
100​
40​
20​
50​
cc C10389081
110​
60​
110​
110​
110​
Sub Servicesumd jus the 4 CCs above
380​
190​
260​
300​
300​
Servicesums the 2 Sub Service above
510​
330​
328​
380​
420​
cc C10389392
10​
50​
80​
10​
10​
Servicesums jusy the 1 CC above
10​
50​
80​
10​
10​
cc C10389093
20​
20​
60​
20​
50​
cc C10389625
30​
40​
30​
20​
30​
cc C10389420
40​
40​
10​
40​
40​
Servicesums just the 3 CCs above
90.00​
100.00​
100.00​
80.00​
120.00​
Totalsums all Services above
760.00​
650.00​
658.00​
600.00​
710.00​
cc C10389665
20​
20​
20​
20​
20​
cc C10389862
20​
20​
20​
20​
20​
cc C10389864
50​
50​
50​
50​
50​
Servicesums just the 3 CCs above as the Total above denotes end of prev section
90.00​
90.00​
90.00​
90.00​
90.00​
cc C10389586
10​
10​
10​
10​
10​
cc C10389587
20​
20​
20​
20​
20​
Sub Servicesums just the 2 CCs above
30​
30​
30​
30​
30​
cc C10389594
20​
20​
20​
20​
20​
cc C10389618
30​
30​
30​
30​
30​
Sub Servicesums just the 2 CCs above
50​
50​
50​
50​
50​
cc C10389863
20​
40​
80​
20​
10​
Servicesums the 1 CC above & the 2 Sub Service
100​
120​
160​
100​
90​
Totalsums the 2 Services above
190.00​
210.00​
250.00​
190.00​
180.00​
 
Upvote 0
OK, let's see if I have my head around it now. You have some editing of the code to do - see the two Const lines near the start. This is where you define the rows and columns to deal with.

VBA Code:
Sub Fill_Blanks_Multi_Column_v3()
  Dim rCell As Range, rColumns As Range
  Dim LastTotalRw As Long, LastServiceRw As Long, LastSubSRw As Long
  
  Const sRngRows As String = "17:52"                '<- Edit as required
  Const sRngColumns As String = "E:AA,AC:AX,BB:BW"  '<- Edit/Expand to suit. If a single column use like AZ:AZ
  
  Set rColumns = Range(sRngColumns)
  With Intersect(Range(sRngRows), rColumns.Columns(1))
    LastTotalRw = .Row
    LastServiceRw = .Row
    LastSubSRw = .Row
    For Each rCell In .Columns(1).SpecialCells(xlBlanks)
      With Intersect(rCell.EntireRow, rColumns)
        Select Case Cells(.Row, "A").Value
          Case "Service"
            .FormulaR1C1 = Replace(Replace("=SUMIF(R#C1:R[-1]C1,""cc"",R#C:R[-1]C)+SUMIF(R^C1:R[-1]C1,""Sub Service"",R^C:R[-1]C)", "#", IIf(LastServiceRw > LastSubSRw, LastServiceRw, LastSubSRw)), "^", LastSubSRw)
            LastServiceRw = rCell.Row
            LastSubSRw = rCell.Row
          Case "Sub Service"
            .FormulaR1C1 = Replace("=SUMIF(R#C1:R[-1]C1,""cc"",R#C:R[-1]C)", "#", IIf(LastServiceRw > LastSubSRw, LastServiceRw, LastSubSRw))
            LastServiceRw = rCell.Row
          Case "Total"
            .FormulaR1C1 = Replace("=SUMIF(R#C1:R[-1]C1,""Service"",R#C:R[-1]C)", "#", LastTotalRw)
            LastTotalRw = rCell.Row
        End Select
      End With
    Next rCell
  End With
End Sub

Here is a part of mine after the code (I moved your comments to col B for clarity & checking)

Book1
ABEFGHI
16Function by Servicecol Ecol Fcol Gcol Hcol I
17cc1030101010
18cc2020202020
19Servicesums just the 2 CC above3050303030
20cc3060603030
21cc4040402050
22cc5020205050
23Servicesums just the 3 CCs above120120120100130
24cc6060606050
25cc708082070
26Sub Servicesums just the 2 ccs above1301406880120
27cc8020808050
28cc9010309090
29cc100100402050
30cc11060110110110
31Sub Servicesumd jus the 4 CCs above380190260300300
32Servicesums the 2 Sub Service above510330328380420
33cc1050801010
34Servicesums jusy the 1 CC above1050801010
35cc2020602050
36cc3040302030
37cc4040104040
38Servicesums just the 3 CCs above9010010080120
39Totalsums all Services above760650658600710
40cc2020202020
41cc2020202020
42cc5050505050
43Servicesums just the 3 CCs above as the Total above denotes end of prev section9090909090
44cc1010101010
45cc2020202020
46Sub Servicesums just the 2 CCs above3030303030
47cc2020202020
48cc3030303030
49Sub Servicesums just the 2 CCs above5050505050
50cc2040802010
51Servicesums the 1 CC above & the 2 Sub Service10012016010090
52Totalsums the 2 Services above190210250190180
Insert Formulas (5)
Cell Formulas
RangeFormula
E19:I19E19=SUMIF($A$17:$A18,"cc",E$17:E18)+SUMIF($A$17:$A18,"Sub Service",E$17:E18)
E23:I23E23=SUMIF($A$19:$A22,"cc",E$19:E22)+SUMIF($A$19:$A22,"Sub Service",E$19:E22)
E26:I26E26=SUMIF($A$23:$A25,"cc",E$23:E25)
E31:I31E31=SUMIF($A$26:$A30,"cc",E$26:E30)
E32:I32E32=SUMIF($A$31:$A31,"cc",E$31:E31)+SUMIF($A$23:$A31,"Sub Service",E$23:E31)
E34:I34E34=SUMIF($A$32:$A33,"cc",E$32:E33)+SUMIF($A$32:$A33,"Sub Service",E$32:E33)
E38:I38E38=SUMIF($A$34:$A37,"cc",E$34:E37)+SUMIF($A$34:$A37,"Sub Service",E$34:E37)
E39:I39E39=SUMIF($A$17:$A38,"Service",E$17:E38)
E43:I43E43=SUMIF($A$38:$A42,"cc",E$38:E42)+SUMIF($A$38:$A42,"Sub Service",E$38:E42)
E46:I46E46=SUMIF($A$43:$A45,"cc",E$43:E45)
E49:I49E49=SUMIF($A$46:$A48,"cc",E$46:E48)
E51:I51E51=SUMIF($A$49:$A50,"cc",E$49:E50)+SUMIF($A$43:$A50,"Sub Service",E$43:E50)
E52:I52E52=SUMIF($A$39:$A51,"Service",E$39:E51)
 
Upvote 0
Ahhhh - we (well you) are so close. i have ran your macro on my master document of 534 lines with just 4 errors which will be my fault for lack of clarrity (again)

My problem seems to be where i have a CC or Sub Service that sits directly under Total - i honestly hadn't realise that this was a situation.

On row 9 below i have a CC that sits directly under a Total - but is missed off the total (labled Total 1 just for ease of reading) for this section, however it does get picked up in the next sections first Service.

However, then in the Total 2 section the service for this section (includingt the extra 4) is pickedup in the total but the Sub Service in this Total 2 section does not get picked up.

Then in the Total 3 section the sub service that was missed in Total 2 does get picked up in this total.

I can not express how grateful i am for your work on this and understand if you have had enough of my requirements.

Column ABCDEFG
Variance
cc C10389834
1​
1​
cc C10389835
1​
1​
Service
2​
2​
cc C10389837
2​
2​
cc C10389838
2​
2​
Service
4​
4​
cc C10389800
4​
4​
TotalThe CC - sits directly under Total and so is missed by the formula.
-4​
6​
10​
cc C10389004
5​
5​
cc C10389921
5​
5​
ServiceAssume linked to the previous issue - and counts the previously missed CC in this Service.
4​
14​
10​
cc C10389401
5​
5​
cc C10389440
5​
5​
Sub Service
10​
10​
TotalSub Service sits sirectly under Total and is missed
-6​
14​
20​
cc C10389170
2​
2​
cc C10389177
2​
2​
ServiceAssume linked to the previous issue - and counts Sub Service of previous section.
10​
14​
4​
cc C10389163
5​
5​
cc C10389164
5​
5​
Service
10​
10​
Total
10​
24​
14​
 
Upvote 0
On row 9 below i have a CC that sits directly under a Total
Your terminology is confusing to me. From what I can understand, the CC that is missed "sits directly above a Total" :confused:

Try adjusting the 'Total' formula line to
VBA Code:
.FormulaR1C1 = Replace(Replace("=SUMIF(R#C1:R[-1]C1,""Service"",R#C:R[-1]C)+SUMIF(R^C1:R[-1]C1,""cc"",R^C:R[-1]C)", "#", LastTotalRw), "^", LastServiceRw)
 
Upvote 0

Forum statistics

Threads
1,213,568
Messages
6,114,348
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top