VBA “运行错误‘9’、下标越界”

[NG1688]

第一次写 请各位大侠帮忙
Sub 汇出()
'求出资料中列出的钢卷数量
a1 = Workbooks("精度指标统计样表.xls").Application.Sheets("sheet1").UsedRange.Rows.Count

'循环将钢卷号放入数组中
For i = 2 To a1
Dim name As String
Dim name1 As String
name = Workbooks("精度指标统计样表.xls").Worksheets("sheet1").Range("C" & i)
name1 = "Prof_" + name + ".xls"
'定义excel文件对象
Dim file1 As Excel.Workbook
Set file1 = Workbooks.Open("D:\excel编程\" & name1)
'求出资料中列出的测试笔数
a2 = Workbooks("D:\excel编程\" & name1).Application.Sheets("Porf_" & name).UsedRange.Rows.Count//这句标注出现问题
Dim b As Double
b = 0
Dim b1 As Double
b1 = 0
Dim b2 As Double
b2 = 0
Dim b3 As Double
b3 = 0
Dim b4 As Double
b4 = 0
Dim b5 As Double
b5 = 0
Dim b6 As Double
b6 = 0
Dim b7 As Double
b7 = 0
Dim b8 As Double
b8 = 0
Dim b9 As Double
b9 = 0
For j = 2 To a2
Dim a3 As Double
Dim a4 As Double
a3 = file1.Worksheets("Prof_" + name).Range("G" & j) * 1000 - file1.Worksheets("Prof_" + name).Range("D" & j)
a4 = file1.Worksheets("Prof_" + name).Range("H" & j) - file1.Worksheets("Prof_" + name).Range("E" & j)
If (a3 > 0 And ae <= 30) Or (a3 >= -30 And a3 < 0) Then
b = b + 1
ElseIf (a3 > 30 And ae <= 50) Or (a3 >= -50 And a3 < -30) Then
b1 = b1 + 1
ElseIf (a3 > 50 And ae <= 100) Or (a3 >= -100 And a3 < -50) Then
b2 = b2 + 1
ElseIf (a3 > 100 And ae <= 200) Or (a3 >= -200 And a3 < -50) Then
b3 = b3 + 1
ElseIf a3 > 200 Or a3 < -200 Then
b4 = b4 + 1
End If

If a4 <= 0 Then
b5 = b5 + 1
ElseIf a4 > 0 And a4 <= 5 Then
b6 = b6 + 1
ElseIf a4 > 5 And a4 <= 12 Then
b7 = b7 + 1
ElseIf a4 > 12 And a4 <= 18 Then
b8 = b8 + 1
ElseIf a4 > 18 Then
b9 = b9 + 1
End If
Next
Workbooks("精度指标统计样表.xls").Worksheets("sheet1").Range("D" & i) = b / (a2 - 1)
Workbooks("精度指标统计样表.xls").Worksheets("sheet1").Range("E" & i) = b1 / (a2 - 1)
Workbooks("精度指标统计样表.xls").Worksheets("sheet1").Range("F" & i) = b2 / (a2 - 1)
Workbooks("精度指标统计样表.xls").Worksheets("sheet1").Range("G" & i) = b3 / (a2 - 1)
Workbooks("精度指标统计样表.xls").Worksheets("sheet1").Range("H" & i) = b4 / (a2 - 1)
Workbooks("精度指标统计样表.xls").Worksheets("sheet1").Range("I" & i) = b5 / (a2 - 1)
Workbooks("精度指标统计样表.xls").Worksheets("sheet1").Range("J" & i) = b6 / (a2 - 1)
Workbooks("精度指标统计样表.xls").Worksheets("sheet1").Range("K" & i) = b7 / (a2 - 1)
Workbooks("精度指标统计样表.xls").Worksheets("sheet1").Range("L" & i) = b8 / (a2 - 1)
Workbooks("精度指标统计样表.xls").Worksheets("sheet1").Range("M" & i) = b9 / (a2 - 1)
Next

End Sub

[booksoon]

.UsedRange.Rows.Count 这个是正规不写法

[chinaboyzyq]

name = Workbooks("精度指标统计样表.xls").Worksheets("sheet1").Range("C" & i)
看看有当前sheet吗？（"Porf_" & name）

[Tiger_Zhao]

a)Workbooks 的索引是不带路径的
b)Sheets 直接属于 WorkBook

a2 = Workbooks(name1).Sheets("Porf_" & name).UsedRange.Rows.Count

前面已经取得了 WorkBook 对象，其实更直接的是

a2 = file1.Sheets("Porf_" & name).UsedRange.Rows.Count