vbscript - How to assign an Excel Range to a 2D array?

Question

Could you please say- how a Excel Range("G2:AA1000") can be assigned to a 2D array? If possible how to return back that 2D array to the same range after performing some operation on that 2D array?After assignment a Range to an 2D array,How each row will be identified from that 2D matrix?

Thanks,

Answer 1

There is an easy way to make changes to an area using an array, and write it out to the same place, or somewhere else.

This example code will copy data from one area to another, using an array:

Sub example()
Dim testdata()
testdata = Range("A1:B13")
Range("D1:E13") = testdata ' simple copy
Range("G1") = testdata ' copy only 1 cell
Range("I1:K22") = testdata 'try to copy too much
End Sub

The testdata array starts from 1, and will extend to the number of columns and rows specified in the range. In this case, testdata(1,1) refers to the data obtained from A1, testdata(1,2) refers to B1, finishing up with testdata(13,1) referring to A13, and testdata(13,2) referring to B13.

Setting the range equal to the array in the next line copies the array into the specified location.

• If the area is smaller than the original array, it will copy only enough of the array to fill that space, so Range("D1")=testdata will only place one cell on the sheet.
• If you specify a larger area, then #N/A will fill the area that is not in the space covered by array elements, so Range("A1:A3")=testdata will fill A1 and A2 with data from the array, but A3 will have #N/A

Result of example program:
Note: A1:B13 is the original data, which gets copied with the subsequent range(??)=testdata