?
Solved

Macro code help needed, please

Posted on 2011-03-22
6
Medium Priority
?
156 Views
Last Modified: 2012-05-11
I am using the code below tied to a command button at the top of the worksheet.  The code autofits all columns and puts a total line at the bottom, 1 row after the last row of data.  This worksheet receives data that is exported from access, so the amount of rows can vary.  The column headers begin in row 4.

It works great as long as there is more than 1 row of data.  If there is only 1 row of data (or no data rows)  the user receives a  "Run time error 1004, Application Defined  or Object Defined Error"  and line 27 is highlighted in code.   How can I correct this please?

Another user on this site provided me with this great macro... I am hoping a small tweak will fix this problem.

Thanks!


Sub DoTasks()

 Dim cell As Range
    Dim total As Long
    Dim rows_in_col As Long
    
    Dim header As Range
 'Find cells in header
    If Range("A4") = "" Then
        'May only be one cell in header
        Set header = Range("A4")
    Else
        Set header = Range(Range("A4"), Range("A4").End(xlToRight))
    End If
    
    'Find the max row
    For Each cell In header
        rows_in_col = Range(cell, cell.End(xlDown)).Count
        If rows_in_col > total Then
            total = rows_in_col
        End If
    Next cell
    
    'Add the sum column two rows below max
    For Each cell In header
        If cell.Column = 11 Or cell.Column = 12 Then
            Cells(cell.Row + total + 1, cell.Column).Formula = "=@subtotal(9," & Range(cell, cell.End(xlDown)).Address & ")"
        End If
    Next cell
    
    'Same command as double clicking the border.
    Range("A:M").EntireColumn.Autofit
    
    Cells(header.Row + total + 1, 1).Activate


End Sub

Open in new window

0
Comment
Question by:snyperj
[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
6 Comments
 
LVL 19

Expert Comment

by:Arno Koster
ID: 35189712
snyperj,

when no data is added, the total variable (the number of rows) will be the number of available rows in the worksheet (eg. 1048573)
When you add 1 to it (as in line 34), it won't fit any more.

A better way would be to use

total = usedrange.rows.count

Open in new window


instead of lines 17 up to 22.
0
 
LVL 6

Expert Comment

by:FernandoFernandes
ID: 35189771
I would fix the code by changing more things... but these changes below already do the job:
Sub DoTasks()

 Dim cell As Range
    Dim total As Long
    Dim rows_in_col As Long
    Dim header As Range
 'Find cells in header
    If Range("A4") = "" Then
        'May only be one cell in header
        Set header = Range("A4")
    Else
        Set header = Range(Range("A4"), Range("A4").End(xlToRight))
    End If
    
    If header Is Nothing Then Exit Sub
    
    'Find the max row
    For Each cell In header
        rows_in_col = Range(cell, cell.End(xlDown)).Count
        If rows_in_col > total And rows_in_col < Cells.Rows.Count - 5 Then
            total = rows_in_col
        End If
    Next cell
    
    If total > 1 Then
        Range(Cells(header.Row + total + 1, 1), Cells(header.Row + total + 1, header.Columns.Count)).FormulaR1C1 = "=SUM(R5C:R[-2]C)"
    End If
    
    'Same command as double clicking the border.
    Range("A:M").EntireColumn.AutoFit
    
    Cells(header.Row + total + 1, 1).Activate

End Sub

Open in new window

0
 
LVL 6

Expert Comment

by:FernandoFernandes
ID: 35189820
I am also against determining last row using a loop and the .End method of the range object from the top.
this is why I had to fix the condition inside the loop.
0
Independent Software Vendors: 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!

 

Author Comment

by:snyperj
ID: 35189868

hmmm.. thanks for the efforts!

akoster - your change raises a new error, Runtime 424 Object Required and highlights the new line of code yo gave which I inserted instead of lines 17 - 22

Fernando- your change puts a total on every column.  How can I get it back to just the columns that were being totaled?  (11 & 12)

Thanks!
0
 
LVL 6

Accepted Solution

by:
FernandoFernandes earned 2000 total points
ID: 35189909
change this line 26 only:
Range(Cells(header.Row + total + 1, 11), Cells(header.Row + total + 1, 12)).FormulaR1C1 = "=SUM(R5C:R[-2]C)"

Open in new window

0
 

Author Closing Comment

by:snyperj
ID: 35189959
Success!   Thank you.
0

Featured Post

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

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

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;…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
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…

752 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