拆分功能在循环内产生'Subscript out of range'错误

Question

下面的代码段试图查看单元列的内容。每个单元格的格式化：" x.xx _-_ x.xx"。例如：5.66-13.44。该代码旨在将每个单元格，将每个字符串转换为双重，将它们与其他数字进行比较，然后重复下一个单元格。

Sub Test()
Dim PC As Worksheet
Dim i As Integer
Dim MaxSpace, MinSpace As Double
Dim MinMax() As String
Set PC = Workbooks("RFQ_Worksheet").Worksheets("Press Choice")
For i = 7 To 52
    MinMax = Split(PC.Cells(i, 8), " - ", 2)
    MaxSpace = CDbl(MinMax(1))
    MinSpace = CDbl(MinMax(0))
    If MaxSpace > 10.3 Then
        'Do some stuff
    End If
Next i
End Sub

包含MaxSpace = CDbl(MinMax(1))的线给出了"范围内的下标"错误。但是，当我用PC.Cells(7, 8)替换PC.Cells(i, 8)时，代码运行正常。

我在这里缺少什么？

Answer 1

基本上，您正在击中没有定界符的循环中的单元格。

修复：

Sub Test()
Dim PC As Worksheet
Dim i As Integer
Dim MaxSpace As Double  'you didn't declare it properly
Dim MinSpace As Double
Dim MinMax() As String
Dim r As Range
Set PC = ThisWorkbook.Worksheets("Press Choice")
For i = 7 To 52
    Set r = PC.Cells(i, 8)
    If InStr(r, " - ") <> 0 Then
        MinMax = Split(PC.Cells(i, 8), " - ", 2)
        MaxSpace = CDbl(MinMax(1))
        MinSpace = CDbl(MinMax(0))
        '... etc
    End If
Next i
End Sub