VBA Code Troubleshooting

What is the error message you are getting? What is the value of i when the error occurs? Can you attach your actual Excel file so we can try to run the code?

I'm not sure what the "With Range(a(i)) refers to

a appears to be an array that contains a list of range names. (It is a best practice to declare a, which was not done here.) The loop goes through every value of a, so Range(a(i)) refers to the range in the worksheet named by the corresponding name in the array.

The most obvious possibility is that one (or more) of the names is not actually defined on your sheet.

Range a is declared in the module code, as shown below:

Please Login or Register  to view this content.

So would I need to declare for a again in the private sub for my "Input" tab?

Unfortunately I can't attach my main spreadsheet since it has far too much confidential information, but the attached is what the code was created on. The set up is basically the same in my main spreadsheet, only with far more "document" sheets, and far more cell for inputting information in the "Input" tab.

a is not a range. It is a list of range names. If you go in your worksheet to Formulas, Name Manager you will see a list of names. In the code in the version of the file you just attached, a is defined as

Please Login or Register  to view this content.

All of those names are listed in Name Manager.

You need to look in the file that has the actual code that you provided in the first post (the file you can't share), go to Formulas, Name Manager, and see if the names are listed in that file.

Please Login or Register  to view this content.

If any are missing, it is causing the error.

Thanks for trouble shooting this with me! So I've gone into my name manager, and every one of them is listed there. There are of course several other names related to other ranges in my name manager, though I don't want those to be a part of this code or to be hidden. I am still getting the "Ru-Time error '1004': Method 'Range' of object'_Global" failed". When I debug it, it continues to point to the "With Range(a(i))" line in the code.

How can I tell what the value of i is when I run the code?

When you click the Debug button that line of code will be highlighted in yellow. If you hover the cursor over the i anywhere in the code, you should get a little pop-up window telling you the value.

If that doesn't work you can bring up the Immediate window with CTRL+g and then type
?i
in the Immediate window. It will tell you the value.

This will tell us which named range is causing the problem. However, you said they are all there so even if we can point to one, I'm not sure how to tell what is wrong with it.

Is it possible for you to delete all the data and attach the file? I don't really need real data to troubleshoot this particular problem.

OK I can do that! The value of i appears to be 1, not sure if that helps at all. Here is the document! The reference cell is $I$21 in the "Input" tab (I highlighted it yellow). Once you try to change that value the error pops up immediately.

PL_HIDE1 refers to this range:

Formula:

Please Login or Register  to view this content.

Change this to just:

Formula:

Please Login or Register  to view this content.

You're welcome. Thanks for the rep.

Guess you just checked they existed, not necessarily what they referred to