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
Macro for subtotal
-
- StarLounger
- Posts: 96
- Joined: 03 Sep 2010, 09:17
- Location: Citra Gran, Jakarta
Macro for subtotal
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Macro for subtotal
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.
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 96
- Joined: 03 Sep 2010, 09:17
- Location: Citra Gran, Jakarta
Re: Macro for subtotal
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.
March column actually only a reference, for expected results.
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Macro for subtotal
I'd add a column for the category, you can then let Excel create subtotals - see the attached workbook and the macro CreateSubtotals.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 96
- Joined: 03 Sep 2010, 09:17
- Location: Citra Gran, Jakarta
Re: Macro for subtotal
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
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Macro for subtotal
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
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 96
- Joined: 03 Sep 2010, 09:17
- Location: Citra Gran, Jakarta
Re: Macro for subtotal
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..
I wonder if it's possible to use sum instead of subtotal? Because I used to check formulas with track precedents..
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- StarLounger
- Posts: 96
- Joined: 03 Sep 2010, 09:17
- Location: Citra Gran, Jakarta
Re: Macro for subtotal
Oh ok, thanks much, Hans.
Best,
Indra
Best,
Indra