Macro Help Please-

Posted on 2011-10-07
Medium Priority
Last Modified: 2012-06-27
We use the attached workbook (dumbed down) for a timesheet report.  The data is exported from SQL and dropped into this workbook.  The user should be able to click the macro button and a macro should fire that automatcially sizes the columns and drops a total row for the hours column (one row after the final row of data.)

Most of the time this works great, for everyone, but....

This one, and occasionally other ones, error out.  Can someone help me to understand why and offer some suggestons on how to fix it so it will always work?   Thanks.

Question by:snyperj
LVL 14

Expert Comment

by:Tommy Kinard
ID: 36933628
The problem is the total variable is 65333 which is to big. To fix this issue I would suggest that you change
rows_in_col = Range(cell, cell.End(xlDown)).Count
rows_in_col = Range(cell, cell.End(xlUp)).Count

This change fixed your issue for me. book1.xls

Author Comment

ID: 36933775
Thanks, but I guess I am looking for a little help in understanding how this works ( I didn't create it)  and not so much a quick fix that might work for this data set, but not the next one.  

The real problem here is that this, as is, works most of the time...
LVL 14

Assisted Solution

by:Tommy Kinard
Tommy Kinard earned 1000 total points
ID: 36933895
The issue is the xlDown instead of going to the bottom and looking up for the last line of data you are looking down and going to the next 1. This would not fail in 2007/2010 versions due to the extra rows and columns that were added.

So what I did was tell excel to look all the way to the bottom and go back up to the last row of actual data. I did not see anything in the file that would cause this issue. But it was the 6th column that was going all the way to 65333, 65332 is the limit for 2003. I only saw around 40+ rows of data and I have no clue why it went that far down.

So, for this try it and see if any more errors show up. If not this is what the issue was.

Did that rambling help any? :)

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


Expert Comment

ID: 36934088
Sub DoTasks()

 Dim cell As Range
 Dim LastRow&, LastCol&
 Dim header As Range

    ' get last used row and column
    LastRow = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
    LastCol = Cells.Find(What:="*", After:=ActiveCell, SearchDirection:=xlPrevious,SearchOrder:=xlByColumns).Column
    Set header = Range(Cells(4, 1), Cells(4, LastCol))
    'Add the sum column two rows below max

    For Each cell In header
        If cell.Column = 7 Then
            Cells(LastRow + 2, cell.Column).Formula = "=@subtotal(9," & Range(Cells(5, cell.Column), Cells(LastRow, cell.Column)).Address & ")"
            'Range(Cells(lastrowk + 2, cell.Column).Formula = "=@subtotal(9," & Range(cells(, cell.End(xlDown)).Address & ")"
        End If
    Next cell
   ' Same command as double clicking the border.
    Cells(LastRow + 2, 1).Activate

End Sub

Accepted Solution

rowanscott earned 1000 total points
ID: 36935605
Ummn dragontooth is partly right i think. try this

Author Closing Comment

ID: 36952227
Appreciate the explanation and the working sample.  
Ahensch- thanks for your efforts as well, unfortunately the macro kept returning a circular reference error?
LVL 14

Expert Comment

by:Tommy Kinard
ID: 36959112
Thanks for the points and the grade. :)

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
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 …
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…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

850 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