Arrays - Rows and Columns

I have a macro that grabs information from spreadsheet1 and verifies it then puts it in a new format on spreadsheet2.

Here is the essential problem...

Sub ArrayTest()
Dim MyArray(9) as Integer
Dim MyRange1 as Range
Dim MyRange2 as Range
Dim Counter as Integer

Set MyRange1 = Range("A1:A10")
Set MyRange2 = Range("B1:K1")

For Counter = 0 to 9
MyArray(Counter) = Counter
Next Counter

MyRange1 = MyArray
MyRange2 = MyArray

End Sub

The problem is that I need the Array to fill in properly down a column, not across a row.

A solution that I discovered was to change these two parts...

Dim MyArray(9, 0) as Integer

For Counter = 0 to 9
MyArray(Counter, 0) = Counter
Next Counter

Is this the only solution, or is there a better one...

Originally Posted by kraljb

I have a macro that grabs information from spreadsheet1 and verifies it then puts it in a new format on spreadsheet2.

Here is the essential problem...

Sub ArrayTest()
Dim MyArray(9) as Integer
Dim MyRange1 as Range
Dim MyRange2 as Range
Dim Counter as Integer

Set MyRange1 = Range("A1:A10")
Set MyRange2 = Range("B1:K1")

For Counter = 0 to 9
MyArray(Counter) = Counter
Next Counter

MyRange1 = MyArray
MyRange2 = MyArray

End Sub

The problem is that I need the Array to fill in properly down a column, not across a row.

A solution that I discovered was to change these two parts...

Dim MyArray(9, 0) as Integer

For Counter = 0 to 9
MyArray(Counter, 0) = Counter
Next Counter

Is this the only solution, or is there a better one...

As far as I can see, the only one, the last element of the array seems to align itsself across the row, the second last down the column.

A note is that you can use Option Base 1 to start the array from 1 rather than the default zero, this could help for clarity if you are aligning the array to rows and/or columns.

---

You can also define array with 2 numbers that are lbound and ubound ,
for example:

Dim MyArray(1 To 10, 1 To 1) As Integer

as in the code below:

Dim MyRange1 As Range
Dim MyArray(1 To 10, 1 To 1) As Integer

Set MyRange1 = Range("A1:A10")

For Counter = 1 To 10
MyArray(Counter, 1) = Counter
Next Counter

MyRange1 = MyArray