2

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
Improve this question
5
  • 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)? Commented Sep 17, 2021 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. Commented Sep 17, 2021 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 Commented Sep 17, 2021 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. Commented Sep 17, 2021 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. Commented Sep 18, 2021 at 17:06

1 Answer 1

Reset to default
2

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
Improve this answer
Sign up to request clarification or add additional context in comments.

10 Comments

Looks like i had it written our of order i copy pasted yours and it worked. my CAR= was above the dim. facepalm. thanks
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.
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.
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.
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.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.