Subscript out of range when passing argument to array

Question

I'm setting up a subroutine to perform matches between two worksheets. The arrays are one dimensional going from the first cell of data to the last, which is held within a variable.

The data in the arrays are not numerical, but if I ReDim them as strings I get a type mismatch in the initialization.

SheetOneLastRow and SheetTwoLastRow are subroutines which find the last row in each sheet to be held in the variables FirstLastRow and SecondLastRow which are declared globally because they are used in other subs.

EDIT 1: The error is on the line:

If search(i) = arr(j) Then

Value of FirstLastRow is 9589 and SecondLastRow is 20750. The search and arr have only been declared here with ReDim.

Sub Match()

SheetOneLastRow
SheetTwoLastRow
Dim i, j As Integer
ReDim arr(SecondLastRow - 2) As Variant
ReDim search(FirstLastRow - 2) As Variant

search = Range(wksv.Cells(2, 11), wksv.Cells(FirstLastRow, 11))
arr = Range(wkst.Cells(2, 6), wkst.Cells(SecondLastRow, 6))

For i = 2 To FirstLastRow
    For j = 2 To SecondLastRow
        If search(i-2) = arr(j-2) Then
        wkst.Cells(j, 3) = wksv.Cells(i, 3)
        End If
    Next j
Next i

End Sub

It would probably help to know on which line the `Subscript out of range` error is thrown (and also the declaration of `arr` and `search` and the values of `SecondLastRow` and `FirstLastRow`, if it's still there the error is thrown). — Matteo NNZ, Jul 14 '19 at 22:38
I don't know your code functionally, but in the case of `search` you're dimensioning it as `FirstLastRow - 2` but then your `i` is going up to `FirstLastRow`. Whatever the value of `FirstLastRow` is, it will go out of range at `i = FirstLastRow - 1`. The same goes for the other array with `j`. — Matteo NNZ, Jul 14 '19 at 22:51
You're right, I'm going to fix that now. Otherwise that shouldn't have an effect on the error, right? — Ramzi Saud, Jul 14 '19 at 22:56

Vityata · Accepted Answer · 2019-07-14T23:50:37.380

0

Search() is a 2D array, and the code is using it as a 1D array.

In general, passing range to arrays is not complicated, but there are a few tricks, you should be aware of. First trick - whenever the range is passed like this:

search = Range(wksv.Cells(2, 11), wksv.Cells(FirstLastRow, 11)) it is passed to a 2-dimensional array. See the blue highlighted line at the screenshot:

The problem with the 2-dimensional arrays is that they are of two dimensions. E.g., you should be looking for Search(2,1) instead of Search(2). Or in the code above it should be: If Search(i,1) = arr(j,1) Then

There are probably better ways to solve the problem, e.g. passing the range to a single dimensional array, as in the example here - https://stackoverflow.com/a/52467171/5448626

This is what would happen, if you force the range to be a 1D array:

Sub Match()

    Dim i, j As Integer
    FirstLastRow = 9589
    SecondLastRow = 20750

    ReDim arr(SecondLastRow - 2) As Variant
    ReDim Search(FirstLastRow - 2) As Variant

    With Worksheets(1) 'put wksv
        Search = Application.Transpose(.Range(.Cells(2, 11), .Cells(FirstLastRow, 11)))
    End With

    With Worksheets(2) 'put wkst
        arr = Application.Transpose(.Range(.Cells(2, 6), .Cells(SecondLastRow, 6)))
    End With

    For i = 2 To FirstLastRow - 2  '-2 is needed because of ReDim arr(SecondLastRow - 2)
        For j = 2 To SecondLastRow - 2
            If Search(i) = arr(j) Then
                Worksheets(1).Cells(j, 3) = Worksheets(2).Cells(i, 3)
            End If
        Next j
    Next i

End Sub

edited Jul 14 '19 at 23:50

answered Jul 14 '19 at 23:04

Vityata

42,633
8
55
100

1

I attempted using this code, but I get the error "object variable or with block variable not set" on the line 'search = application.transpose....' – Ramzi Saud Jul 14 '19 at 23:29
@RamziSaud - This is a typical VBA error for not defined parent worksheet. Make sure to replace back `Worksheets(1)` to `wksv` and `Worksheets(2)` to `wks~something`. – Vityata Jul 14 '19 at 23:36
I had done that already. When I comment out the line with the error then the line 'arr=application..' also has the same error. – Ramzi Saud Jul 14 '19 at 23:41
@RamziSaud - see the edit of the code. Or open a completely new Excel and try to run the code above as copy+paste, adjusting the `Worksheets(1)` and `Worksheets(2)` correspondingly. Then the code should enter correctly the nested loops (but probably would not exit them without an error) – Vityata Jul 14 '19 at 23:47
I've opened a new workbook with your code and I'm back to the original error of subscript out of range. – Ramzi Saud Jul 15 '19 at 00:02
@RamziSaud - are you sure? Did you include the `-2` as in the edit? Try to replace the `FirstLastRow = 9589` and the `SecondLastRow = 20750` with two digit numbers. And do it again, it should work. – Vityata Jul 15 '19 at 00:14
1

It seems to work now, thanks for all your help! As a side note, is there an issue in my code that would cause long processing times? It takes about a minute or so to execute. – Ramzi Saud Jul 15 '19 at 00:52
@RamziSaud - congrats. Concerning the optimization - 9589*20750 is quite a big number, thus it would be tough to get something meaningful. Still, consider using `long` instead of `integer` or `variant` everywhere, you may win a couple of seconds - `Dim i as Long, j As Long` – Vityata Jul 15 '19 at 14:02

Subscript out of range when passing argument to array

1 Answers1