title: VBA Variant Index out of range
body: <p>I am new to VBA and I need to expand an existing Worksheet and keep its formatting. There are 7 sections with variable length (in rows) and a width of 14 columns that need to be completed. So what I am trying to do is the following:</p>
  
  <ul>
  <li>find the lines where the sections start</li>
  <li>select the data out of each section and save it into an array (i thought about this as an array of length 7 and each entry contains a 2-dim. array with the data in it)</li>
  <li>select my new data and expand the existing array (created in the last step) with that new data</li>
  <li>override the current sheet with my new created array</li>
  <li>add formatting</li>
  </ul>
  
  <p>I managed to do step 1 and am currently struggling at step 2: I need to create an array with variable length where I can insert the data.</p>
  
  <p>My code so far:</p>
  
  <pre><code>' this should create the array with the 7 entries
  ' "myArray" contains the row-numbers where the sections start
  Function GenerateSheetArray(sheet As Worksheet, myArray As Variant) As Variant
  
      Dim finalArray As Variant
      Dim myInt As Integer
  
      'here each entry should be filled
      For i = 0 To 6
          myInt = myArray(i)
          finalArray(i) = GenerateArrayPart(sheet, myInt)
      Next
  
      GenerateSheetArray = finalArray
  
  End Function
  
  'This should fill each entry with the data of corresponding section
  Function GenerateArrayPart(sheet As Worksheet, headline As Integer) As Variant
      Dim leftIndex As Integer, rightIndex As Integer, rowcount As Integer
      Dim sheetArray() As Variant
  
      rowcount = 0
      leftIndex = 1
      rightIndex = 14
      i = headline + 1
  
      Do While sheet.Cells(i, 1) &lt;&gt; ""
          rowcount = rowcount + 1
          i = i + 1
      Loop
  
      If (rowcount &gt; 0) Then
          For colIndex = leftIndex To rightIndex
              For rowIndex = 1 To rowcount
                  Row = headline + rowIndex
                  sheetArray(rowIndex - 1, colIndex - 1) = sheet.Cells(Row, colIndex)
              Next
          Next
      End If
  
      GenerateArrayPart = sheetArray
  End Function
  </code></pre>
  
  <p>Now my problem is, that VBA throws an error at this line:</p>
  
  <pre><code>'atm rowIndex and colIndex are 1, Row is 40
  'I know that there is data in that cell
  sheetArray(rowIndex - 1, colIndex - 1) = sheet.Cells(Row, colIndex)
  </code></pre>
  
  <p>VBA says:</p>
  
  <blockquote>
    <p>Index out of range</p>
  </blockquote>
  
  <p>in method <code>GenerateArrayPart</code>.
  How can this happen? I thought that <code>variant</code> can be pretty much everything and also does not need bounds to be used? </p>