下标超出范围的VBA变量数据

Question

我对excel VBA有点陌生，经过一些研究，我拼凑了一些工作代码(见底部)。基于这些代码和其他一些东西，我发现我创造了这个混乱:

Sub TranslateNewBOM()

Dim NewFootPrint As Variant
Dim Translated As Variant
Dim temp As String
Dim n As Long
Dim MaxRow As Integer
Do
    MaxRow = n
    n = n + 1
Loop Until Cells(n, 3).Value = "stop"
Cells(3, 8).EntireColumn.Insert
NewFootPrint = Range(Cells(3, 7), Cells(MaxRow, 7)).CurrentRegion.Value
Translated = Range(Cells(3, 8), Cells(MaxRow, 8)).CurrentRegion.Value

For i = 3 To MaxRow
    temp = NewFootPrint(i, 7)  'THIS IS THE LINE THAT GIVES ME THE ERROR
    temp = Left(temp, 3)
    If temp = "" Then
        Cells(i, 5).Value = "void"
    End If
    If temp = "CAP" Then
        Translated(i, 8).Value = "SMC" & Right(NewFootPrint(i, 7).Text, _
        Len(NewFootPrint(i, 7).Text) - 3)
        Translated(i, 8) = Replace(Translated(i, 8).Text, " ", "-")
    End If
Next i
End Sub

这个错误对我来说没有意义;可能是因为我不完全理解VBA中的变体或数组。但是，当我将其与我编写的其他代码进行比较时，语法在NewFootPrint方面几乎相同。唯一的区别是所涉及的数字要大得多。这里是工作代码，由于其糟糕的命名约定，它还在进行中。NewFootPrint ~= DataRangeNew在我的脑海里。

编辑:底部代码不再工作了。我不记得改变了什么，但现在同样的超出范围的错误弹出。我的头发都掉光了

Sub GetandSortBOM()
' Data is imported through a template from a raw database.
' Variable declarations
Dim xnum As Integer
Dim MaxRows As Long
Dim Rng As Variant
Dim DataRangeNew As Variant
Dim DataRangeOld As Variant
Dim DataRangeNewFoot As Variant
Dim DataRangeNewTo As Variant
Dim DataRangeNewFootTo As Variant
Dim Irow As Long
Dim rows As Long
Dim MaxCols As Long
Dim MyVarOld As String
Dim MyVarNew As String
Dim temp() As String

DataRangeNew = Range(Cells(2, 12), Cells(1587, 12)).CurrentRegion.Value         ' These work together
DataRangeNewFoot = Range(Cells(2, 13), Cells(1587, 13)).CurrentRegion.Value     ' and store data in
DataRangeOld = Range(Cells(3, 3), Cells(MaxRows, 3)).CurrentRegion.Value        ' columns without cell
DataRangeNewTo = Range(Cells(3, 8), Cells(MaxRows, 8)).CurrentRegion.Value      ' manipulation. Too much
DataRangeNewFootTo = Range(Cells(3, 7), Cells(MaxRows, 7)).CurrentRegion.Value  ' data to go through without
Rng = Range(Cells(3, 7), Cells(MaxRows, 8)).CurrentRegion.Value
NumRows = Range(Cells(2, 12), Cells(1587, 12)).CurrentRegion.rows.Count

For rows = 3 To MaxRows
    MyVarOld = DataRangeOld(rows, 3)
    For Irow = 1 To NumRows
                                         ''''''''''''''''''''''''''''''''''''''''''''''
        MyVarNew = DataRangeNew(Irow, 12)' Why does this work, but not my other code? '
                                         ''''''''''''''''''''''''''''''''''''''''''''''
        If MyVarOld = MyVarNew Then
            DataRangeNewTo(rows, 8) = DataRangeOld(rows, 3)
            DataRangeNewFootTo(rows, 7) = DataRangeNewFoot(Irow, 13)
        End If
    Next Irow
Next rows
' Combines 2 columns of new data into a 2D array
ReDim temp(1 To MaxRows, 1 To 2)
' Puts the information into the 2D array
For i = 3 To MaxRows
    Rng(i, 7) = DataRangeNewFootTo(i, 7)
    Rng(i, 8) = DataRangeNewTo(i, 8)
Next i
' Puts 2D array in cells
Range(Cells(3, 7), Cells(MaxRows, 7)).CurrentRegion = Rng

End Sub

错误是运行时错误'9':下标超出范围。对我来说，它似乎在这个范围内;至少与我的旧代码相比是这样的。帮助吗?

Answer 1

在第一种情况下:

NewFootPrint = Range(Cells(3, 7), Cells(MaxRow, 7)).CurrentRegion.Value

将不创建一个有7列的数组，因为它是一个单列。:)所以，试着像NewFootPrint(i, 7)一样索引它是行不通的。您应该像这样初始化您的Variant:

NewFootPrint = Range(Cells(3, 7), Cells(MaxRow, 7)).Value

或者更好:

With Worksheets("whatever worksheet use")
    ' ...
    NewFootPrint = .Range(.Cells(3, 7), .Cells(MaxRow, 7)).Value
    ' ...
End With

，并像这样访问它:

NewFootPrint(i, 1)

后来编辑:获取从(r1, c1)到(r2, c2)范围内的数据，其中r2 ≥R1和c2 ≥c1，将创建一个数组(1 To r2-r1+ 1,1 To c2-c1+1)。所以你应该检查所有的 for循环和所有的索引。

• 由于您使用CurrentRegion, NewFootPrint和Translated可能包含相同的值。这是你的意图吗?否则，删除CurrentRegion
• 你确定数组有7列吗?
• For rows = 3 To MaxRows应该是For rows = 0 To MaxRows - 4
  因为这样的数组是从零开始的。
• 也可以是Dim NewFootPrint as Range然后是
  Set NewFootPrint = Range(Cells(3, 7), Cells(MaxRow, 7))