excel - 数组下标超出范围错误的 For 循环

Question

任何人都知道为什么我在 IF 语句中得到一个下标超出范围的错误。我只是在学习数组，所以我只能假设它与此有关。

Dim CARMA2 As Worksheet
Dim Assignments As Worksheet
Sub data2()

Dim arr() As Variant
Dim CAR() As Variant
arr = Array(Worksheets("Assignments").UsedRange)
CAR = Array(Worksheets("CARMA2").UsedRange)
Dim i As Variant


For x = LBound(CAR, 1) To UBound(CAR, 1)
For i = LBound(arr, 1) To UBound(arr, 1)

If arr(i, 5) = CAR(x, 1) And arr(i, 7) = """" Then
   arr(i, 7) = CAR(x, 3)

End If

Next i
Next x

End Sub

Answer 1

要将范围内的所有值放入二维数组中，请将范围的 Value 属性分配给 Variant，例如

Dim arr As Variant
arr = Worksheets("Assignments").UsedRange.Value

您可以使用 Dim arr() as Variant ，但这是不必要的。它只是将数组的每个元素强制转换为 Variant。但是 Dim arr As Variant将创建一个变体数组(不是变体数组)，并且将根据需要键入元素。
当您创建这种数组时，它是 base 1 数组。所以你的 3、5 和 7 需要考虑到这一点。

Sub data2()

    Dim arr As Variant
    Dim CAR As Variant
    Dim x As Long, i As Long
    
    arr = Worksheets("Assignments").UsedRange.Value
    CAR = Worksheets("CARMA2").UsedRange.Value
    
    For x = LBound(CAR, 1) To UBound(CAR, 1)
        For i = LBound(arr, 1) To UBound(arr, 1)
            If arr(i, 5) = CAR(x, 1) And arr(i, 7) = """" Then
               arr(i, 7) = CAR(x, 3)
               
            End If
        Next i
    Next x

End Sub