For Loop with Array subscript out of range error-CodePudding

Anyone have any idea why i am getting a subscript out of range error at the IF statement. I am just learning arrays so i can only assume it has to do with that.

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

CodePudding user response：

To put all the values from a range into a 2-d array, assign the Value property of the range to a Variant, like

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

You can use Dim arr() as Variant, but it's unnecessary. It's just coercing every element of the array to a Variant. But Dim arr As Variant will create a variant array (not an array of variants) and the elements will be typed as appropriate.

When you create this kind of array, it's base 1 array. So your 3, 5, and 7 need to account for that.

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