title: Declaring and initializing a dynamic array of sheets in VBA
body: <p>I am trying to create an array of sheets in Excel.  Each sheet has several columns and rows that need to be searched, compared, and filled accordingly.  I am having trouble creating the array of sheets.  I keep getting a Subscript out of range error on line #27.  It happens on all 4 of the sheets if I comment out the preceding ones.</p>
  
  <pre><code>Sub news()
  
      'activate sheets
      Sheet1.Activate
      Sheet2.Activate
      Sheet3.Activate
      Sheet4.Activate
  
      'array of letters for the columns
      Dim alpha(1 To 13) As String
      alpha(1) = "a"
      alpha(2) = "b"
      alpha(3) = "c"
      alpha(4) = "d"
      alpha(5) = "e"
      alpha(6) = "f"
      alpha(7) = "g"
      alpha(8) = "h"
      alpha(9) = "i"
      alpha(10) = "j"
      alpha(11) = "k"
      alpha(12) = "l"
      alpha(13) = "m"
  
      'array of sheets
      Dim shets() As Sheets, sheetCount As Integer
      Set shets(1) = Sheets("Sheet1")
      Set shets(2) = Sheets("Sheet2")
      Set shets(3) = Sheets("Sheet3")
      Set shets(4) = Sheets("Sheet4")
  
      'used to make sure i am not shifted and photos goes to photos, videos to videos, and compliance to compliance
      Dim newShift As Integer
      newShift = 7
  
      'for loop counter variables
      Dim i, j, k As Integer
  
      'goes through the sheets
      For i = 2 To sheetCount
          'goes through the columns
          For j = 3 To 7 Step 2
              'goes through the rows
              For k = 2 To ThisWorksheet.Rows.count
                  If (Sheets(shets(i - 1)).Cells(k, alpha(j)) = Sheets(shets(i)).Cells(k, alpha(j))) Then
                      Sheets(shets(i)).Cells(k, alpha(j + newShift)) = False
                  ElseIf (Sheets(shets(i - 1)).Cells(k, alpha(j)) &lt; Sheets(shets(i)).Cells(k, alpha(j))) Then
                      Sheets(shets(i)).Cells(k, alpha(j + newShift)) = True
                  Else
                      Sheets(shets(i)).Cells(k, alpha(j + newShift)) = "ERROR"
                  End If
              Next
              newShift = newShift - 1
          Next
      Next
  
  End Sub
  </code></pre>