For Loop with Array subscript out of range error

Question

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

It suggests that the second dimension (the `7` in `arr(i, 7)` as an example) is probably out of bounds/doesn't exist. What are the sizes of these two arrays when your code hits the `if` statement? It may help to note, since you are new to arrays, that arrays almost always start at index 0, not index 1. So perhaps you meant `If arr(i, 4) = CAR(x, 0) And arr(i, 6) = """" Then arr(i, 6) = CAR(x, 2)`? — JNevill, Sep 17 '21 at 17:49
so i need this to be dynamic as the size of the sheets might change which is why i did UsedRange. But i have a feeling that is not correct. — Josh Hudson, Sep 17 '21 at 17:50
Using `Array()` you are assigning a single range object to array `arr`, the same with array `CAR`. So you have index boundaries of `0 To 0` referencing a single object in a zero-based **1-dimensional** array. - Therefore I suspect you intended to create a (1-based!) 2-dim datafield array which can be done e.g. by `arr = ThisWorkbook.Worksheets("Assignments").UsedRange` instead; now you are able to reference values indexed e.g. by `arr(i, 5)` and `arr(i, 7)` etc. @JoshHudson — T.M., Sep 17 '21 at 18:11
This gave me a type mismatch at the arr= line. Basically i have 2 large tabs in my workbook and need to compare data within each. Since they are so big i thought an array would speed things up since a normal for loop is taking an hour plus to run. — Josh Hudson, Sep 17 '21 at 18:20
@JoshHudson Sorry for late response: Testing I had no issue with my code referencing the project's Sheet Code(Name) via e.g. `arr = Sheet1.UsedRange` without adding the `.Value` property explicitly. Meanwhile Dick has already summarized some points worth knowing in his accepted answer concerning especially the differences between `Dim arr As Variant` and `Dim arr() As Variant` and in his comment about the special behaviour of the `.Value` or `.Value2` prop in connection with `UsedRange`. — T.M., Sep 18 '21 at 17:06

score 1 · Accepted Answer · answered Sep 17 '21 at 18:29

1

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

answered Sep 17 '21 at 18:29

Dick Kusleika

32,673
4
52
73

Looks like i had it written our of order i copy pasted yours and it worked. my CAR= was above the dim. facepalm. thanks – Josh Hudson Sep 17 '21 at 18:40
1

It's OK to Dim stuff like you did, I just never do it. The missing piece was the `.Value` at the end of UsedRange. The Value property is the default property of a Range (usually), but the Range object is just special enough that it's trying to use the Cells property as the default in this case. Maybe, I'm kind of guessing. – Dick Kusleika Sep 17 '21 at 18:51
ok so i changed the reference values (i, 7) to (i,6) for the array but i get the subscript out of range. Also it is searching the headers too. Is there a way to have it look 1 row down to start? – Josh Hudson Sep 17 '21 at 18:54
@DickKusleika Would suggest to include your helpful hint in comment above concerning the special behaviour of `UsedRange` in connection with the `.Value` property in your answer. - Fyi In my own prior testing via `arr = Sheet1.UsedRange` I didn't add a `.Value` | `.Value2` property explicitly, but referenced the project's Sheet **Code(Name)** without issues. So I wasn't aware of a possible error proposing an intended implicit value assignment via `ThisWorkbook.Worksheets("Assignments").UsedRange`. – T.M. Sep 18 '21 at 17:23
1

That's very strange. When I use Codename, I don't get the error either. But when I use the Worksheets object, I do. I wonder why those behave differently. – Dick Kusleika Sep 19 '21 at 13:52
If you can call out your range explicitly, you should probably do that. UsedRange can grow to encompass stuff you don't want. If you can use a table, even better. Then you can `Sheet1.ListObjects(1).DataBodyRange.Value`. But if you're stuck with UsedRange, then `Sheet1.UsedRange.Offset(1).Resize(Sheet1.UsedRange.Rows.Count - 1).Value` will get rid of the first row. – Dick Kusleika Sep 19 '21 at 13:54
1

`Dim arr() as Variant arr = Worksheets(1).UsedRange` fails with a type mismatch. But `Dim arr As Variant` doesn't fail. Also, `Dim arr() As Variant arr = Sheet1.UsedRange` doesn't fail. – Dick Kusleika Sep 19 '21 at 13:59
Thanks for response, one never stops learning - **Summary:** A) Assignments to a datafield array by *`UsedRange` without `.Value`* prop and referencing *(Work)Sheets* (via number or sheet name) succeed after declaring the array per se as variant (`Dim arr As Variant`); they will fail after *"coercing every element of the array to a Variant"* (`Dim arr() As Variant`). - B) Referencing a project's Sheet Code(Name) allows both declarations. - Of course I'd prefer *calling out a range explicitly*. @DickKusleika – T.M. Sep 19 '21 at 17:31
Hint: @MathieuGuindon 's post [Properties of ActiveSheet](https://stackoverflow.com/questions/64010564/all-properties-of-application-activesheet-in-excel-object-browser-does-not-lis/64012200#64012200)- though not directly relevant for the "strange" behaviour of the `UsedRange` property - might contribute to a better general understanding how the application resolves targeted accessible types known at compile-time. Direct member calls against `ActiveSheet`|`UsedRange` which are properties pointing to an object are implicitly late-bound & may not know the wanted target object type at run-time. – T.M. Sep 20 '21 at 19:05
1

I think you're on to something. The `Worksheets` property returns a `Sheets` collection object, which and contain `Worksheet` and `Chart` objects. I'll bet it's that "generic" collection object that's breaking how it determines the default property. – Dick Kusleika Sep 20 '21 at 21:05

For Loop with Array subscript out of range error

1 Answers1