CODEWITHSUNDEEP
excelvba
4purs
4purs

Reputation: 13

Subscript out of range error comparing one array to another

I align one set of data to another looping though cells. Essentially if IDs match and the next condition is satisfied, copy and paste the cell from one data set to the other in a new column.

Looping though cells takes forever. I tried using arrays. I get the error

subscript out of range

in If array2(i, 2) = array1(j, 7) Then.

Dim array1 As Variant
Dim array2 As Variant 
Dim i, j As Integer  

array1 = Range("A2:k100000").Value

array2 = Range("M2:Q834").Value

For i = 1 To UBound(array2, 2)
    For j = 1 To UBound(array1, 2)

        If array2(i, 2) = array1(j, 7) Then '//get a subscript out of range error here

            If array2(i, 3) <= array1(j, 10) And array2(i, 3) >= array1(j, 9) Then

                If IsEmpty(array1(j, 11)) Then array1(j, 11) = array2(i, 1)

            Else: array1(j, 11) = array1(j, 11).Concat(array2(i, 1))

            End If
        End If
    Next j
Next i

Upvotes: 0

Views: 120

Answers (2)

Zack E
Zack E

Reputation: 706

Remove the .Concat from Else: array1(j, 11) = array1(j, 11).Concat(array2(i, 1)) As the & should be used to Concatenate. The below code caused no errors as long as the values in the cells were real numbers. Also for future reference //get a subscript out of range error here should be either rem get a subscript out of range error here or 'get a subscript out of range error here to comment a line of code; using // VBA is expecting an operation to happen hence the error you received.

Sub test()
    Dim array1() As Variant
    Dim array2() As Variant
    Dim i as Long, j As Long


    array1 = Range("A2:K100000").Value

    array2 = Range("M2:Q834").Value

    For i = 1 To UBound(array2, 2)
        For j = 1 To UBound(array1, 2)
            If array2(i, 2) = array1(j, 7) Then
                If array2(i, 3) <= array1(j, 10) And array2(i, 3) >= array1(j, 9) Then
                    If IsEmpty(array1(j, 11)) Then
                        array1(j, 11) = array2(i, 1)
                    Else
                       array1(j, 11) = array1(j, 11) & (array2(i, 1))
                    End If
                End If
            End If
        Next j
    Next i
End Sub

Upvotes: 1

Sam
Sam

Reputation: 5721

I don't get a subscript out of range error, but I do see some other issues. I get a feeling that there are some differences in the code you are running and what you are posting. The things I noted:

Variable types

Dim i, j As Integer

Two problems here. First, only the last variable will become an integer. Second, you should be using Long instead, otherwise you will get an overflow.

Dim i as Long, j As Long

If Then Else
Using Else: is a very dangerous way of doing it. Do

If Condition Then
    'Something
Else
    'Something else
End If

instead. Quite soon you will have no clue of what If the Else belongs to.

UBound
I suspect that 

UBound(array2, 2)

should have been

UBound(array2, 1)

instead. The , 2) gives you the width of the array, but I guess you want the height of the array.

Upvotes: 3

Related Questions