title: Run-time error '9': Subscript out of range for a set workbook statement
body: <p>When trying to run the following code in vba, I keep getting a runtime error stating &quot;subscript out of range&quot;. Upon debugging, it appears the error is in this line: <code> Set wkbRef = Excel.Workbooks(&quot;FileName.xlsx&quot;)</code>.</p>
  <p>I do not know what subscript the system is referring to in this statement. Any troubleshooting tips would be appreciated.</p>
  <p>The following code sums data in the same cell reference for each set of twelve continuous worksheets in an external workbook via a do-while loop.</p>
  <p>Code:</p>
  <pre><code>Sub LoopValues()
  
  Application.ScreenUpdating = False  'prevents premature updates during calculation
  
  ' Define vars
  Dim i As Integer
  Dim wkbRef As Excel.Workbook
  Dim wkbOpen As Excel.Workbook
  ' Dim wks As Excel.Worksheet
  Dim WsName As String
  Dim wsExists As Boolean  ' loop condition
  
  Dim temp As Double
  Dim startVal As Double
  ' vars for Do While
  Dim counter As Integer
  
  
  
  Set wkbRef = Excel.Workbooks(&quot;FileName.xlsx&quot;)
  ' set workbooks (open &amp; reference)
  Set wkbOpen = ActiveWorkbook
  ' Set wks = wkbRef.Worksheets()
  
  
  
  ' Set Counter for do while
  counter = 0
  
  ' exit condition for do while
  wsExists = True
  
  ' declare array for values from sheets
  Dim arr() As Variant
  
  ' create an empty array, and alter in Do While (re dim)
  arr = Array()
  
  Do While wsExists
      
      ' create a string for each sheet in BatchEmissions
      ' record format
      WsName = Format(DateSerial(2020, 2 + counter, 1), &quot;mmyy&quot;)
      
      ' Set ws to wsName
      On Error Resume Next
      Set ws = wbOpen.Sheets(WsName)
      
      ' if sheets(wsName) does not exists Err.Number &lt;&gt; 0
      If Err.Number &lt;&gt; 0 Then
          wsExists = False
          On Error GoTo 0
          
      Else
          
          ' reseize arrray and ensure previous data is saved
          ReDim Preserve arr(UBound(arr) + 1)
          
          ' update array
          arr(UBound(arr)) = ws.Range(&quot;AA208&quot;).Value
      
      End If
          ' increments to next sheet
          counter = counter + 1
  
  Loop
  
  wkbOpen.Close False
  
  ' loop through all values exceptlast 11
  For i = LBound(arr) To UBound(arr) - 11
      
      ' set cell ref to value
      ' to sum arr(0) to arr(11) and increment by i each time
      ' we must transpose the array, and wrap the transposed array inside an index
      ' and for ROWS generate an array each time incremented by i through Evaluate(ROW(1:12) etc
      wb.Sheets(&quot;Sheet1&quot;).Cells(99 + i, &quot;Q&quot;).Value = Application.Sum(Application.Index(Application.Transpose(arr), Evaluate(&quot;Row(&quot; &amp; (1 + i) &amp; &quot;:&quot; &amp; &quot;)&quot;), 0))
  
  Next
  
  Application.ScreenUpdating = True
  </code></pre>
  <p>End Sub</p>