Macro Help Please-

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.


book1.xls
snyperjAsked:
Who is Participating?
 
rowanscottCommented:
Ummn dragontooth is partly right i think. try this
Copy-of-book1.xls
0
 
Tommy KinardCommented:
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
0
 
snyperjAuthor Commented:
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...
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Tommy KinardCommented:
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? :)
0
 
ahenschCommented:
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.
    Range("A:K").EntireColumn.AutoFit
   
    Cells(LastRow + 2, 1).Activate

End Sub
0
 
snyperjAuthor Commented:
Appreciate the explanation and the working sample.  
Ahensch- thanks for your efforts as well, unfortunately the macro kept returning a circular reference error?
0
 
Tommy KinardCommented:
Thanks for the points and the grade. :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.