Runtime Error
 

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..


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements

Runtime Error
 

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..


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from
http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step
guide to creating financial statements
.

Runtime Error
 

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..


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from
http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by
step guide to creating financial statements
.

Runtime Error
 

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.

Patrick Molloy wrote:

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..


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from
http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by
step guide to creating financial statements
.


--

Dave Peterson