title: Excel 2007 vba macro: error 9 subscript out of range
body: <p>I have a code, written for me by @Jon49 that i adapted to suit my purposes :
  The problem i have with this code is that it works for some of my files but produce an error 9 subscript out of range on others. essentially i have a loop that opens every file in a folder in turn performs the foloowing code. saves it then closes it before opening another one. The format of all the files is the same. so what is producing the error??</p>
  
  <p>When the error happens and i debug it it tends to show the cause some where here :</p>
  
  <pre><code>sData(j, 7) = vData(1, j + 10)
  sData(j, 8) = vData(i, j + 10)
  sData(j, 9) = vData(3, j + 10)
  sData(j, 10) = vData(2, j + 10)
  </code></pre>
  
  <p><strong>SCROLL to BOTTOM  for what i think is causing the error</strong></p>
  
  <p>Here is the code:</p>
  
  <pre><code>Range("k1").Select
  Dim Parameters As String
  Parameters = Range(ActiveCell.End(xlToRight).Offset(0, 0), ActiveCell).Count
  Dim i As Long, j As Long, k As Long
  Dim rData As Range
  Dim sData() As String, sName As String
  Dim wks As Worksheet
  Dim vData As Variant
  Application.EnableEvents = False     'Initialize worksheets
  Set wks = ActiveSheet      'Get data
  Set rData = wks.UsedRange
  vData = rData
  ReDim sData(1 To Parameters, 1 To rData.Columns.Count - 10)
  rData.Offset(1).Clear
  rData.Offset(11).Resize(1).Clear 
  </code></pre>
  
  <p>&lt;---- ??? not sure about importance of the line above because i commented out and the code will still work</p>
  
  <pre><code>For i = 1 To UBound(vData)
    For j = 1 To UBound(sData)
      For k = 1 To 6
        sData(j, k) = vData(i, k)
      Next k
      sData(j, 7) = vData(1, j + 10)
      sData(j, 8) = vData(i, j + 10)
      sData(j, 9) = vData(3, j + 10)
      sData(j, 10) = vData(2, j + 10)
    Next j         'Print transposed data
    wks.Range("A" &amp; Application.Rows.Count).End(xlUp) _
    .Offset(1).Resize(UBound(sData), UBound(sData, 2)) = sData
  Next i
  Application.EnableEvents = True
  </code></pre>
  
  <hr>
  
  <pre><code>Range("K1").Select
  Range(ActiveCell.End(xlToRight).Offset(0, 0), ActiveCell).Delete
  Rows("2:" &amp; Parameters + 1).Delete
  </code></pre>
  
  <p><img src="https://i.stack.imgur.com/De8KW.png" alt="enter image description here"> 
  <img src="https://i.stack.imgur.com/f7jEC.png" alt="enter image description here">
  <img src="https://i.stack.imgur.com/yvWB2.png" alt="enter image description here"></p>
  
  <p><strong>One thing that i noticed is the files that the error seems to be produced on 
  has few parameters ( the two files ive noticed it erroring so far have 9 [will debug to sData(j, 10) = vData(2, j + 10)] and 7 [sData(j, 8) = vData(i, j + 10)]parameters each ) the others it is okay with so far each have more than 10 parameters .   parameters being CH4, NO, NO2 etc</strong></p>