CODEWITHSUNDEEP
vbaexcel
Chaostryder
Chaostryder

Reputation: 223

Excel 2007 vba macro: error 9 subscript out of range

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??

When the error happens and i debug it it tends to show the cause some where here :

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)

SCROLL to BOTTOM for what i think is causing the error

Here is the 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

<---- ??? not sure about importance of the line above because i commented out and the code will still work

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" & Application.Rows.Count).End(xlUp) _
  .Offset(1).Resize(UBound(sData), UBound(sData, 2)) = sData
Next i
Application.EnableEvents = True

Range("K1").Select
Range(ActiveCell.End(xlToRight).Offset(0, 0), ActiveCell).Delete
Rows("2:" & Parameters + 1).Delete

enter image description here enter image description here enter image description here

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

Upvotes: 1

Views: 12327

Answers (1)

Tim Williams
Tim Williams

Reputation: 166156

You're trying to access a value which doesn't exist in your sData or vData array. One or both of them have fewer "rows" or "columns" than you think. Try adding:

Msgbox "sData: #rows=" & ubound(sData,1) & "   #cols=" & _
        ubound(sData,2) & vbcrlf & 
       "vData #rows=" & ubound(vData,1) &  "   #cols=" & ubound(vData,2)

Immediately after

ReDim sData(1 To Parameters, 1 To rData.Columns.Count - 10)

See what values you get.

Upvotes: 3

Related Questions