title: Why am I getting subscript out of range error on VBA?
body: <p>I'm refactoring a code that analyzes 12 tickers and returns the total volume and the return for each ticker. I am getting an out-of-range error for step 3a, and I don't know why. Any ideas why?</p>
  <pre class="lang-vb prettyprint-override"><code>Sub AllStocksAnalysisRefactored()
      'Define startTime and endTime as variables
      Dim startTime As Single
      Dim endTime  As Single
  
      'Ask the client what year they would like to run with input box.
      yearValue = InputBox(&quot;What year would you like to run the analysis on?&quot;)
  
      'Start timer
      startTime = Timer
      
      'Format the output sheet on All Stocks Analysis worksheet
      Worksheets(&quot;All Stock Analysis&quot;).Activate
      
      Range(&quot;A1&quot;).Value = &quot;All Stocks (&quot; + yearValue + &quot;)&quot;
      
      'Create a header row on All Stocks Analysis worksheet
      Cells(3, 1).Value = &quot;Ticker&quot;
      Cells(3, 2).Value = &quot;Total Daily Volume&quot;
      Cells(3, 3).Value = &quot;Return&quot;
  
      'Initialize array of all tickers
      Dim tickers(12) As String
      
      tickers(0) = &quot;AY&quot;
      tickers(1) = &quot;CSIQ&quot;
      tickers(2) = &quot;DQ&quot;
      tickers(3) = &quot;ENPH&quot;
      tickers(4) = &quot;FSLR&quot;
      tickers(5) = &quot;HASI&quot;
      tickers(6) = &quot;JKS&quot;
      tickers(7) = &quot;RUN&quot;
      tickers(8) = &quot;SEDG&quot;
      tickers(9) = &quot;SPWR&quot;
      tickers(10) = &quot;TERP&quot;
      tickers(11) = &quot;VSLR&quot;
      
      'Activate data worksheet
      Worksheets(yearValue).Activate
      
      'Get the number of rows to loop over
      RowCount = Cells(Rows.Count, &quot;A&quot;).End(xlUp).Row
      
      '1a) Create a ticker Index
      Dim tickerIndex As Single
      tickerIndex = 0
  
      '1b) Create three output arrays
      Dim tickerVolumes(12) As Long
      
      Dim tickerStartingPrices(12) As Single
     
      Dim tickerEndingPrices(12) As Single
      
      
      ''2a) Create a for loop to initialize the tickerVolumes to zero.
      For i = 0 To 11
      tickerVolumes(i) = 0
      
          
      ''2b) Loop over all the rows in the spreadsheet.
          For j = 2 To RowCount
      
          '3a) Increase volume for current ticker
              **tickerVolumes(tickerIndex) = tickerVolumes(tickerIndex) + Cells(j, 8).Value**
          
          '3b) Check if the current row is the first row with the selected tickerIndex.
          'If  Then
           If Cells(j - 1, 1).Value &lt;&gt; Cells(j, 1) Then
              tickerStartingPrices(tickerIndex) = Cells(j, 6).Value
              
          'End If
          End If
          
          '3c) check if the current row is the last row with the selected ticker
           'If the next row’s ticker doesn’t match, increase the tickerIndex.
          'If  Then
              If Cells(j + 1).Value &lt;&gt; Cells(j, 1) Then
                  tickerEndingPrices(tickerIndex) = Cells(j, 6).Value
              
  
              '3d Increase the tickerIndex.
                  tickerIndex = tickerIndex + 1
              
          'End If
          End If
         Next j
      'End For loop
      Next i
      
      '4) Loop through your arrays to output the Ticker, Total Daily Volume, and Return.
      For i = 0 To 11
      
          
          Worksheets(&quot;All Stock Analysis&quot;).Activate
          tickerIndex = i
          
          Cells(4 + i, 1).Value = tickers(tickerIndex)
          Cells(4 + i, 2).Value = tickerVolumes(tickerIndex)
          Cells(4 + i, 3).Value = tickerEndingPrices(tickerIndex) / tickerStartingPrices(tickerIndex) - 1
  
      'Format headline with bold and underline
          Worksheets(&quot;All Stock Analysis&quot;).Activate
          With Range(&quot;A3:C3&quot;)
          .Font.Bold = True
          .Borders(xlEdgeBottom).LineStyle = xlContinuous
          End With
          
        'Format number results on All Stock Analysis worksheet
          Range(&quot;B4:B15&quot;).NumberFormat = &quot;#,##0&quot;
          Range(&quot;C4:C15&quot;).NumberFormat = &quot;0.0%&quot;
          Range(&quot;B3:B15&quot;).AutoFit
          
       Next i
        'Add green and red background for positive and negative results, respectively. Use a For loop and conditionals.
        'Define variables for first and last row we are adding background color.
          dataRowStart = 4
          dataRowEnd = 15
  
             'start For loop
                  For i = dataRowStart To dataRowEnd
                  
          
                  'Start conditional
                      If Cells(i, 3) &gt; 0 Then
                          Cells(i, 3).Interior.Color = vbGreen
              
                      Else
                          Cells(i, 3).Interior.Color = vbRed
          
                  'End conditional
                      End If
              'End For loop
                  Next i
     
    
      'End Timer and print msg on how long it took to run the code.
      endTime = Timer
      MsgBox &quot;This code ran in &quot; &amp; (endTime - startTime) &amp; &quot; seconds for the year &quot; &amp; (yearValue)
  
  End Sub
  </code></pre>