Subscript out of range (Error 9)

[carolmanente]

Hello,

I have a worksheet that has the names of the employees in the area of the company I work in, called Employees. Each employee has their own worksheet (the name of the sheet is the name of the employee). The purpose of the for looping is to search the Employees worksheet for employee names and make changes to their respective worksheets if a given condition is met. But Excel returned with the error Subscript out of range (Error 9). Why is this error ocurring? The error is in the red line of the code.

Please Login or Register  to view this content.

[Norie]

Have you checked the sheet names and the values in the list for things like leading/trailing spaces, spelling errors etc?

Also, what workbooks are open when you run the code?

[Sintek]

Not to sure without seeing all your code, but this stands out...
Change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

[Zer0Cool]

Why is this error occurring?

Subscript out of range happens when you try and refer to an element of a collection/array that does not exist. The gist of it as it pertains to your code is you are trying to .select a sheet that doesnt exist.

To overly simplify, if you have 3 sheets in a file and did:

Please Login or Register  to view this content.

you should get the same error. If your sheets are named: Sheet1, Taxes, Bob and you did:

Please Login or Register  to view this content.

you should also get the error.

you need to check what:

Please Login or Register  to view this content.

becomes in that line when you get the error. You could add a debug line above it and have it debug.print to the immediate window the value of just """ & Plan2.Cells(i, 3).Value" to see what it is at that stage in the loop prior to failing. Alternatively you could add a break and step through the code and evaluate the value prior to getting the error.

Since we dont know whats in your cells I cant speak to why you have "" & preceding the cell values, not sure what the benefit of adding nothing before that string would be.

Its also generally asking for trouble to have a with inside another with. It should work, but often gets confused the more closely related both objects are. Especially when combined with implied language like Sheets() instead of workbook.worksheets() or .cells instead of sheet/worksheet.cells().

without sample data/workbook its hard to be more specific about the cause of your problem