Runtime Error 9 Subscript out of Range

Jakson

New Member
Joined
Sep 14, 2021
Messages
21
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Help! This is the most ridiculously big brained problem I've ever run into. I'm not sure where or when the problem came about, but all of a sudden I can't reference one of my sheets anymore! The only way I can get VBA to run any code that has to do with range references or object references to this sheet is to use the indexed sheet value, in this case, sheet 3 of 15.

In previous subs, and in subs contained by other workbooks, I can use a variety of reference methods to get a worksheet or range in a worksheet. For some reason, no level of qualification or exactness, barring the literal command "sheet3", can activate or manipulate or even bring this mystery sheet into memory. I can see the sheet, it has not been modified by any user protections or hides or anything.

I'm completely miffed. Any ideas what brought this on? How to fix it? What to do to work around it?

I can attach some code if anyone is interested. I'd attach the workbook in question but its got HIPPA sensitive data on it and I'd need to do some heavy pruning.
 

Attachments

  • F11.PNG
    F11.PNG
    76 KB · Views: 24
Upvote 0

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Just to cover off all basis. You said sheet3 works.
So with that workbook active.
Go to code window.
And in the immediate window copy paste the below and hit enter.
(if its not visible hit ctrl+G)
Then try your code again.

VBA Code:
Sheet3.Name = "Training Supervision"

I'll give this snippet a try later today when I have a moment.
 
Upvote 0
I'll give this snippet a try later today when I have a moment.
Just to cover off all basis. You said sheet3 works.
So with that workbook active.
Go to code window.
And in the immediate window copy paste the below and hit enter.
(if its not visible hit ctrl+G)
Then try your code again.

VBA Code:
Sheet3.Name = "Training Supervision"


Alex, thank you very much. This resolved my issue.

Any idea what caused this? I've never experienced this issue until now.
 
Upvote 0
I think you had a double space in the sheet name.

Besides user error, is there any other way for this to happen? I had re-typed the name of the sheet when I initially experienced the issue. I don't believe I put in a double space. However, I cannot say with certainty I did not.

What a funky typo, lmfao!
 
Upvote 0
The code @Alex Blakenburg gave was just a rename of the sheet, so if that fixed your problem then there was a problem with the name of the sheet compared to what you were trying to call it.
 
Upvote 0
The code @Alex Blakenburg gave was just a rename of the sheet, so if that fixed your problem then there was a problem with the name of the sheet compared to what you were trying to call it.
Once the sheet rename code worked I figured it must've been some formatting issue. Reviewing the screen shot above, I think you're correct that I had added a double space between the first and second word. Boy I feel silly! LOL
 
Upvote 0
It is often the little things that make you pull your hair out. :)
Not to mention that once you have been staring at it for hours, you start to see what you expect to see, which is of course why you asked for a picture.

@Jakson the fact that you had tried it using the code name (Sheet3) and thought to mention it was also really helpful. I thought just renaming the sheet to what you had in the code would be quicker and save some back and forward correspondence (being in Australian and in a different timezone you become very conscious of the need to reduce the amount of toing and froing)
 
Upvote 0
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,272
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top