VBA循环从细胞将数据复制到阵列 [英] VBA Loop for copying data from cell to array
问题描述
我在Excel中是新的VBA,我写code从表复制细胞阵列。当我跑我得到了运行时错误。我不知道什么是错。
子DistSystem()昏暗的计数作为整数
昏暗我作为整数
昏暗array_rank()为Variant
昏暗的array_city()为Variant
昏暗array_assign()为Variant 数=表(111),范围(Y2)。价值 对于i = 0到数
array_city(I)=范围(A&放大器; I)。价值
array_rank(I)=范围(E和放大器;我)。价值
array_assign(ⅰ)=范围(F&放大器;ⅰ)。价值
下一个 对于i = 1到10
MSGBOX array_rank(I,1)
下一个结束小组
我怀疑你将要在多个地方的战斗错误。
code本节有2显著问题
array_city(I)=范围(A&放大器; I)。价值
array_rank(I)=范围(E和放大器;我)。价值
array_assign(ⅰ)=范围(F&放大器;ⅰ)。价值
首先,你正试图将值分配给数组没有任何尺寸。您decleared阵列,但你离开了他们量纲。您需要定义尺寸您尝试将值分配给前阵
像
的东西 REDIM array_city(1来算)
接下来,您正在试图从 A值范围(A&放大器; I)
在 i的值
是零。 细胞A0
不存在,也将抛出一个错误。
于是重写你的code书面,你需要做一些修改:
子DistSystem()昏暗的计数作为整数
昏暗我作为整数
昏暗array_rank()为Variant
昏暗的array_city()为Variant
昏暗array_assign()为Variant 数=表(111),范围(Y2)。价值 REDIM array_rank(1来算)
REDIM array_city(1来算)
REDIM array_assign(1来算) 对于i = LBOUND(array_rank)为UBound函数(array_rank)
array_city(I)=范围(A&放大器; I)。价值
array_rank(I)=范围(E和放大器;我)。价值
array_assign(ⅰ)=范围(F&放大器;ⅰ)。价值
下一个 对于i = 1到10
MSGBOX array_rank(I)
下一个结束小组
不过,你是在复杂的你是如何读值到数组。你可以简单地直接读取整个范围分成数组
子DistSystem()昏暗的计数作为整数
昏暗我作为整数
昏暗array_rank为Variant通知中的数组是不能再与申报()
昏暗array_city为Variant' - >这是必要的
昏暗array_assign为Variant 数=表(111),范围(Y2)。价值 array_city =范围(A1:A&放大器;计数).value的
array_rank =范围(E1:E&放大器;计数).value的
array_assign =范围(F1:F&放大器;计数).value的 对于i = 1到10
MSGBOX array_rank(I,1)
下一个结束小组
由此产生的与阵列是2维,用行
值作为第一个维度和列第二维。因为所有的范围都是一列,你会通过调用 array_rank(行,1)
或 array_city(行,1)<访问任何价值/ code>或
array_assign(行,1)
。
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.
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
I suspect you are going to be battle errors in multiple places.
This section of code has 2 significant problems
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 i
is zero. Cell "A0"
does not exists and will also throw an error.
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 Row
value 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)
.
这篇关于VBA循环从细胞将数据复制到阵列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!