用于将数据从单元格复制到数组的 VBA 循环
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24032532/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA Loop for copying data from cell to array
提问by user3333621
I'm new to VBA in excel, I write code for copying cells from sheet to an array. When I run I got run time error. I don't know whats wrong.
我是 excel 中的 VBA 新手，我编写了用于将单元格从工作表复制到数组的代码。当我运行时，出现运行时错误。我不知道怎么了。
Sub DistSystem()
Dim count As Integer
Dim i As Integer
Dim array_rank() As Variant
Dim array_city() As Variant
Dim array_assign() As Variant
count = Sheets("111").Range("Y2").Value
For i = 0 To count
array_city(i) = Range("A" & i).Value
array_rank(i) = Range("E" & i).Value
array_assign(i) = Range("F" & i).Value
Next
For i = 1 To 10
MsgBox array_rank(i, 1)
Next
End Sub
回答by psubsee2003
I suspect you are going to be battle errors in multiple places.
我怀疑你会在多个地方出现战斗错误。
This section of code has 2 significant problems
这一段代码有2个明显的问题
array_city(i) = Range("A" & i).Value
array_rank(i) = Range("E" & i).Value
array_assign(i) = Range("F" & i).Value
First you are trying to assign values to array that do not have any dimensions. You decleared the arrays but you left them dimensionless. You need to define the dimensions before you try to assign values to the array
首先，您尝试将值分配给没有任何维度的数组。您清除了数组，但使它们无量纲。在尝试为数组赋值之前，您需要定义维度
Something like
就像是
Redim array_city(1 to count)
Next you are trying to get a value from Range("A" & i)when the value of iis zero. Cell "A0"does not exists and will also throw an error.
接下来，您尝试从Range("A" & i)的值为i零时获取值。 Cell "A0"不存在，也会抛出错误。
So to rewrite your code as written, you would need to make a few changes:
因此，要按照所写的方式重写代码，您需要进行一些更改：
Sub DistSystem()
Dim count As Integer
Dim i As Integer
Dim array_rank() As Variant
Dim array_city() As Variant
Dim array_assign() As Variant
count = Sheets("111").Range("Y2").Value
Redim array_rank(1 to count)
Redim array_city(1 to count)
Redim array_assign(1 to count)
For i = LBound(array_rank) To UBound(array_rank)
array_city(i) = Range("A" & i).Value
array_rank(i) = Range("E" & i).Value
array_assign(i) = Range("F" & i).Value
Next
For i = 1 To 10
MsgBox array_rank(i)
Next
End Sub
However, you are over complicating how you are reading the values into the array. You can simply read the entire range directly into the array
但是，您将值读入数组的方式过于复杂。您可以简单地将整个范围直接读入数组
Sub DistSystem()
Dim count As Integer
Dim i As Integer
Dim array_rank As Variant 'Notice the arrays are not longer declared with ()
Dim array_city As Variant ' -> this is necessary
Dim array_assign As Variant
count = Sheets("111").Range("Y2").Value
array_city = Range("A1:A" & count).Value
array_rank = Range("E1:E" & count).Value
array_assign = Range("F1:F" & count).Value
For i = 1 To 10
MsgBox array_rank(i, 1)
Next
End Sub
The resulting array with be 2 dimensions, with the Rowvalue as the first dimension and the column as the 2nd dimension. since all of the ranges are a single column, you would access any value by calling array_rank(Row,1)or array_city(Row,1)or array_assign(Row,1).
结果数组为 2 维，Row值作为第一维，列作为第二维。由于所有范围都是一列，因此您可以通过调用array_rank(Row,1)orarray_city(Row,1)或来访问任何值array_assign(Row,1)。
