Macro Help Please-

Posted on 2011-10-07
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
    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

    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
    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? :)
    LVL 2

    Expert Comment

    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
    LVL 4

    Accepted Solution

    Ummn dragontooth is partly right i think. try this

    Author Closing Comment

    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
    Thanks for the points and the grade. :)

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
    Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
    This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
    This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

    732 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

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now