?
Solved

Excel Automation of Autosum

Posted on 2016-08-11
23
Medium Priority
?
167 Views
Last Modified: 2016-08-15
Scenario: I have hundreds of tables that stretch from row 1 to row 1452. The tables are an export from another program that display percentages and totals. The total is always 100% but its not really "100%" because when rounding is taken into account, they actually total 101% or 99%. Since it is exported from another program, the 100% is just a value and not a formula. Exporting with the rounding in effect is not an option.

Question: Is there some way, maybe through VBA or a macro, that I could have Excel find every 100% and insert an autosum formula for the values above it? I tried the FIND/REPLACE to search for 100% and replace it with ALT+= which is the shortcut key for Autosum, but it wouldn't take it.

Any help is greatly appreciated!
0
Comment
Question by:FMezler
[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
  • 9
  • 6
  • 5
  • +2
23 Comments
 
LVL 52

Expert Comment

by:Rgonzo1971
ID: 41752133
Hi,

Could you send a sample?

Regards
0
 
LVL 4

Expert Comment

by:Alexandre Michel
ID: 41752148
Are all your tables of the same number of rows?
Are all your totals (the 100%) right under the numbers to add?
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 41752157
Is there some identifier which can be used within each set of data to determine where the Autosum has to go, eg:

Id 1
Id 1
Id 1
Id 1
Total  100%
Id 2
Id 2
Id 2
Id 2
Total  100%

If so, you could filter on the 100% rows and delete them and then use the Subtotal Function wizard to replace them.

Thanks
Rob H
0
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 

Author Comment

by:FMezler
ID: 41752158
I just have table after table after table of stuff like this: Notice the first table doesn't add to 100% (its actually 101%) but it displays 100% because it was just exported as values and no formulas. The second table does add to 100% but just because the value came across, not because it was calculated. I would want some way of Excel finding every 100% and replacing it with an Autosum formula.

3%
6%
35%
23%
13%
10%
6%
5%
100%

2%
7%
30%
28%
12%
11%
5%
5%
100%
0
 

Author Comment

by:FMezler
ID: 41752165
Alexandre: All the tables are NOT the same number of rows. All the totals (the 100%) are right under the numbers I need to add. The way I do it now is I just go to the cell with 100%, select it, and hit ALT+= to insert the autosum formula and press enter and move to the next 100%.
0
 

Author Comment

by:FMezler
ID: 41752167
Rob: There really is no unique identifier - just labels describing what the numbers represent.
0
 
LVL 52

Expert Comment

by:Rgonzo1971
ID: 41752168
Hi,

pls try

Sub Macro()
    With ws.Cells
        Set c = .Find("100%", LookIn:=xlValues, Lookat:=xlWhole)
        If Not c Is Nothing Then
            firstAddress = c.Address
            Do
                If c.Offset(1) = "" Then
                    Set Rng = Range(c.Offset(-1), c.End(xlUp))
                    c.Formula = "=Sum(" & Rng.Address(0, 0) & ")"
                End If
                
            Loop While Not c Is Nothing And c.Address <> firstAddress
        End If
    End With

End Sub

Open in new window

Regards
0
 
LVL 32

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 2000 total points
ID: 41752177
Assuming your tables are in col. A, then try this......


Sub PlaceSumFormula()
Dim rng As Range
For Each rng In Range("A:A").SpecialCells(xlCellTypeConstants, 1).Areas
    rng.Cells(rng.Rows.Count).Formula = "=SUM(R[-" & rng.Rows.Count - 1 & "]C:R[-1]C)"
Next rng
End Sub

Open in new window

If it is not column A, change the column reference in Range("A:A") on line#3.
0
 

Author Comment

by:FMezler
ID: 41752191
Rgonzo - I assigned your code to a macro, but when I run it, I get an error on the WITH WS.CELLS line and it just stops.
0
 
LVL 32

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41752197
Did you try the code I suggested after changing the column reference if required?
0
 
LVL 52

Expert Comment

by:Rgonzo1971
ID: 41752199
Sorry
Sub Macro()
    With ActiveSheet.Cells
        Set c = .Find("100%", LookIn:=xlValues, Lookat:=xlWhole)
        If Not c Is Nothing Then
            firstAddress = c.Address
            Do
                If c.Offset(1) = "" Then
                    Set Rng = Range(c.Offset(-1), c.End(xlUp))
                    c.Formula = "=Sum(" & Rng.Address(0, 0) & ")"
                End If
                
            Loop While Not c Is Nothing And c.Address <> firstAddress
        End If
    End With

End Sub

Open in new window

0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 41752216
Would you be able to add an identifier?

For example, the following will count the occurence of 100% in the cells above:

=COUNTIF($A$1:$A1,100%)

Copied down the extent of the data. Assuming the only lines with 100% are the total lines then each group will be numbered sequentially starting at 0. You can then copy paste values on this helper column, filter on the 100% rows and delete them, then use the ID column as the change criteria in the Subtotal Wizard.

Just testing this and the formula needs to go in row 2, assuming row 1 has headers.
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 41752264
See attached.

Thanks
Rob
AutoSum.xlsx
0
 

Author Comment

by:FMezler
ID: 41752267
Subodh: Yours is close. It inserts an autosum formula, but it adds incorrectly. It adds one row above it and itself. So say for example if the 100% was in cell H23, it reads =AUTOSUM(H22:H23) which comes out to 0 since the Autosum formula is in itself.

Rgonzo: Sorry, nothing happens with your code.
0
 

Author Comment

by:FMezler
ID: 41752274
Rob: All the tables are different sizes and it would be very time consuming to assign them identifiers for each table.
0
 
LVL 52

Expert Comment

by:Rgonzo1971
ID: 41752275
Sample file?
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 41752276
That is what the formula does, enter it in the first cell and then just copy down.

If the data is continuous then you can enter the formula in a cell in the column next  to the data and then double click the bottom right of that cell and it will fill down as far as required.

If there are gaps, then Shift + End + Home will take cursor to the last cell and then just Ctrl + D will fill down. When filtering for 100% show Blanks as well and then select everything to delete,  only visible rows will be affected.
0
 

Author Comment

by:FMezler
ID: 41752325
I have attached a file with what I am trying to do. It is one case only though - I usually have hundreds.
ExpertExchangeHelpSample.xlsx
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 41752351
Can you upload a more realistic sample, ie not just one set of data? We can then see what gaps there between tables, variations in number of rows etc.

Thanks
Rob H
0
 
LVL 52

Expert Comment

by:Rgonzo1971
ID: 41752507
then try

Sub Macro()
    With ActiveSheet.Cells
        Set c = .Find("100%", LookIn:=xlFormulas, Lookat:=xlWhole)
        If Not c Is Nothing Then
            firstAddress = c.Address
            Do
                If c.Offset(1) = "" Then
                    Set Rng = Range(c.Offset(-1), Cells(Range("B" & c.Offset(-1).Row).End(xlUp).Row, c.Column))
                    c.Formula = "=Sum(" & Rng.Address(0, 0) & ")"
                End If
            Set c = .FindNext()
            If c Is Nothing Then Exit Do
            Loop While c.Address <> firstAddress
        End If
    End With

End Sub

Open in new window

0
 

Author Comment

by:FMezler
ID: 41752614
Here is another sample. The table sizes and possible answers always vary. There is no pattern. Ideally I am looking for if I highlight (in my example) L1-L70 and Q1-Q70 and tell it to replace all 100% with the Autosum function.
ExpertExchangeHelpSample.xlsx
0
 
LVL 52

Expert Comment

by:Rgonzo1971
ID: 41753389
Hi,

if you only want to change the "100%" in Col C to E, my code should do the trick
https://www.experts-exchange.com/questions/28962810/Excel-Automation-of-Autosum.html?anchor=a41752614#a41752507

Regards
1
 

Author Closing Comment

by:FMezler
ID: 41756228
The solution by Subodh Tiwari did the closest to what I was looking to do. Unfortunately, it has to find the 100% in the cell and if it sees the =Round(Cell, 2) formula in there, it ignores it. I copied the entire sheet, pasted it as values so the =Round(cell, 2) was 100% and the code worked. Now if it could be adjusted to find the formulas that were equal to 100%, it would be perfect. Thanks for the help!
0

Featured Post

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

743 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