EXCEL中运行VBA代码提示错误‘6’，溢出，如何解决？请各位大侠帮忙，谢谢！！

Question

程序从sheet1中提取数据，统计参加比赛各代表队获得1——8名的人数。当运行到if时，就溢出，请各位大侠看一下。
Private Sub 金银铜()
Dim a, b, c, d, e, f, g, h, j, k, i As Integer
Dim m, n As String
b = 0
c = 0
d = 0
e = 0
f = 0
g = 0
h = 0
j = 0
k = 0
For a = 3 To Rows.Count
m = Cells(a, 2)
n = Cells(a, 3)
For i = 5 To Sheet1.Rows.Count ' 从sheet1中第5行循环到最后的数据行
If (Sheet1.Cells(i, 4) = m And Sheet1.Cells(i, 5) = n And Sheet1.Cells(i, 10) <> "") Then ' 从sheet1中查看单位、组别是是否相同，并且已得分
If Sheet1.Cells(i, 9) = 1 Then ' 查看所得名次，进行记数
b = b + 1
ElseIf Sheet1.Cells(i, 9) = 2 Then
c = c + 1
ElseIf Sheet1.Cells(i, 9) = 3 Then
d = d + 1
ElseIf Sheet1.Cells(i, 9) = 4 Then
e = e + 1
ElseIf Sheet1.Cells(i, 9) = 5 Then
f = f + 1
ElseIf Sheet1.Cells(i, 9) = 6 Then
g = g + 1
ElseIf Sheet1.Cells(i, 9) = 7 Then
h = h + 1
ElseIf Sheet1.Cells(i, 9) = 8 Then
j = j + 1
End If
If Sheet1.Cells(i, 11) <> "" Then
k = k + 1
End If
End If
Next i
Cells(a, 4) = b
Cells(a, 5) = c
Cells(a, 6) = d
Cells(a, 7) = e
Cells(a, 8) = f
Cells(a, 9) = g
Cells(a, 10) = h
Cells(a, 11) = j
Cells(a, 12) = k
Next a
End Sub

Answer 1

你定义的i是整形变量。。整形变量的取值范围是-32768到32767.。。
但是你的For i = 5 To Sheet1.Rows.Count 这句Sheet1.Rows.Count哪怕是EXECL2003都有65536.。。明显超出了32767，不溢出才有鬼了。。追问

请问i取什么值？sheet1中实际行数只有3480行。

追答

两种办法呗。再不确定循环次数的情况下要嘛用do while ……loop循环，如果非要用for循环的话，你又确定实际行数只有3480行，就写for i= 5 to 3480 呗