Runtime Error

  • Thread starter Thread starter conio96
  • Start date Start date
C

conio96

Hi, I have a program that will loop a folder, look for excel file and
process it. The following is part of the code I'm using.

Set xlBook = Workbooks.Open(filename)
For k = 1 To xlBook.Worksheets.Count Step 1
maxRow = xlBook.Sheets(k).UsedRange.Rows.Count
Debug.Print "maxrow is ", maxRow
For i = maxRow To 2 Step -1
'process the data
Next i

The problem I'm facing currently, when I run the macro, I received a
overflow runtime error on the maxRow =
xlBook.Sheets(k).UsedRange.Rows.Count. The strange thing is I only
receive this error when I run the program with certain files.
Can you tell me how to overcome this problem?

Thanks a lot..
 
Do you use Option Explicit?
If so, how is maxRow dimensioned? It needs to be:
Dim maxRow As Long

More than 32767 rows will break an integer.

Kevin Beckham
-----Original Message-----

Hi, I have a program that will loop a folder, look for excel file and
process it. The following is part of the code I'm using.

Set xlBook = Workbooks.Open(filename)
For k = 1 To xlBook.Worksheets.Count Step 1
maxRow = xlBook.Sheets(k).UsedRange.Rows.Count
Debug.Print "maxrow is ", maxRow
For i = maxRow To 2 Step -1
'process the data
Next i

The problem I'm facing currently, when I run the macro, I received a
overflow runtime error on the maxRow =
xlBook.Sheets(k).UsedRange.Rows.Count. The strange thing is I only
receive this error when I run the program with certain files.
Can you tell me how to overcome this problem?

Thanks a lot..


------------------------------------------------

~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step
Click to expand...
guide to creating financial statements
 
maxRow = xlBook.Sheets(k).UsedRange.Rows.Count

this is incorrect. the count of rows in the used rows
range is not the max row where the used range does not
begin at row 1.

Either:
maxRow = xlBook.Worksheets(k).UsedRange.Rows.Count + _
xlBook.Worksheets(k).UsedRange.Row

OR:
maxRow = xlBook.Worksheets(k).Cells.SpecialCells
(xlCellTypeLastCell).Row

note I used the "Worksheets" property
NOT "Sheets" ....Sheets includes graphs & XL5 macro sheets

Patrick Molloy
Microsoft Excel MVP
-----Original Message-----

Hi, I have a program that will loop a folder, look for excel file and
process it. The following is part of the code I'm using.

Set xlBook = Workbooks.Open(filename)
For k = 1 To xlBook.Worksheets.Count Step 1
maxRow = xlBook.Sheets(k).UsedRange.Rows.Count
Debug.Print "maxrow is ", maxRow
For i = maxRow To 2 Step -1
'process the data
Next i

The problem I'm facing currently, when I run the macro, I received a
overflow runtime error on the maxRow =
xlBook.Sheets(k).UsedRange.Rows.Count. The strange thing is I only
receive this error when I run the program with certain files.
Can you tell me how to overcome this problem?

Thanks a lot..


------------------------------------------------

~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by
Click to expand...
step guide to creating financial statements
 
I think Patrick wanted to subtract 1 from his first formula.

maxRow = xlBook.Worksheets(k).UsedRange.Rows.Count + _
xlBook.Worksheets(k).UsedRange.Row -1

Another option:

With xlBook.Worksheets(k).UsedRange
maxRow = .Rows(.Rows.Count).Row
End With

A lot like Patrick's second formula.
 
Back
Top