VBA Formula to create Subtotals

[Michael M]

Hi
I currently use this to create a formula in column "G" wherever there is a bold cell, and it SUMS up as far as the next Bold cell, -1 row.

How do I change the code to SUM down to the next Bold cell,-1 row.
I simply can't get my head around this, at the moment.

s = 13
For Each r In Range("G13:G" & lrow) 'ADDS SUBTOTALS AT EACH BOLD CELL IN "G"
    If r.Font.Bold = True Then
        With r
            .Formula = "=sum(r" & s & "c:r" & r.Row - 1 & "c)"
            .Interior.ColorIndex = 36
        End With

Any assistance greatly appreciated.

 

[Trebor76]

Hi Michael,

I suppose you could change this line...

r.Row - 1

...to this:

r.Row

But, (and I might be missing the point here) but won't that cause a circular?

Robert

 

[Michael M]

Hi Robert
I did try that , but it then only sums the first row
Basically the user wants the Sum at the top of the range rather than at the bottom, like this

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">14</td><td style="font-weight: bold;text-align: center;;">Item</td><td style="font-weight: bold;text-align: right;;"> $ 125,422.00 </td><td style="font-weight: bold;text-align: right;;"> $ 125,422.00 </td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: center;;">hour</td><td style="text-align: right;color: #FF0000;;">50.00</td><td style="text-align: right;border-right: 1px solid black;;">1,500.00</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: center;;">each</td><td style="text-align: right;color: #FF0000;;">7500.00</td><td style="text-align: right;border-right: 1px solid black;;">22,500.00</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: center;;">m3</td><td style="text-align: right;color: #FF0000;;">250.00</td><td style="text-align: right;border-right: 1px solid black;;">2,500.00</td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: center;;">item</td><td style="text-align: right;color: #FF0000;;">1000.00</td><td style="text-align: right;border-right: 1px solid black;;">500.00</td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: center;;">each</td><td style="text-align: right;color: #FF0000;;">100.00</td><td style="text-align: right;border-right: 1px solid black;;">300.00</td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: center;;">item</td><td style="text-align: right;color: #FF0000;;">95872.00</td><td style="text-align: right;border-right: 1px solid black;;">95,872.00</td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: center;;">m3</td><td style="text-align: right;color: #FF0000;;">250.00</td><td style="text-align: right;border-right: 1px solid black;;">1,250.00</td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style="text-align: center;;">item</td><td style="text-align: right;color: #FF0000;;">1000.00</td><td style="text-align: right;border-right: 1px solid black;;">1,000.00</td></tr></tbody></table><p style="width:7.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Price Makeup</p><br /><br />

 

[Fazza]

something like this, Mike? This gives an idea, adjust as required

s = lrow
For i = Range("G13:G" & lrow).Cells.Count To 13 Step -1
    If Range("G13:G" & lrow).Cells(i).Font.Bold = True Then
        With r
            .Formula = "=sum(r" & s & "c:r" & r.Row - 1 & "c)"
            .Interior.ColorIndex = 36
        End With
...

 

[Michael M]

Hi Fazza
I've obviously been looking at this for far too long !!
I tried your snippet and basically nothing happened.
The code at the moment that works UP the column is this

Sub Subtest() ' ADD AND FORMAT NEW SUB TOTALS AND TOTALS TO PRICE SHEET
Dim r As Range, s As Long, c As Range, lrow As Long
lrow = Worksheets("Price Makeup").Cells(Rows.Count, "G").End(xlUp).Row
Application.ScreenUpdating = False
s = 13
For Each r In Range("G13:G" & lrow) 'ADDS SUBTOTALS AT EACH BOLD CELL IN "G"
    If r.Font.Bold = True Then
        With r
            .Formula = "=sum(r" & s & "c:r" & r.Row - 1 & "c)"
            .Interior.ColorIndex = 36
        End With
        s = r.Row + 1
    End If
Next
End Sub

Using you code gives me an error at "For i"

Sub Subtest() ' ADD AND FORMAT NEW SUB TOTALS AND TOTALS TO PRICE SHEET
Dim r As Range, s As Long, c As Range, lrow As Long, i As Range
lrow = Worksheets("Price Makeup").Cells(Rows.Count, "G").End(xlUp).Row
Application.ScreenUpdating = False
s = lrow
For i = Range("G13:G" & lrow).Cells.Count To 13 Step -1
    If Range("G13:G" & lrow).Cells(i).Font.Bold = True Then
        With r
            .Formula = "=sum(r" & s & "c:r" & r.Row - 1 & "c)"
            .Interior.ColorIndex = 36
        End With
        s = r.Row + 1
    End If
Next
End Sub

 

[Trebor76]

I'm a little confused re the logic (nothing new I hear you say).

The amount in cell G14 is the total of all the figures beneath it yet none of them are bold and no idea what the total in cell F14 is?

 

[Michael M]

Robert
Sorry....confusing at the moment is my middle name.
F14 is irrelevant as it is a text value in the sample workbook.

Yes the amount in G14 is the sum of those figures below it because it is BOLD and it is the formula I'm after.
So, to refresh, the code will look in column "G" and go down the column until it finds a "TEXT Value" in the cell that is Bold. It then replaces the TEXT value with a formula summing all the numbers below it to the next Bold TEXT value.
A larger sample may help

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">14</td><td style="font-weight: bold;text-align: right;;">1</td><td style="font-weight: bold;text-align: center;;">Item</td><td style="font-weight: bold;text-align: right;;"></td><td style="font-weight: bold;text-align: right;;"> $ 125,422.00 </td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;">30.0</td><td style="text-align: center;;">hour</td><td style="text-align: right;color: #FF0000;;">50.00</td><td style="text-align: right;border-right: 1px solid black;;">1,500.00</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;">3.0</td><td style="text-align: center;;">each</td><td style="text-align: right;color: #FF0000;;">7500.00</td><td style="text-align: right;border-right: 1px solid black;;">22,500.00</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;">5.0</td><td style="text-align: center;;">m3</td><td style="text-align: right;color: #FF0000;;">250.00</td><td style="text-align: right;border-right: 1px solid black;;">1,250.00</td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;;">1.0</td><td style="text-align: center;;">item</td><td style="text-align: right;color: #FF0000;;">1000.00</td><td style="text-align: right;border-right: 1px solid black;;">1,000.00</td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style=";"></td><td style="text-align: right;border-right: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="font-weight: bold;text-align: right;;">1930</td><td style="font-weight: bold;text-align: center;;">m2</td><td style="font-weight: bold;text-align: right;;"></td><td style="font-weight: bold;text-align: right;border-right: 1px solid black;;"> $ 148,610.00 </td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: right;;">1930.0</td><td style="text-align: center;;">m2</td><td style="text-align: right;color: #FF0000;;">77.00</td><td style="text-align: right;border-right: 1px solid black;;">148,610.00</td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: right;color: #FF0000;;"></td><td style="text-align: right;border-right: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style="font-weight: bold;text-align: right;;">1</td><td style="font-weight: bold;text-align: center;;">Item</td><td style="font-weight: bold;text-align: right;;"></td><td style="font-weight: bold;text-align: right;border-right: 1px solid black;;"> $ 4,500.00 </td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style="text-align: right;;">80.0</td><td style="text-align: center;;">hour</td><td style="text-align: right;color: #FF0000;;">50.00</td><td style="text-align: right;border-right: 1px solid black;;">4,000.00</td></tr><tr ><td style="color: #161120;text-align: center;">25</td><td style="text-align: right;;">2.0</td><td style="text-align: center;;">day</td><td style="text-align: right;color: #FF0000;;">125.00</td><td style="text-align: right;border-right: 1px solid black;;">250.00</td></tr><tr ><td style="color: #161120;text-align: center;">26</td><td style="text-align: right;;">2.0</td><td style="text-align: center;;">day</td><td style="text-align: right;color: #FF0000;;">125.00</td><td style="text-align: right;border-right: 1px solid black;;">250.00</td></tr></tbody></table><p style="width:7.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Price Makeup</p><br /><br />

 

[Trebor76]

What about this:

Sub AddBold()

    Dim strBoldCells As String
    Dim lngRowStart As Long, _
        lngRowLast As Long
    Dim rngCell As Range
    
    lngRowStart = 15
    lngRowLast = Worksheets("Price Makeup").Cells(Rows.Count, "G").End(xlUp).Row
    
    For Each rngCell In Sheets("Price Makeup").Range("G" & lngRowStart & ":G" & lngRowLast)
        With rngCell
            If .Font.Bold = True Then
                If strBoldCells = "" Then
                    strBoldCells = "=" & .Address
                Else
                    strBoldCells = strBoldCells & "+" & .Address
                End If
            .Interior.ColorIndex = 36
            End If
        End With
    Next rngCell
    
    If strBoldCells = "" Then
        Sheets("Price Makeup").Range("G" & lngRowStart - 1).Value = 0
    Else
        Sheets("Price Makeup").Range("G" & lngRowStart - 1).Formula = strBoldCells
    End If

End Sub

HTH

Robert

 

[Michael M]

Sorry Robert
That only Highlights the Bold cells in yellow, which I can do fine.
The bold cells need to be replaced with a formula .....see my previous post.

The reason for the need for the formula is that rows will be inserted / deleted during the works, so the total needs to update each time.

 

[Trebor76]

That only Highlights the Bold cells in yellow

No, it also creates a formula string of all bolded amounts. I still can't see how the $125,422 in cell G14 is calculated