如何将 Excel 范围分配给二维数组? [英] How to assign an Excel Range to a 2D array?

问题描述

你能说 - 如何将 Excel 范围(G2:AA1000")分配给二维数组?如果可能的话，如何在对该二维数组执行某些操作后将该二维数组返回到相同的范围?在将范围分配给二维数组后，如何从该二维矩阵中识别每一行?

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?

谢谢，

推荐答案

有一种简单的方法可以使用数组对区域进行更改，并将其写到同一个地方或其他地方.

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

testdata 数组从 1 开始，将扩展到范围内指定的列数和行数.本例中，testdata(1,1)指的是从A1得到的数据，testdata(1,2)指的是B1，以 testdata(13,1) 引用 A13 和 testdata(13,2) 引用 <强>B13.

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.

• 如果该区域小于原始数组，它只会复制足够的数组来填充该空间，因此 Range("D1")=testdata 只会在工作表上放置一个单元格.
• 如果指定更大的区域，那么#N/A会填充数组元素覆盖的空间以外的区域，所以Range("A1:A3")=testdata 将使用数组中的数据填充 A1 和 A2，但 A3 将具有 #N/A

• 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

示例程序的结果:
注意:A1:B13 为原始数据，与后续的range(??)=testdata 一起复制

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

这篇关于如何将 Excel 范围分配给二维数组?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！