CODEWITHSUNDEEP
arraysvbaexcelloops
skkakkar
skkakkar

Reputation: 2828

Syntax error in array program

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.

Upvotes: 1

Views: 148

Answers (1)

user4039065
user4039065

Reputation:

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.

Upvotes: 1

Related Questions