Syntax error in array program

Question

I am trying to write worksheet data range from one place to other using array approach with a view to try array manipulation later. This program works fine.

  Sub Array2Range5()

    Dim Directory As Variant
    Directory = Range("A30:D39").Value2

    For i = LBound(Directory, 1) To UBound(Directory, 1)
    For j = LBound(Directory, 2) To UBound(Directory, 2)
        Worksheets("List").Range(Worksheets("List").Cells(i + 1, j + 1), Worksheets("List").Cells(i + 1, j + 1)) = Directory(i, j)
    Next j
    Next i
End Sub

The following program for same purpose gives run time error ‘9’.Subscript out of range in the following code portion.

           Worksheets("List").Range(Worksheets("List").Cells(i + 1, j + 1), Worksheets("List").Cells(i + 1, j + 1)) = Directory(i, j)

I am not able to correct it and the program is as follows.

 Sub Array2Range6()

    Dim Directory As Variant
    Directory = Range("A30:D39").Value2

    For i = 0 To UBound(Directory, 1)
    For j = 0 To UBound(Directory, 2)
        Worksheets("List").Range(Worksheets("List").Cells(i + 1, j + 1), Worksheets("List").Cells(i + 1, j + 1)) = Directory(i, j)
    Next j
    Next i
  End Sub

My query is whether these lines are wrong in syntax. If so what should be the correct syntax on similar lines

    For i = 0 To UBound(Directory, 1)
    For j = 0 To UBound(Directory, 2)

Any help shall be highly appreciated.

score 1 · Accepted Answer · 2018-03-09 07:13:58Z

1

Dumping values from a worksheet range into an array always results in a 1-based 2-D array not a zero-based 1-D or 2-D array.

Proof this with the following:

Dim Directory As Variant
Directory = Range("A30:D39").Value2

debug.print LBound(Directory, 1) & " to " & UBound(Directory, 1)
debug.print LBound(Directory, 2) & " to " & UBound(Directory, 2)

The first works because you are using the LBound; the latter fails because you are specifying the LBound to be zero when it is actually 1.

Personally, I always use LBound for the lower boundary regardless of whether the array is a zero-based 1-D array, a one-based 1-D array (using Option Base 1 directive) or a one-based 2-D array.

Collectives™ on Stack Overflow

Syntax error in array program

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related