'Compile Error - Procedure Too Large'

Ok thank you. I checked with just 5 employees and the code ran fine. So I broke the code up into segments of 5 and called the subs:

TeamMember1-5
TeamMember6-10
TeamMember11-15
TeamMember16-20
TeamMember21-25
TeamMember26-30

I have 2 questions.

Have done this correctly? New code attached. and how do i now get my button to run all 6 subs as one?

many thanks

Nods

Many thanks once again. sorry for the questions but this is my first vba project.

Just one more question. I get complie errors on the lables that are split between to subs for example:

If TeamMbr5.Value = "Team Mbr" Or ThursStart5.Value = "Start" Or ThursFinish5.Value = "Finish" Then
GoTo Friday06

Exit Sub

End If



Sheets("Rota Input").Cells(Rows.Count, "H").End(xlUp).Offset(1, 0) = Me.WeekDD2.Value

Sheets("Rota Input").Cells(Rows.Count, "I").End(xlUp).Offset(1, 0) = "Thursday"
   
Sheets("Rota Input").Cells(Rows.Count, "J").End(xlUp).Offset(1, 0) = Me.SiteDD2.Value

Sheets("Rota Input").Cells(Rows.Count, "K").End(xlUp).Offset(1, 0) = Me.DeptDD2.Value

Sheets("Rota Input").Cells(Rows.Count, "L").End(xlUp).Offset(1, 0) = Me.TeamMbr5.Value

Sheets("Rota Input").Cells(Rows.Count, "M").End(xlUp).Offset(1, 0) = Me.ThursStart5.Text

Sheets("Rota Input").Cells(Rows.Count, "N").End(xlUp).Offset(1, 0) = Me.ThursFinish5.Value

End Sub


Sub TeamMember6to10()


Friday06:

Is there a way of solving this. it is an integral part of the form to prevent unneccesary data being written to the wookbook?

many thanks

nods

Really sorry but i'm not following you! Not sure what you mean.

is it possible to have a

Then 


Go to Sub Name

Statment I tried a few things and it didnt seem to work.

many thanks I was feeling a lil proud of that code aswel but your code much more user friendly.

Just 1 problem I get the 'Can not find specified object error' on

If TeamMbr1.Value <> "Team Mbr" And _
              Me.Controls(sDay & "Start1").Value <> "Start" And _
              Me.Controls(sDay & "Finish1").Value = "Finish" Then

Any ideas?

Hi thanks for your help. I am checking the debug tutorial you sent. Thank you very much btw.

I cant see why I am getting the can not find object error.

I am trying to attach the file but I keep getting an error message.

Really sorry not sure waht you mean? Where do I type this?

the obeject names are:

DAYStart1 - DAYStart30
DAYFinish1 - DAYFinish30
TeamMbr1 - TeamMbr30

The DAY is replaced with Mon, Tues , Wed, Thurs , Fri , Sat and Sun

many thanks.

The problem was the array was assuming my 'Day' object labels always had 3 letters. this isnt the case because i used Thurs and Tues.

I amended the code and now works fine.

new code for information is :

Private Sub RunBtn_Click()

    Dim vDay        As Variant
    Dim sDay        As String
    Dim iCnt        As Long

    If WeekDD2.Value = "Select Week" Or SiteDD2.Value = "Select Site" Or DeptDD2.Value = "Team/Dept" Then
        MsgBox "Please Select a Site, Week, and Department", vbExclamation, "Error"
        Exit Sub
    End If
            Sheets("Rota").Range("G2").Value = Me.WeekDD2.Value
            Sheets("Rota").Range("C2").Value = Me.SiteDD2.Value
    
    For iCnt = 1 To 30
        
        For Each vDay In Array("Fri", "Sat", "Sun", "Mon", "Tues", "Wed", "Thurs")
          
         sDay = (vDay)
            
            If TeamMbr1.Value <> "Team Mbr" And _
               Me.Controls(sDay & "Start" & iCnt).Value <> "Start" And _
               Me.Controls(sDay & "Finish" & iCnt).Value <> "Finish" Then

                RotaOut Array(Me.WeekDD2.Value, _
                              vDay, _
                              Me.SiteDD2.Value, _
                              Me.DeptDD2.Value, _
                              Me.TeamMbr1.Value, _
                              Me.Controls(sDay & "Start" & iCnt).Text, _
                              Me.Controls(sDay & "Finish" & iCnt).Value)
                              
                If vDay = "Friday" Then
                   

             End If
            End If
        
        Next vDay
        
    Next iCnt

    Worksheets("Rota Input").Calculate
    Worksheets("Rota Tables").Calculate
    Worksheets("Rota").Calculate
    Worksheets("Overview").Calculate
    Worksheets("Rota").Select
    MsgBox "Saved.", vbInformation
End Sub

However...

I tried applying the same principles to another crazy 5000 line peice of code that I wrote and I'm not sure where I am goin wrong. The code is used to amend the data that is written to the workbook in the above code. So should be similar.

I am getting the 'invalid Next control Variable reference' error on the

Next iCnt

Line

Can you see where I have gone wrong? This is my first attempt at arrays in VBA.

Many thanks

Nods

An example workbook attached.

New code

Dim varSite As String
Dim varWeek As String
Dim varTeam As String
Dim varWS As Worksheet
Dim varLastRow As Long
Dim varRow As Long
Dim vDay As Variant
Dim sDay As Variant
Dim iCnt As Long


Application.ScreenUpdating = False

varSite = Me.SiteDD2.Value
varWeek = Me.WeekDD2.Value
varTeam = Me.DeptDD2.Value

Set varWS = Sheets("Rota Input")
varLastRow = varWS.Range("A50000").End(xlUp).Row

For iCnt = 1 To 30
        
        For Each sDay In Array("Fri", "Sat", "Sun", "Mon", "Tues", "Wed", "Thurs")
          
         For Each vDay In Array("Friday", "Saturday", "Sunday", "Monday", "Tuesday", "Wednesday", "Thursday")
            
            If TeamMbr1.Value <> "Team Mbr" And _
               Me.Controls(sDay & "Start" & iCnt).Value <> "Start" And _
               Me.Controls(sDay & "Finish" & iCnt).Value <> "Finish" Then

For varRow = 2 To varLastRow

    If varWS.Cells(varRow, 2).Value = varWeek Then
       If varWS.Cells(varRow, 4).Value = varSite Then
        If varWS.Cells(varRow, 5).Value = varTeam Then
        Select Case varWS.Cells(varRow, 1).Value

                Case Me.WeekDD2.Value + sDay + Me.SiteDD2.Value + Me.DeptDD2.Value + iCnt
                
                varWS.Cells(varRow, 12).Value = Me.Controls(TeamMbr & iCnt).Value
                varWS.Cells(varRow, 13).Value = Me.Controls(sDay & "Start" & iCnt).Value
                varWS.Cells(varRow, 14).Value = Me.Controls(sDay & "Finish" & iCnt).Value



End Select
        End If
      End If
      End If
      
     
Next iCnt
Next vDay
Next sDay

End If
    
    Sheets("Rota").Range("G2").Value = Me.WeekDD2.Value
      Sheets("Rota").Range("C2").Value = Me.SiteDD2.Value
      
      End If
      
 Next varRow
Application.ScreenUpdating = True

Application.ScreenUpdating = True
Worksheets("Rota Input").Calculate
Worksheets("Rota").Calculate

MsgBox "Saved.", vbInformation

End Sub

rotaexample.xlsx

It looked like I had my end if's in the wrong place so I changed it to

                                    End Select
                                    
                                End If

                            Next iCnt

                        End If

                    Next vDay

                End If

            Next sDay

        End If

        Sheets("Rota").Range("G2").Value = Me.WeekDD2.Value
        Sheets("Rota").Range("C2").Value = Me.SiteDD2.Value

And now I get the 'next with out for' error message. I have no idea what I am doing wrong.

I hint would be much apreciated!

Many thanks

Nods

Sorry Just seen your prev post, Is that your version of my code?

I just tried to compile it and I got a syntax error on the

If .Cells(jj, 2).Value & .Cells(jj, 4).Value & .Cells(jj, 5).Value =WeekDD2.Value & DeptDD2.Value & SiteDD2.Value and .Cells(jj, 1).Value=WeekDD2.Value & sDay & SiteDD2.Value & DeptDD2.Value & j then .Cells(jj, 12).resize(,3).Value =split(Controls(TeamMbr & j).Value & "|" & Controls(sDay & "Start" & j).Value & "|" & Controls(sDay & "Finish" & j).Value,"|")

line

I have used the indent add in you sugested and my code looks like this now.

But I still cant spot the error. I'm really sorry for being a pain but i am trying to learn VBA.

Many thanks.

Nods

Ok So I reformated my code so it easier to read and have being trying several things but still can work out why I am getting the error. I am getting the 'invalid next control' error. Can someone please give some guidance on where I am going wrong?

Many thanks yet again.

nods

Private Sub RotaAmendButton_Click()

    If Me.SiteDD2.Value = "Select Site" Or Me.SiteDD2.Value = "" Then
        MsgBox "Please Select a Site, Week and Department for the week you want view. If you make any changes click on 'Amend Historical Rota'. To copy the Rota to a new week, change the week and click on 'Save'.", vbInformation
        Me.SiteDD2.SetFocus
        Exit Sub
    End If

    If Me.WeekDD2.Value = "Select Week" Or Me.WeekDD2.Value = "" Then
        MsgBox "Please Select a Site, Week and Department for the week you want view. If you make any changes click on 'Amend Historical Rota'. To copy the Rota to a new week, change the week and click on 'Save'.", vbInformation
        Me.WeekDD2.SetFocus
        Exit Sub

    End If
    If Me.DeptDD2.Value = "Team/Dept" Or Me.WeekDD2.Value = "" Then
        MsgBox "Please Select a Site, Week and Department for the week you want view. If you make any changes click on 'Amend Historical Rota'. To copy the Rota to a new week, change the week and click on 'Save'.", vbInformation
        Me.DeptDD2.SetFocus
        Exit Sub

    End If

    Dim varSite As String
    Dim varWeek As String
    Dim varTeam As String
    Dim varWS As Worksheet
    Dim varLastRow As Long
    Dim varRow As Long
    Dim vDay As Variant
    Dim sDay As Variant
    Dim iCnt As Long


    Application.ScreenUpdating = False

    varSite = Me.SiteDD2.Value
    varWeek = Me.WeekDD2.Value
    varTeam = Me.DeptDD2.Value

    Set varWS = Sheets("Rota Input")
    varLastRow = varWS.Range("A50000").End(xlUp).Row

    For iCnt = 1 To 30

        For Each sDay In Array("Fri", "Sat", "Sun", "Mon", "Tues", "Wed", "Thurs")

            For Each vDay In Array("Friday", "Saturday", "Sunday", "Monday", "Tuesday", "Wednesday", "Thursday")

                If TeamMbr1.Value <> "Team Mbr" And _
                   Me.Controls(sDay & "Start" & iCnt).Value <> "Start" And _
                   Me.Controls(sDay & "Finish" & iCnt).Value <> "Finish" Then

                    For varRow = 2 To varLastRow

                        If varWS.Cells(varRow, 2).Value = varWeek Then
                            If varWS.Cells(varRow, 4).Value = varSite Then
                                If varWS.Cells(varRow, 5).Value = varTeam Then
                                    Select Case varWS.Cells(varRow, 1).Value

                                    Case Me.WeekDD2.Value + sDay + Me.SiteDD2.Value + Me.DeptDD2.Value + iCnt

                                        varWS.Cells(varRow, 12).Value = Me.Controls(TeamMbr & iCnt).Value
                                        varWS.Cells(varRow, 13).Value = Me.Controls(sDay & "Start" & iCnt).Value
                                        varWS.Cells(varRow, 14).Value = Me.Controls(sDay & "Finish" & iCnt).Value



                                    End Select
                                End If
                            End If
                        End If


                    Next iCnt
                Next vDay
            Next sDay

        End If

        Sheets("Rota").Range("G2").Value = Me.WeekDD2.Value
        Sheets("Rota").Range("C2").Value = Me.SiteDD2.Value

    End If

Next varRow
Application.ScreenUpdating = True

Application.ScreenUpdating = True
Worksheets("Rota Input").Calculate
Worksheets("Rota").Calculate

MsgBox "Saved.", vbInformation

End Sub

Sheets("Rota").Range("G2").Value = WeekDD2.Value
Sheets("Rota").Range("C2").Value = SiteDD2.Value
End Sub

Worksheets("Rota Input").Calculate
Worksheets("Rota Tables").Calculate
Worksheets("Rota").Calculate
Worksheets("Rota").Select
MsgBox "Saved.", vbInformation

End Sub

Thanks for all of your help. I have debugged the code an it compiles. However I still cant get it to populate the form.

I have reposted under different heading because problem is now different.