Macro for subtotal

[Indra]

Hi there,

Could someone please help me in writing a macro for the January and February columns as shown in the attached file?

Warm regards,
Indra

[HansV]

Can you explain why you need a macro for this? You could simply fill or copy the formulas in the March column to the left.

[Indra]

I have very long rows and columns of tables, which already had detailed figures in every cell, but I need to check and redo the inconsistent subtotal formula.
March column actually only a reference, for expected results.

[HansV]

I'd add a column for the category, you can then let Excel create subtotals - see the attached workbook and the macro CreateSubtotals.

Excel Macro Subtotal.xlsm

[Indra]

I apologize if I didn't provide enough detail to describe my needs clearly. The table format is fixed, and there are always color cells (it can be yellow or blue) where I need to calculate subtotals. Additionally, I need to calculate a grand total at the bottom. The range of the table may vary, so I kindly request a macro to fill in a sum formula in every cell with the color RGB (255,255,0) and a grand total at the bottom every column

[HansV]

Try this macro:

Code: Select all

Sub CreateSubTotals()
    Dim r As Long
    Dim r1 As Long
    Dim m As Long
    Dim c As Long
    Dim n As Long
    Application.ScreenUpdating = False
    m = Cells(Rows.Count, 2).End(xlUp).Row
    n = Cells(3, Columns.Count).End(xlToLeft).Column
    r1 = 4
    For r = 4 To m - 1
        If Cells(r, 2).Interior.Color = vbYellow Then
            Range(Cells(r, 3), Cells(r, n)).FormulaR1C1 = "=SUBTOTAL(9,R" & r1 & "C:R" & r - 1 & "C)"
            r1 = r + 1
        End If
    Next r
    Range(Cells(m, 3), Cells(m, n)).FormulaR1C1 = "=SUBTOTAL(9,R4C:R" & r - 1 & "C)"
    Application.ScreenUpdating = True
End Sub

[Indra]

Many thanks Hans. I applied it to a table with more columns and rows still work.
I wonder if it's possible to use sum instead of subtotal? Because I used to check formulas with track precedents..

[HansV]

That would make it much more complicated.

[Indra]

Oh ok, thanks much, Hans.

Best,
Indra