Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 12-29-2005, 11:17 AM
Novice
 
Join Date: Nov 2005
Posts: 6
rnstewart
Default Mysterious "Subscript out of range" error

I have a VBA application I've developed, and one line of code works fine on my computer but refuses to work on another.



The code in question is designed to read the value of a particular cell of the spreadsheet that contains the app. It looks like this:

Code:
If (Workbooks("FileConvert.xls").Worksheets("Sheet1").Range("B1").Value <> "") Then
I.e., "Look at this workbook (named 'FileConvert.xls'), get the value from Cell B1 in sheet 'Sheet1' and see if it is empty".

On my machine, this works fine. On the other one, it throws a "Subscript out of range" error. Both computers are running Excel 2002. I also tried referencing the sheet by index instead of name (i.e., "Worksheets(1)" instead of "Worksheets("Sheet1")").

Any ideas what could be going wrong here? It seems like a simple enough command.
Reply With Quote
  #2  
Old 12-29-2005, 11:26 AM
Matrix's Avatar
Matrix Matrix is offline Windows 7 32bit Office 2010 32bit
Admin
 
Join Date: Jan 2005
Posts: 462
Matrix is on a distinguished road
Default

Maybe because your VBA application couldn't find the file. Did you copy all files to the second computer?
Reply With Quote
  #3  
Old 12-29-2005, 11:44 AM
Novice
 
Join Date: Nov 2005
Posts: 6
rnstewart
Default

Quote:
Originally Posted by Kevin
Maybe because your VBA application couldn't find the file. Did you copy all files to the second computer?
There's only one file in question. The VBA app is referencing the spreadsheet that it is contained in.
Reply With Quote
  #4  
Old 12-29-2005, 12:37 PM
Matrix's Avatar
Matrix Matrix is offline Windows 7 32bit Office 2010 32bit
Admin
 
Join Date: Jan 2005
Posts: 462
Matrix is on a distinguished road
Default

Something has to be different on this computer. The error means "your code is referring to an element in the code that does not exist." Such as:

- A non-existent sheetname
- A sheet number higher than you have, such as might be encountered by looking through each sheet and deleting some sheets along the way. Near the end since there are fewer sheets you get the error message.
- Outside the range of an array

Maybe, paste your code here is easier.
Reply With Quote
  #5  
Old 12-29-2005, 01:04 PM
Novice
 
Join Date: Nov 2005
Posts: 6
rnstewart
Default

Quote:
Originally Posted by Kevin
Something has to be different on this computer. The error means "your code is referring to an element in the code that does not exist." Such as:

- A non-existent sheetname
- A sheet number higher than you have, such as might be encountered by looking through each sheet and deleting some sheets along the way. Near the end since there are fewer sheets you get the error message.
- Outside the range of an array

Maybe, paste your code here is easier.
Yeah, those are all the things I would expect to cause a problem like this. But , in this particular case, I can't find any that would apply. The user on the otther end is not making any changes to the workbook, so I know that the sheet I'm referencing still exists. And anyway, I tried referencing it by index (index 1, to be precise), and every workbook has at least one sheet, right? I am not deleting or making any changes to the sheets; I have code elsewhere that modifies the value of the cell in question, but that code has not yet been called when the error occurs -- and even if it has, just because the value of the cell has changed doesn't mean that the cell doesn't exist!

This is frustrating as hell. Fortunately, the person who's having the problem is here in town, so I'm headed down to his house to try and debug it on his machine. Hopefully there I'll be able to track the issue down. I'll keep you posted.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
MYOB and "Item.Send error" Outlook message peter_lambros Outlook 1 12-06-2008 08:24 AM
Excel error: "Too many different cell formats" enviroko Excel 0 01-09-2008 07:27 AM
"IF" "THEN" statements in Macros mfgeng_iss Misc 0 10-04-2007 07:31 AM
"open" or "open using" option not availa ccd Outlook 0 09-22-2006 01:23 AM


All times are GMT -7. The time now is 07:11 PM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft