Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
xpath error? Runtime Error 13 type mismatch | Excel Discussion (Misc queries) | |||
xpath error? Runtime Error 13 type mismatch | Excel Discussion (Misc queries) | |||
Runtime error '1004' General ODBC error | New Users to Excel | |||
Excel 2003 Macro Error - Runtime error 1004 | Excel Discussion (Misc queries) | |||
Unknown where is the problem on the Runtime error - Automation error | Excel Programming |