Using an array in a case statment

[nods]

Hi i have this code which is supose to populate a user form with data depending on the values the user enters.

I originaly used this code but it was nearly 5000 rows of code because the form has 30 rows so i thought i would use an array to make things easier. The code worked fine but was a tad memory intensive.

Original code x 30

Dim varSite As String
Dim varWeek As String
Dim varTeam As String
Dim varWS As Worksheet
Dim varLastRow As Long
Dim varRow 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 Each varCtrls In Me.Controls
    If Left$(varCtrls.Name, 7) = "Txt" Then
        varCtrls.Value = "N/A"
    End If
Next varCtrls

For varRow = 2 To varLastRow
    If varWS.Cells(varRow, 8).Value = varWeek Then
       If varWS.Cells(varRow, 10).Value = varSite Then
        If varWS.Cells(varRow, 11).Value = varTeam Then
        
            
            
            End If
            
            
            Select Case varWS.Cells(varRow, 7).Value
                
                
              
                
                Case Me.WeekDD2.Value + "Friday" + Me.SiteDD2.Value + Me.DeptDD2.Value + "1"
                    Me.TeamMbr1.Value = varWS.Cells(varRow, 12).Value
                    Me.FriStart1.Text = varWS.Cells(varRow, 13).Value
                    Me.FriFinish1.Text = varWS.Cells(varRow, 14).Value
                   
                Case Me.WeekDD2.Value + "Saturday" + Me.SiteDD2.Value + Me.DeptDD2.Value + "1"
                    Me.TeamMbr1.Value = varWS.Cells(varRow, 12).Value
                    Me.SatStart1.Text = varWS.Cells(varRow, 13).Value
                    Me.SatFinish1.Text = varWS.Cells(varRow, 14).Value

                Case Me.WeekDD2.Value + "Sunday" + Me.SiteDD2.Value + Me.DeptDD2.Value + "1"
                    Me.TeamMbr1.Value = varWS.Cells(varRow, 12).Value
                    Me.SunStart1.Text = varWS.Cells(varRow, 13).Value
                    Me.SunFinish1.Text = varWS.Cells(varRow, 14).Value
                
                Case Me.WeekDD2.Value + "Monday" + Me.SiteDD2.Value + Me.DeptDD2.Value + "1"
                    Me.TeamMbr1.Value = varWS.Cells(varRow, 12).Value
                    Me.MonStart1.Text = varWS.Cells(varRow, 13).Value
                    Me.MonFinish1.Text = varWS.Cells(varRow, 14).Value
            
                Case Me.WeekDD2.Value + "Tuesday" + Me.SiteDD2.Value + Me.DeptDD2.Value + "1"
                    Me.TeamMbr1.Value = varWS.Cells(varRow, 12).Value
                    Me.TuesStart1.Text = varWS.Cells(varRow, 13).Value
                    Me.TuesFinish1.Text = varWS.Cells(varRow, 14).Value

                Case Me.WeekDD2.Value + "Wednesday" + Me.SiteDD2.Value + Me.DeptDD2.Value + "1"
                    Me.TeamMbr1.Value = varWS.Cells(varRow, 12).Value
                    Me.WedStart1.Text = varWS.Cells(varRow, 13).Value
                    Me.WedFinish1.Text = varWS.Cells(varRow, 14).Value
 
                Case Me.WeekDD2.Value + "Thursday" + Me.SiteDD2.Value + Me.DeptDD2.Value + "1"
                    Me.TeamMbr1.Value = varWS.Cells(varRow, 12).Value
                    Me.ThursStart1.Text = varWS.Cells(varRow, 13).Value
                    Me.ThursFinish1.Text = varWS.Cells(varRow, 14).Value


         Case Me.WeekDD2.Value + "Friday" + Me.SiteDD2.Value + Me.DeptDD2.Value + "2"
                    Me.TeamMbr2.Value = varWS.Cells(varRow, 12).Value
                    Me.FriStart2.Text = varWS.Cells(varRow, 13).Value
                    Me.FriFinish2.Text = varWS.Cells(varRow, 14).Value
                   
                Case Me.WeekDD2.Value + "Saturday" + Me.SiteDD2.Value + Me.DeptDD2.Value + "2"
                    Me.TeamMbr2.Value = varWS.Cells(varRow, 12).Value
                    Me.SatStart2.Text = varWS.Cells(varRow, 13).Value
                    Me.SatFinish2.Text = varWS.Cells(varRow, 14).Value 
   
                    End Select
        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
Worksheets("Rota Input").Calculate
Worksheets("Rota").Calculate
End Sub

Here is my new code - it complies fine but doesn't seem to work.

Can some tell me where I am going wrong?

Private Sub RotaViewBtn_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 vDay        As Variant
    Dim sDay        As String
    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 vDay In Array("Friday", "Saturday", "Sunday", "Monday", "Tuesday", "Wednesday", "Thursday")
          
         sDay = Left(vDay, 3)
         
            
            If TeamMbr1.Value <> "Team Member" 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, 8).Value = varWeek Then
                            If varWS.Cells(varRow, 10).Value = varSite Then
                                If varWS.Cells(varRow, 11).Value = varTeam Then
                                    Select Case varWS.Cells(varRow, 7).Value

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

                                        Me.Controls(TeamMbr & iCnt).Value = varWS.Cells(varRow, 12).Value
                                        Me.Controls(sDay & "Start" & iCnt).Value = varWS.Cells(varRow, 13).Value
                                        Me.Controls(sDay & "Finish" & iCnt).Value = varWS.Cells(varRow, 14).Value
                            
                                    End Select
                                 End If
                            End If
                        End If
                    Next
                   
                   If vDay = "Friday" Then
                   
                   End If
            End If
        
        Next vDay
       Next iCnt
        
       Application.ScreenUpdating = True
        
        Sheets("Rota").Range("G2").Value = WeekDD2.Value
        Sheets("Rota").Range("C2").Value = SiteDD2.Value
        Worksheets("Rota Input").Calculate
        Worksheets("Rota").Calculate
        Worksheets("Rota Tables").Calculate
        
        MsgBox "Complete.", vbInformation
 
        

End Sub

example of the table the code is looking at

rotaexample.xlsx

[Bob Phillips]

Doesn't work is not very helpful, doesn't give us much steer.

[nods]

i'm not sure whats wrong to be honest. It compiles fine. But the code doesnt seem to write and data to the spread sheet.

even if i reverse engineer the code

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

it doesnt find data in the spread sheet. I think it is a problem with my case statment.

[nods]

If have this similar code using an array to write the data to the same table. and i get a syntax error on the

If Me.Controls("TeamMbr" & iCnt).Value <> "Team Member" And_
               Me.Controls(sDay & "Start" & iCnt).Value <> "Start" And_
               Me.Controls(sDay & "Finish" & iCnt).Value <> "Finish" Then

Any help would be really apreciated. I have been up all night looking at this,

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("Friday", "Saturday", "Sunday", "Monday", "Tuesday", "Wednesday", "Thursday")
          
         sDay = Left(vDay, 3)
         
            
            If Me.Controls("TeamMbr" & iCnt).Value <> "Team Member" 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.Controls("TeamMbr" & iCnt).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

[romperstomper]

You need a space between the And and the underscore:

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

[nods]

Hi thanks for that but it didn't solve the problem.

I have solved the syntax error in the above code though, But i am still having problems with first snippet of code.
It compiles fone with out errors. But it doesn't work and I cant see what the problem is. Is there a way of testing my case statment? Or can anyone see where I am going wrong?

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

Many thanks

Nods

[snb]

It's no use using Select case if there is only one condition to test.
Did you explore the differences between the operators + and & ?

With varWS
  If .Cells(varRow, 7).Value =WeekDD2.Value & SiteDD2.Value & DeptDD2.Value & i Then
    Controls(TeamMbr & i).Value = .Cells(varRow, 12).Value
    Controls(sDay & "Start" & i).Value = .Cells(varRow, 13).Value
    Controls(sDay & "Finish" & i).Value = .Cells(varRow, 14).Value
  End If
Ed With

[romperstomper]

That was to solve your syntax issue, nothing else.
Are you adding numbers together or concatenating text? If the latter, use '&' not '+'

[nods]

Many thanks. I am concatenating text. I made the change you sugested and the code still doesn't work. My case statment now looks like:

ase Me.WeekDD2.Value & vDay & Me.SiteDD2.Value & Me.DeptDD2.Value & iCnt

I have attached an example of the table the code is looking at. It should find a match in column G and populate the form with columns L,M and N.

Many thanks

RotainputExample.xls

[romperstomper]

That workbook might have been quite useful if it had your form in it...

[nods]

Sorry here is the work book with form. The only button I am concerned with is the 'View Historical Rota' button.

RotainputExample.zip

[romperstomper]

Your Case statement seems fine to me other than that this:

Me.Controls(TeamMbr & iCnt).Value = varWS.Cells(varRow, 12).Value

should be:

Me.Controls("TeamMbr" & iCnt).Value = varWS.Cells(varRow, 12).Value

For future reference, most people find it easier to debug code if the project isn't password protected, and all the required sheets and forms are present for the code to run.

[nods]

Oops my bad. I was trying to upload the entire document but it kept failing.

The password is donkey.

I added the "" as you sugested and it made no difference.

[romperstomper]

I've already unlocked and reviewed it, as I said, and your case statement works for me. What specifically do you mean when you say it doesn't work?

[nods]

So for example if the user selects Week 19 , Talpore and Cleaner then clicks View Historical rota the form should populate with that weeks data.And it doesn't.

[romperstomper]

That's because this line stops it:

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

unless you have already altered the control values to something other than the defaults.

[nods]

Many thanks. That was obvious once you had pointed it out.

The form seems to poulate fine now but only upto line 94. Fore example if was to select week 19, Talpore , cleaner it will populate perfectlty upto line 94(Employee N, On Friday). I was to select Restaurant it doesnt populate at all.

I cant see how this could be possible?

[romperstomper]

You only have data in column A for rows 1 to 93, and that's the column you use to determine the last row of data.

[nods]

Many thanks. Problem solved.