Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2016-07-24
18
Medium Priority
?
136 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
  • 3
  • +2
18 Comments
 
LVL 33

Expert Comment

by:Rob Henson
ID: 41727691
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 49

Expert Comment

by:Martin Liss
ID: 41727809
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 49

Expert Comment

by:Martin Liss
ID: 41727857
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:jgreenlee01
ID: 41727925
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 33

Expert Comment

by:Rob Henson
ID: 41727947
@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 49

Expert Comment

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

Author Comment

by:jgreenlee01
ID: 41727964
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
ID: 41727998
Hi Martin,

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

Expert Comment

by:Rob Henson
ID: 41728003
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
 

Author Comment

by:jgreenlee01
ID: 41728016
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
ID: 41728039
I've attached the non macro version here
Budget-Template-Vision.xlsx
0
 
LVL 49

Expert Comment

by:Martin Liss
ID: 41728048
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 22

Expert Comment

by:Roy Cox
ID: 41728073
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 26

Expert Comment

by:ProfessorJimJam
ID: 41728259
@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
ID: 41728269
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 26

Expert Comment

by:ProfessorJimJam
ID: 41728398
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
ID: 41728403
Thank you!
0
 
LVL 26

Accepted Solution

by:
ProfessorJimJam earned 2000 total points
ID: 41729013
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

Enroll in September's Course of the Month

This month’s featured course covers 16 hours of training in installation, management, and deployment of VMware vSphere virtualization environments. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

722 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