Run-time error '9' Subscript out of Range Help

Pookiemeister

Board Regular
Joined
Nov 26, 2015
Messages
240
Office Version
365, 2010
I am getting a "Run-time error '9': Subscript out of range" on the following code:
Code:
Worksheets("Exhibit ""I""").Activate
The worksheet name is Exhibit "I". If I remember correctly about what that run-time error means, on this line of code its saying it can't find a sheet with that name to activate. I have a feeling it has something to due with all the quotes around the letter (I). Unfortunately, I can not rename this spreadsheet because it is within a public file on a server and I'm not the author of it. I tried different ways to change the worksheet name like adding in single quotes(') instead of double quotes(") but VBA would change that line of code to RED Letters. The only way I could rename the worksheet above so that entire line of code wouldn't be in RED Letters was to position the quotes as shown above. But it still gave me a Run-time error '9'. I never dealt with worksheet names with quotes in them so I'm at a loss on to fix this error. Thank you.
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,601
Office Version
365
Platform
Windows
Try:
Code:
Worksheets("Exhibit " & """1""").Activate
 

Pookiemeister

Board Regular
Joined
Nov 26, 2015
Messages
240
Office Version
365, 2010
Thank you both for the quick reply. Unfortunately, I can't get either suggestion to work without getting a Run-time error '9'.
 

Pookiemeister

Board Regular
Joined
Nov 26, 2015
Messages
240
Office Version
365, 2010
mumps
Thank you for the suggestive link but from what I saw, that example had quotes around the worksheet name versus mine has quotes within a quote for my worksheet name.
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,559
Office Version
365
Platform
Windows
How about
Code:
Worksheets("Exhibit " & """I""").Activate
If that doesn't work run this
Code:
Sub chk()
Debug.Print "|" & ActiveSheet.Name & "|", Len(ActiveSheet.Name)
End Sub
and then copy the information from the immediate window to the thread
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
12,381
Office Version
365, 2010
Platform
Windows, Mobile
Deleted as should have refreshed, response was the same as Fluff's first suggestion :(
 
Last edited:

Pookiemeister

Board Regular
Joined
Nov 26, 2015
Messages
240
Office Version
365, 2010
Fluff
The first suggestion didn't work. So here's the answer to the code Sub chk()

|Finished Goods Summary| 22

I know it returns the Activesheet name and its length but how is this code used to debug(troubleshoot)? Thank You.
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,559
Office Version
365
Platform
Windows
I should have said to select the Exhibit "I" sheet before running that code.
 

Pookiemeister

Board Regular
Joined
Nov 26, 2015
Messages
240
Office Version
365, 2010
After selecting the Exhibit "I" sheet it now returns
|Exhibit "I" | 12
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,095,350
Messages
5,443,953
Members
405,258
Latest member
daveyf

This Week's Hot Topics

Top