Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Subscript out of range
I am running into a Run-time error '9', Subscript out of range. When I run my macro in Excel 2000 and 2002, it works fine. When I run it under Excel 2003, that is when I get this error. I open a text file and try to move the values into sheets spread out throughout my workbook. Here is what some of my code looks like: ESTemp = ActiveWorkbook.Name StringLength = Len(ESTemp) EST = Left(ESTemp, StringLength - 4) UF = Application.GetOpenFilename(FileFilter:="Text Files (*.txt),*.txt", Title:="Open Estimator saved inputs file") If UF = "False" Then ' user hit cancel' Exit Sub End If Workbooks.OpenText Filename:=UF ActiveSheet.Name = "LEst" ESTemp2 = ActiveWorkbook.Name StringLength = Len(ESTemp2) ESTTEXT = Left(ESTemp2, StringLength - 4) Workbooks(EST).Worksheets("HDQ1").Range("B10").Val ue = Workbooks(ESTTEXT).Worksheets("LEst").Range("A90") The macro dies when I try to move data from the input text file into my worksheets. I change the name of the input text file sheet name because when the users create the files, they can name them whatever they want. I just don't understand why this works with older versions (9.0 & 10.0) and not 11.0? Bruce Gold -- Bruce001 ------------------------------------------------------------------------ Bruce001's Profile: http://www.excelforum.com/member.php...o&userid=26630 View this thread: http://www.excelforum.com/showthread...hreadid=490173 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Subscript out of range
when referencing a workbook, you should not remove the extension as you show
you are doing in the code. Whether it works without the extension is based on a windows setting. However, it always works when you use the extension regardless of the setting, so it is best always use the extension when refering to a workbook. Workbooks("Myworkbook.xls") will always work Workbooks("Myworkbook") will only work sometimes. -- Regards, Tom Ogilvy "Bruce001" wrote in message ... I am running into a Run-time error '9', Subscript out of range. When I run my macro in Excel 2000 and 2002, it works fine. When I run it under Excel 2003, that is when I get this error. I open a text file and try to move the values into sheets spread out throughout my workbook. Here is what some of my code looks like: ESTemp = ActiveWorkbook.Name StringLength = Len(ESTemp) EST = Left(ESTemp, StringLength - 4) UF = Application.GetOpenFilename(FileFilter:="Text Files (*.txt),*.txt", Title:="Open Estimator saved inputs file") If UF = "False" Then ' user hit cancel' Exit Sub End If Workbooks.OpenText Filename:=UF ActiveSheet.Name = "LEst" ESTemp2 = ActiveWorkbook.Name StringLength = Len(ESTemp2) ESTTEXT = Left(ESTemp2, StringLength - 4) Workbooks(EST).Worksheets("HDQ1").Range("B10").Val ue = Workbooks(ESTTEXT).Worksheets("LEst").Range("A90") The macro dies when I try to move data from the input text file into my worksheets. I change the name of the input text file sheet name because when the users create the files, they can name them whatever they want. I just don't understand why this works with older versions (9.0 & 10.0) and not 11.0? Bruce Gold -- Bruce001 ------------------------------------------------------------------------ Bruce001's Profile: http://www.excelforum.com/member.php...o&userid=26630 View this thread: http://www.excelforum.com/showthread...hreadid=490173 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Subscript out of range
Thanks Tom. That seems to have done the trick. I'm not even sure why I stripped off the extension before. It is old code that broke when applied to Excel 2003. -- Bruce001 ------------------------------------------------------------------------ Bruce001's Profile: http://www.excelforum.com/member.php...o&userid=26630 View this thread: http://www.excelforum.com/showthread...hreadid=490173 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Subscript out of Range | Excel Discussion (Misc queries) | |||
9: Subscript out of range | Excel Discussion (Misc queries) | |||
Subscript Out of Range | Excel Programming | |||
Subscript out of range? | Excel Programming | |||
Subscript Out of Range | Excel Programming |