Using an array in a case statment

**nods** · 11-11-2010, 12:34 AM

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