0

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
Improve this question
1
  • another thing to point out is to use As Long instead of As Integer due to the limitations on As Integer Commented Nov 27, 2019 at 17:45

2 Answers 2

Reset to default
3

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

I didnt even notice the Dim i, j As Integer. Thanks for pointing that out too along with all of your other excellent information.
Ah you're right, I mean to count the height, but when i put ubound(array2,1) and ubound(array1,1) i then get a mismatch error.
1

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
Improve this answer

2 Comments

ah i see thank you! but the second if statement where it checks if a number is between two other numbers does include decimals. would it not work in that case?
Edited the answer to say real numbers

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.