Solved

Need help modifying an existing macros to allow for subtotals if there are 59 rows in the document

Posted on 2014-09-12
5
104 Views
Last Modified: 2014-09-15
Glen Ray assisted with a macro recently that I need help tweeking.  The macro adds subtotals and a grand total which can be applied and removed as additional data is entered using two different controls.

It seems that all is well when we have at least 60 rows of data, but if I have 59 or less, then the subtotal and grand total on the last page returns a circular reference error.  If I add data to row 60, everything works as expected.

Also, when only 59 rows are entered, the row formatting (borders) are not appearing at the top of the 2nd page - 1st row of data.

I have attached the file.
H--Documents-Bad-Debt-Log---Sylvia-Dorma
0
Comment
Question by:klitton7
[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
  • 3
  • 2
5 Comments
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40320320
I found my last copy of the workbook I sent you and was able to replicate the error.  I've updated the code to correct it.  You should be able to select and copy the code from "Module2" (only the "Insert_Subotals" subroutine is changed) and put that in your current workbook.
Sub Insert_Subtotals()
    Dim r, i, x As Integer
    
    Range("A9").Select 'must start on row 9 to avoid false subtotal on top
    Do Until ActiveCell.Value = ""
        If (ActiveCell.Row - 6) Mod 28 = 0 Then
            'insert blank row
            ActiveCell.Offset(-1, 0).Select
            ActiveCell.EntireRow.Insert
            Range(ActiveCell, ActiveCell.Offset(0, 14)).Select
            For x = xlEdgeLeft To xlInsideHorizontal '7 to 12
                If x <> xlEdgeTop Then
                    With Selection.Borders(x)
                        .LineStyle = xlNone
                    End With
                End If
            Next x
            'insert page Total row
            ActiveCell.Offset(1, 0).Select
            ActiveCell.EntireRow.Insert
            ActiveCell.Value = "Total on this page"
            ActiveCell.Offset(0, 11).FormulaR1C1 = "=SUM(R[-27]C:R[-2]C)"
            ActiveCell.Offset(0, 12).FormulaR1C1 = "=SUM(R[-27]C:R[-2]C)"
            ActiveCell.Offset(0, 14).FormulaR1C1 = "=SUM(R[-27]C:R[-2]C)"
            Range(ActiveCell, ActiveCell.Offset(0, 14)).Select
            Selection.Font.Bold = True
            r = 0
        End If
        ActiveCell.Offset(1, 0).Select
        r = r + 1
    Loop
    
    'insert blank row
    ActiveCell.EntireRow.Insert
    'insert page Total row
    ActiveCell.Offset(1, 0).Select
    ActiveCell.Value = "Total on this page"
    ActiveCell.Offset(0, 11).FormulaR1C1 = "=SUM(R[-" & r & "]C:R[-2]C)"
    ActiveCell.Offset(0, 12).FormulaR1C1 = "=SUM(R[-" & r & "]C:R[-2]C)"
    ActiveCell.Offset(0, 14).FormulaR1C1 = "=SUM(R[-" & r & "]C:R[-2]C)"
    ActiveCell.RowHeight = 19.5
    
    i = ActiveCell.Row - 6
    ActiveCell.Offset(1, 0).Value = "GRAND TOTALS"
    ActiveCell.Offset(1, 11).FormulaR1C1 = "=SUM(R[-" & i & "]C:R[-1]C)/2"
    ActiveCell.Offset(1, 12).FormulaR1C1 = "=SUM(R[-" & i & "]C:R[-1]C)/2"
    ActiveCell.Offset(1, 14).FormulaR1C1 = "=SUM(R[-" & i & "]C:R[-1]C)/2"
    ActiveCell.Offset(1, 0).RowHeight = 19.5
    
    Range(ActiveCell, ActiveCell.Offset(1, 14)).Select
    Selection.Font.Bold = True
   
    'copy formats for totals
    Range(ActiveCell.Offset(0, 11), ActiveCell.Offset(0, 14)).Copy
    Range(ActiveCell.Offset(0, 11), ActiveCell.Offset(1, 14)).PasteSpecial xlPasteFormats
    Application.CutCopyMode = False
    
    Range("A6").Select
    
End Sub

Open in new window


The changes are in lines 27 & 30 and the removal of the original code above line 33 (that's where the real error was occurring).

Updated example file attached.
EE-H-Documents-BadDebtLogMaster-2c.xls
0
 

Author Comment

by:klitton7
ID: 40323192
Thanks Glen - just one formatting issue to address if you don't mind.

In the file you sent me, I removed rows 60 and greater, then used the "insert total rows" control.

I notice that on page two, the borders do not appear on the top of the first row.  Can you adjust that for me if you don't mind?
0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 500 total points
ID: 40323507
I fixed the formatting issue and updated the pagination logic also.  The last page should have split off one row earlier instead of pushing the page total and Grand total onto their own page.

Regards,
-Glenn
EE-H-Documents-BadDebtLogMaster-2d.xls
0
 

Author Comment

by:klitton7
ID: 40323744
Thanks Glenn!
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40323748
You're welcome.  Sorry for the confusion.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

696 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