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
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
How is this?

Book1
ABCDEFGHI
21required resultbasic FormulacaSSsaS
32COL BCol CCol DCol E
43cc11.00 0 
54cc1213.00 0 
65ss13 13.0013 
76cc33.00 13 
87cc58.00 13 
98cc311.00 13 
109ss11 11.0024 
1110cc22.00 24 
1211ss2 2.0026 
1312cc55.00 26 
1413s31 5.003131
1514cc33.00 0 
1615cc811.00 0 
1716cc920.00 0 
1817cc1232.00 0 
1918ss32 32.0032 
2019cc44.00 32 
2120cc6367.00 32 
2221ss67 67.0099 
2322s  9999
2423TOTAL130
Sheet1
Cell Formulas
RangeFormula
F4:F23F4=IF(B4="cc",IFERROR(C4+F3,C4),"")
G4:G23G4=IF(LEFT(B4,1)="s",F3,"")
H4:H23H4=IF(B3="s",0,VALUE("0"&H3)+VALUE("0"&G4))
I4:I23I4=IF(B4="s",H4,"")
I24I24=SUM(I4:I23)
 
Upvote 0
This has your column D required result...

Book1
ABCDEFGHIJ
11required resultbasic Formulacasssasrequired result
22COL BCol CCol DCol ECol D
33cc11.00 0  
44cc1213.00 0  
55ss13 13.0013 13
66cc33.00 13  
77cc58.00 13  
88cc311.00 13  
99ss11 11.0024 11
1010cc22.00 24  
1111ss2 2.0026 2
1212cc55.00 26  
1313s31 5.00313131
1414cc33.00 0  
1515cc811.00 0  
1616cc920.00 0  
1717cc1232.00 0  
1818ss32 32.0032 32
1919cc44.00 32  
2020cc6367.00 32  
2121ss67 67.0099 67
2222s  999999
2323TOTAL130130
Sheet1
Cell Formulas
RangeFormula
F3:F22F3=IF(B3="cc",IFERROR(C3+F2,C3),"")
G3:G22G3=IF(LEFT(B3,1)="s",F2,"")
H3:H22H3=IF(B2="s",0,VALUE("0"&H2)+VALUE("0"&G3))
I3:I22I3=IF(B3="s",H3,"")
J3:J23J3=IF(B3<>"cc",IF(I3="",G3,I3),"")
I23I23=SUM(I3:I22)
 
Upvote 0
HI CSmith, the formulas work great but what I need is a single formula to go into the blank cells in column C which will do this.
 
Upvote 0
Huh? Can you explain better C is a data column from your example...
 
Upvote 0
Hi , thanks for getting back.
Yes Col C is a data column - where there is data in column C this has pulled in from an external source (Hyperion). Column C also has the blank cells and it is in these blanks that I need a formula to pull through the formulas dependant on value in column B. This extract is a smaller version on my master book. In the master book column C is data for January and then I have Feb data in column D, March in Column E etc.going on for 2 years data..so I cant really have 5 additional columns per month.

I think what i'm asking might be impossible
 
Upvote 0
Without better data to "see" what you are talking about I doubt I will be of better assistance :(

Almost "nothing" is truly impossible :D
 
Upvote 0
Try this with a copy of your data.

VBA Code:
Sub Fill_Blanks()
  Dim rCell As Range
  
  For Each rCell In Range("C3:C" & Range("B" & Rows.Count).End(xlUp).Row).SpecialCells(xlBlanks)
    Select Case rCell.Offset(, -1).Value
      Case "ss": rCell.FormulaR1C1 = "=SUMIF(R3C[-1]:R[-1]C[-1],""cc"",R3C:R[-1]C)-SUMIF(R3C[-1]:R[-1]C[-1],""s*"",R3C:R[-1]C)"
      Case "s": rCell.FormulaR1C1 = "=SUMIF(R3C[-1]:R[-1]C[-1],""cc"",R3C:R[-1]C)-SUMIF(R3C[-1]:R[-1]C[-1],""s"",R3C:R[-1]C)"
      Case "TOTAL": rCell.FormulaR1C1 = "=SUMIF(R3C[-1]:R[-1]C[-1],""s"",R3C:R[-1]C)"
    End Select
  Next rCell
End Sub
 
Upvote 0
. In the master book column C is data for January and then I have Feb data in column D, March in Column E etc.
Sorry, I had missed this. My previous code will need slight alteration. I'll post back again shortly with that.
 
Upvote 0
My previous code will need slight alteration.
Actually, it had a bit more wrong with it than I first thought. :oops:

In any case, test this version. I'm assuming that row 2 can be used to determine the extent of the columns to process.

VBA Code:
Sub Fill_Blanks_Multi_Column()
  Dim rA As Range, rRw As Range
  
  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
    For Each rRw In rA.Rows
      rRw.Select
      Select Case rRw.Cells(0).Value
        Case "ss": rRw.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": rRw.FormulaR1C1 = "=SUMIF(R3C2:R[-1]C2,""cc"",R3C:R[-1]C)-SUMIF(R3C2:R[-1]C2,""s"",R3C:R[-1]C)"
        Case "TOTAL": rRw.FormulaR1C1 = "=SUMIF(R3C2:R[-1]C2,""s"",R3C:R[-1]C)"
      End Select
    Next rRw
  Next rA
End Sub

Here is my sheet after the code has been run. The coloured cells were empty before the code was run (colours were manually & were just for me to check the different types of calculations.

Book1
BCDEF
1
2Hdr BJanFebMar
3cc114
4cc1236
5ss13410
6cc3218
7cc5126
8cc3189
9ss113233
10cc2614
11ss2614
12cc553
13s314760
14cc342
15cc82017
16cc9217
17cc121520
18ss324156
19cc41717
20cc63103
21ss672720
22s996876
23TOTAL130115136
Insert Formulas
Cell Formulas
RangeFormula
C5:E5, C21:E21, C18:E18, C11:E11, C9:E9C5=SUM(INDEX(C$2:C4,AGGREGATE(14,6,(ROW(C$2:C4)-ROW(C$2)+1)/($B$2:$B4<>"cc"),1)+1):C4)
C13:E13, C22:E22C13=SUMIF($B$3:$B12,"cc",C$3:C12)-SUMIF($B$3:$B12,"s",C$3:C12)
C23:E23C23=SUMIF($B$3:$B22,"s",C$3:C22)
 
Upvote 0

Forum statistics

Threads
1,212,928
Messages
6,110,737
Members
448,295
Latest member
Uzair Tahir Khan

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