Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 147
  • Last Modified:

MACRO to auto sum any row with the word total in it

Hello,

I could really use some assistance with this process.

In the Budget tab, I need to build a macro that does the following,

Inserts a row after a row with the word total in it

Auto sum any row with the word total in it,  from the first blank row before it through the row just before the total row.

Is this possible?
Budget-Template-Test.xlsm
0
jgreenlee01
Asked:
jgreenlee01
  • 7
  • 4
  • 3
  • +2
1 Solution
 
Rob HensonIT & Database AssistantCommented:
Have you looked at using the Subtotal Wizard?

This will insert rows and subtotals to a data set based on the contents of a specific column changing.

I can't look at the file as I can't download xlsm in my workplace. Is there some other way of identifying when to insert the row, eg change of ID?

Thanks
Rob H
1
 
Martin LissRetired ProgrammerCommented:
If you put this UDF in a module you can use it to get your totals by putting (for example) =SumFormulas(C23:N27) in the cell where you want the total to show up.

Function SumFormulas(R As Range) As Double
Dim cel As Range
For Each cel In R
    SumFormulas = SumFormulas + cel.Value
Next
End Function

Open in new window

0
 
Martin LissRetired ProgrammerCommented:
And this macro will add the blank rows.

Sub InsertBlankRow()

Dim lngRow As Long

With Sheets("Budget")
    For lngRow = 9 To .UsedRange.Rows.Count
        If InStr(UCase(.Cells(lngRow, "B")), "TOTAL") > 0 Then
            .Rows(lngRow + 1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        End If
    Next
End With
End Sub

Open in new window

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
jgreenlee01Author Commented:
Hi Martin,

I appreciate you sticking with me through this.

The formula above insert a row above instead of below,  how do I change it to insert a row below?
0
 
Rob HensonIT & Database AssistantCommented:
@jgreenlee01 - have you tried the Subtotal Wizard as I suggested? This adds rows below as required and adds subtotals to whichever column(s) you choose.
0
 
Martin LissRetired ProgrammerCommented:
Change line 8 to
 .Rows(lngRow).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
0
 
jgreenlee01Author Commented:
Hi Rob,

Thanks for the suggestion,  unfortunately it doesn't work for this situation.  There will be a consistently changing data set
0
 
jgreenlee01Author Commented:
Hi Martin,

The change you described did not add any rows,  is there something I am perhaps doing incorrectly?
0
 
Rob HensonIT & Database AssistantCommented:
Yes, the wizard has an option for removing existing subtotals and replacing with new for when the data changes.

How does the macro solution deal with changing data?
0
 
jgreenlee01Author Commented:
Hi Rob,

I'm not entirely sure to be honest.  I'm still learning.

I've attached the non macro version of my spreadsheet here.   I need the spreadsheet to Auto sum on the rows that contain the word "total"  in column B,  they need to sum the category above.  However the line items above may change in description, the total descriptions are the only constant, the gl codes in column A will change as well.

I will not be the end user for this spreadsheet.  This is a template that will have info downloaded into it, then at least 7 different people will have their hands on each one created.  There are hundreds of properties that this will be generated for. And at least 17 different charts of accounts that I can identify, possibly more.  

So whatever solution that is created it must be easy for the end user to do.

Is there a way to use the subtotal function for rows that contain specific text and sum up to the previous subtotal?
0
 
jgreenlee01Author Commented:
I've attached the non macro version here
Budget-Template-Vision.xlsx
0
 
Martin LissRetired ProgrammerCommented:
My original code in post ID: 41727857 was correct. In other words line 8 should be .Rows(lngRow + 1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove. Here are pictures before and after I run it. How are you running the macro?
Before
After
0
 
Roy CoxGroup Finance ManagerCommented:
Your sheet has SUBTOTAL Formulas which are ideal for youir requirements. Howver, the numbers are not formatted as Numbers. You can use PasteSpecial to convert numbers stored as Text to real numbers.

To use the Paste Special command, follow these steps:
In any blank cell, type the value 1.
Make sure the cell you typed 1 in is formatted as a number.
Select the cell in which you typed 1, and then right click and choose Copy.
Select the cells with the values that you want to convert to numbers.
0
 
ProfessorJimJamCommented:
@jgreenlee01

please see solution in the attached workbook.

run the macro  or click on icon "click me"  then see what happens in the Column U. it will sum the above cells where it finds the word total in column B.

Sub TEST()
 ' By ProfJimJam 
LastRow = Range("B" & Rows.Count).End(xlUp).Row + 1
StartRow = 1
For i = StartRow To LastRow
If InStr(UCase(Trim(Cells(i, "B"))), "TOTAL") And i > StartRow Then
Cells(i, "U").Formula = "=SUM(S" & StartRow & ":S" & i - 1 & ")"

StartRow = i + 1
End If
Next
End Sub

Open in new window

EE.xlsm
0
 
jgreenlee01Author Commented:
Hi Jim,

That's kind of what I need this to do, except;
 I need it to insert a row below the total, and

I need the rows C through N to sum their perspective columns up through the previous total.
0
 
ProfessorJimJamCommented:
Ok

I am behind the wheel now, as soon as I get a chance , I will share with you the modified code.
0
 
jgreenlee01Author Commented:
Thank you!
0
 
ProfessorJimJamCommented:
please find attached.

you can also alter the code, in case if you want to add more columns.

Sub Test()

'by ProfJimJam
    For lRow = Cells(Cells.Rows.Count, "B").End(xlUp).Row To 1 Step -1
        If InStr(UCase(Trim(Cells(lRow, "B"))), "TOTAL") Then Rows(lRow).EntireRow.Insert
    Next lRow
LastRow = Range("B" & Rows.Count).End(xlUp).Row + 1
StartRow = 1
For i = StartRow To LastRow
If InStr(UCase(Trim(Cells(i, "B"))), "TOTAL") And i > StartRow Then
Cells(i, "C").Formula = "=SUM(C" & StartRow & ":C" & i - 1 & ")"
Cells(i, "D").Formula = "=SUM(D" & StartRow & ":D" & i - 1 & ")"
Cells(i, "E").Formula = "=SUM(E" & StartRow & ":E" & i - 1 & ")"
Cells(i, "F").Formula = "=SUM(F" & StartRow & ":F" & i - 1 & ")"
Cells(i, "G").Formula = "=SUM(G" & StartRow & ":G" & i - 1 & ")"
Cells(i, "H").Formula = "=SUM(H" & StartRow & ":H" & i - 1 & ")"
Cells(i, "I").Formula = "=SUM(I" & StartRow & ":I" & i - 1 & ")"
Cells(i, "J").Formula = "=SUM(J" & StartRow & ":J" & i - 1 & ")"
Cells(i, "K").Formula = "=SUM(K" & StartRow & ":K" & i - 1 & ")"
Cells(i, "L").Formula = "=SUM(L" & StartRow & ":L" & i - 1 & ")"
Cells(i, "M").Formula = "=SUM(M" & StartRow & ":M" & i - 1 & ")"
Cells(i, "N").Formula = "=SUM(N" & StartRow & ":N" & i - 1 & ")"

StartRow = i + 1
End If
Next
End Sub

Open in new window

EE.xlsm
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 4
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now