getting Error 9 "Subscript out of range" with code

**H_Kennedy** · 12-22-2013, 08:36 PM

I get an error when trying to transfer data using userform.When selecting from userform the date,team & shift and items (1,2 or 3 items) from Listbox1 i get an error.If say I select day then I only need info from day transferred but if I select "all" from dropdown box I need all 3 shifts info transferred for that selected date

Any help appreciated

H

**protonLeah** · 12-22-2013, 10:34 PM

You cannot do:

Please Login or Register  to view this content.

You need a loop something like below:

Please Login or Register  to view this content.

**H_Kennedy** · 12-22-2013, 10:53 PM

Thanks

I still get an error

Thanks

**protonLeah** · 12-22-2013, 11:09 PM

You have spaces at the end of some of the names on the sheets themselves.

**H_Kennedy** · 12-22-2013, 11:27 PM

Thanks

I have this error now

Please Login or Register  to view this content.

**protonLeah** · 12-23-2013, 01:28 AM

In my original post, I moved: Set wksDest = ThisWorkbook.Worksheets("group a") outside of the For Each... loop. It's missing from your current code altogether. I have edited post #2 in RED.

**H_Kennedy** · 12-23-2013, 01:44 AM

Thanks

It does place the data now but still gives an error...also it does not differentiate between Day, Afternoon Night when selecting from userform..

Thanks

**H_Kennedy** · 12-23-2013, 05:03 AM

Hi

The error is a - code 9 Subscript out of Range....error in code below from attached workbook post #7

Please Login or Register  to view this content.

The sheets are in the workbook but could this be from the Array or Loop

Thanks

**H_Kennedy** · 12-23-2013, 08:02 PM

Still having issues .. could this be a potential issue

Please Login or Register  to view this content.

Please Login or Register  to view this content.

**protonLeah** · 12-24-2013, 12:43 AM

Sorry I haven't had chance to look at this today. I'll try tomorrow, but it may be Thursday.

**H_Kennedy** · 12-24-2013, 01:12 AM

Thank you

It seems to be the counter I think

Please Login or Register  to view this content.

Combobox2 -- I am hoping to also have it show for the selected date either just data for Day or Afternoon or Night or if "All" selected transfer for all 3 shifts....

Thanks again

**H_Kennedy** · 12-26-2013, 07:49 PM

Hi

I made some changes but still there is miss-alignment,,,any thoughts please

Also when I delete it removes the headings

This should read...I notices after I posted the file

Please Login or Register  to view this content.

Thanks

**protonLeah** · 12-26-2013, 08:37 PM

Please test the attachment. I made some changes. I deleted the For Each loop. Since you specify which team you want to process it's not necessary. I added code to only check specified stops if "All" is not selected. I tried to test for errors but...

**H_Kennedy** · 12-26-2013, 08:50 PM

Thank you

When I delete the data and re-enter data from userform this errors

Please Login or Register  to view this content.

Then I stop the debugger and it works again but it is missing the Shift for Internal and Excternal

Thank you

**protonLeah** · 12-27-2013, 01:10 AM

What Team, date, etc. did you choose that caused the error? I've been trying to guess.

**H_Kennedy** · 12-27-2013, 03:43 AM

Team A and 15-dec-2013
Thanks

**protonLeah** · 12-28-2013, 11:42 PM

The following works as long as there are no more than six total items per stop, because your arrays are dimensioned with six rows and your report form only allows for six items per stop. So if a counter increments to 7 you will get a "subscript out of range" error.

**H_Kennedy** · 12-29-2013, 03:44 AM

Thank you
That works really well.

If I insert another 4 rows for each stop (10 for each Stop instead of 6) is this possible to change in the code?
In the future there may be a need to add more rows so if it is possible where can I change the code?

Thanks for your help

**protonLeah** · 12-31-2013, 12:25 AM

The simplest way would be to add the four new rows to each STOP block, then hard code the changes.

Change the dimensions of the arrays from arrOP_PS(1 To 6, 1 To 5) to arrOP_PS(1 To 10, 1 To 5), etc.

WksDest.Range("O6") would stay the same, but,
WksDest.Range("O15") would change to "O19"
and WksDest.Range("O24") would change to "O28"

**H_Kennedy** · 12-31-2013, 10:49 PM

Thanks ProtonLeah

All the changes now work..thanks

I have a final question please

I am going to transfer all 3 shifts everytime so will not need the option for selecting Day,Afternoon or Night......all 3 will be transferred everytime
I am also going to transfer all 3 stops everytime so will not need to select individually Planned Stops,Unplanned Internal and External.....all 3 stops transferred everytime

In the Userform I will only need the Date and selection of a Team plus Transfer button.....does the code need to be altered or do I just remove the Stop Type Listbox and Shift Combobox from the Userform or can the code be commented out for future use if required?

I just want to remove the option for selecting a particular shift and also a particular stop....all will be transferred everytime

Thanks

**H_Kennedy** · 01-03-2014, 02:42 AM

Hi

I have a final question please

I am going to transfer all 3 shifts everytime so will not need the option for selecting Day,Afternoon or Night......all 3 will be transferred everytime
I am also going to transfer all 3 stops everytime so will not need to select individually Planned Stops,Unplanned Internal and External.....all 3 stops transferred everytime

In the Userform I will only need the Date and selection of a Team plus Transfer button.....does the code need to be altered or do I just remove the Stop Type Listbox and Shift Combobox from the Userform or can the code be commented out for future use if required?

I just want to remove the option for selecting a particular shift and also a particular stop....all will be transferred everytime

Thanks

**protonLeah** · 01-03-2014, 04:34 PM

Try this one. The design of the form is the same but I set the shift and stop boxes to hidden and modified the code to ignore

**H_Kennedy** · 01-04-2014, 06:57 AM

Thanks once again

That works nicely...to finish this workbook off can the actual Shift and Stop box`s be removed completely from the Userform so I can redesign and make the Userform smaller with only the Date and Team Box`s...I removed but it gave an error

Thanks for all the help

**protonLeah** · 01-05-2014, 12:55 AM

Yes. Just select and delete them. Then delete the commented out code that references them.

**H_Kennedy** · 01-06-2014, 08:37 PM

Hi ProtonLeah

Thanks for the help...appreciated

getting Error 9 "Subscript out of range" with code

re: getting Error 9 "Suncsript out of rnage" with code

re: getting Error 9 "Suncsript out of rnage" with code

re: getting Error 9 "Suncsript out of rnage" with code

re: getting Error 9 "Suncsript out of rnage" with code

re: getting Error 9 "Suncsript out of rnage" with code

re: getting Error 9 "Suncsript out of rnage" with code

re: getting Error 9 "Suncsript out of rnage" with code

re: getting Error 9 "Suncsript out of rnage" with code

re: getting Error 9 "Suncsript out of rnage" with code

re: getting Error 9 "Suncsript out of rnage" with code

re: getting Error 9 "Suncsript out of rnage" with code

Re: getting error with code

Re: getting Error 9 "Subscript out of range" with code

Re: getting Error 9 "Subscript out of range" with code

Re: getting Error 9 "Subscript out of range" with code

Re: getting Error 9 "Subscript out of range" with code

Re: getting Error 9 "Subscript out of range" with code

Re: getting Error 9 "Subscript out of range" with code

Re: getting Error 9 "Subscript out of range" with code

Re: getting Error 9 "Subscript out of range" with code

Re: getting Error 9 "Subscript out of range" with code

Re: getting Error 9 "Subscript out of range" with code

Re: getting Error 9 "Subscript out of range" with code

Re: getting Error 9 "Subscript out of range" with code

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] trying to understand why a code is not working "error 9 subscript out off range"

[SOLVED] Can't debug this code. The Arrays are producing a Subscript out of Range error

Bookmarks

Bookmarks

Posting Permissions