Solved

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

Posted on 2016-07-24
18
76 Views
Last Modified: 2016-07-26
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
Comment
Question by:jgreenlee01
  • 7
  • 4
  • 3
  • +2
18 Comments
 
LVL 31

Expert Comment

by:Rob Henson
Comment Utility
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
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
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
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
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
 

Author Comment

by:jgreenlee01
Comment Utility
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
 
LVL 31

Expert Comment

by:Rob Henson
Comment Utility
@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
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
Change line 8 to
 .Rows(lngRow).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
0
 

Author Comment

by:jgreenlee01
Comment Utility
Hi Rob,

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

Author Comment

by:jgreenlee01
Comment Utility
Hi Martin,

The change you described did not add any rows,  is there something I am perhaps doing incorrectly?
0
 
LVL 31

Expert Comment

by:Rob Henson
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:jgreenlee01
Comment Utility
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
 

Author Comment

by:jgreenlee01
Comment Utility
I've attached the non macro version here
Budget-Template-Vision.xlsx
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
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
 
LVL 17

Expert Comment

by:Roy_Cox
Comment Utility
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
 
LVL 25

Expert Comment

by:ProfessorJimJam
Comment Utility
@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
 

Author Comment

by:jgreenlee01
Comment Utility
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
 
LVL 25

Expert Comment

by:ProfessorJimJam
Comment Utility
Ok

I am behind the wheel now, as soon as I get a chance , I will share with you the modified code.
0
 

Author Comment

by:jgreenlee01
Comment Utility
Thank you!
0
 
LVL 25

Accepted Solution

by:
ProfessorJimJam earned 500 total points
Comment Utility
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now