Question

下面EXCEL VBA程序执行的时候提示：错误6，溢出，是什么问题？

Sub 找出连续长度的字符串()
Dim str As String
Dim substr As String
Dim t As Integer
Dim M&, h, j&, k&
Dim maxRow As Long
Dim Arr() As String

For M = 1 To 1000
str = Range("A" & M).Value
'判断单元格字符串长度超过30就对非数字字符进行替换
If Len(str) > 30 Then
For i = 1 To Len(str)
substr = Mid(str, i, 1)
If substr >= "0" And substr <= "9" Then
Else
Range("A" & M).Replace substr, "a"
End If
Next i
'判断11位连续数字后，输出该数字和行号
Arr() = Split(Range("A" & M), "a")
k = UBound(Arr)
For j = 0 To k
h = Len(Arr(j))
If h = 11 Then
Range("B" & M).Value = Arr(j)
Range("C" & M).Value = M
End If
Next
End If
Next
End Sub

Answer 1

估计你调试过程中切换过工作表。试试看：

Sub 找出连续长度的字符串()
Dim str As String

Dim substr As String

Dim t As Integer

Dim M&, h, j&, k&

Dim maxRow As Long

Dim Arr() As String

With Sheet1                '注：你的数据所在的工作表名称，修改后再用

For M = 1 To 1000

str = .Range("A" & M).Value             '配合 With 语句，这里用 "." 代替 Sheet1

'判断单元格字符串长度超过30就对非数字字符进行替换

If Len(str) > 30 Then

For i = 1 To Len(str)

substr = Mid(str, i, 1)

If substr >= "0" And substr <= "9" Then

Else

.Range("A" & M).Replace substr, "a"    '配合 With 语句，这里用 "." 代替 Sheet1

End If

Next i

'判断11位连续数字后，输出该数字和行号

Arr() = Split(.Range("A" & M), "a")  '配合 With 语句，这里用 "." 代替 Sheet1

k = UBound(Arr)

For j = 0 To k

h = Len(Arr(j))

If h = 11 Then

.Range("B" & M).Value = Arr(j)    '配合 With 语句，这里用 "." 代替 Sheet1

.Range("C" & M).Value = M         '配合 With 语句，这里用 "." 代替 Sheet1

End If

Next

End If

Next  

  

End With

End Sub