Excel 2007 vba 宏：错误 9 下标超出范围
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7420510/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel 2007 vba macro: error 9 subscript out of range
提问by Chaostryder
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??
我有一个代码，由@Jon49 为我编写，适合我的目的：我使用此代码的问题是它适用于我的某些文件，但在其他文件上产生错误 9 下标超出范围。本质上，我有一个循环打开文件夹中的每个文件，依次执行以下代码。保存它然后在打开另一个之前关闭它。所有文件的格式都是一样的。那么是什么产生了错误？
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
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
我注意到的一件事是似乎产生错误的文件的参数很少（我注意到它到目前为止出错的两个文件有 9 个 [将调试到 sData(j, 10) = vData(2, j + 10) ] 和 7 个 [sData(j, 8) = vData(i, j + 10)]parameters each ) 其他的都可以，到目前为止每个都有超过 10 个参数。参数为 CH4、NO、NO2 等
回答by Tim Williams
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:
您正在尝试访问 sData 或 vData 数组中不存在的值。它们中的一个或两个的“行”或“列”比您想象的要少。尝试添加：
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.
看看你得到了什么值。
