Dynamic Formula entry - VBA code sought

glynn1969

New Member
Joined
Nov 24, 2018
Messages
47
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
 

CSmith

Well-known Member
Joined
Jan 13, 2020
Messages
614
Office Version
365, 2010, 2007
Platform
Windows, Mobile, Web
How is this?

Book1
ABCDEFGHI
21required resultbasic FormulacaSSsaS
32COL BCol CCol DCol E
43cc11.000
54cc1213.000
65ss1313.0013
76cc33.0013
87cc58.0013
98cc311.0013
109ss1111.0024
1110cc22.0024
1211ss22.0026
1312cc55.0026
1413s315.003131
1514cc33.000
1615cc811.000
1716cc920.000
1817cc1232.000
1918ss3232.0032
2019cc44.0032
2120cc6367.0032
2221ss6767.0099
2322s9999
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)
 

CSmith

Well-known Member
Joined
Jan 13, 2020
Messages
614
Office Version
365, 2010, 2007
Platform
Windows, Mobile, Web
This has your column D required result...

Book1
ABCDEFGHIJ
11required resultbasic Formulacasssasrequired result
22COL BCol CCol DCol ECol D
33cc11.000
44cc1213.000
55ss1313.001313
66cc33.0013
77cc58.0013
88cc311.0013
99ss1111.002411
1010cc22.0024
1111ss22.00262
1212cc55.0026
1313s315.00313131
1414cc33.000
1515cc811.000
1616cc920.000
1717cc1232.000
1818ss3232.003232
1919cc44.0032
2020cc6367.0032
2121ss6767.009967
2222s999999
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)
 

glynn1969

New Member
Joined
Nov 24, 2018
Messages
47
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.
 

CSmith

Well-known Member
Joined
Jan 13, 2020
Messages
614
Office Version
365, 2010, 2007
Platform
Windows, Mobile, Web
Huh? Can you explain better C is a data column from your example...
 

glynn1969

New Member
Joined
Nov 24, 2018
Messages
47
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
 

CSmith

Well-known Member
Joined
Jan 13, 2020
Messages
614
Office Version
365, 2010, 2007
Platform
Windows, Mobile, Web
Without better data to "see" what you are talking about I doubt I will be of better assistance :(

Almost "nothing" is truly impossible :D
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,589
Office Version
365
Platform
Windows
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
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,589
Office Version
365
Platform
Windows
. 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.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,589
Office Version
365
Platform
Windows
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)
 

Forum statistics

Threads
1,085,157
Messages
5,382,042
Members
401,767
Latest member
JohnLeek

Some videos you may like

This Week's Hot Topics

Top