For-Next 循环和数组 - VBA
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17256563/
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
For-Next Loop and Arrays - VBA
提问by user2512664
I keep making the same mistake, and I have no idea what it is. Every time I make a For-Loop dealing with arrays (usually, trying to read one array and write the values in a second array), it just takes the very last value from the first array and writes it in every single slot of the second array.
我一直在犯同样的错误，我不知道它是什么。每次我对数组进行 For-Loop 处理时（通常，尝试读取一个数组并将值写入第二个数组中），它只会从第一个数组中获取最后一个值并将其写入第二个数组的每个插槽中大批。
Here's a copy of the easiest/shortest one I'm working on. It's supposed to read the values out of A1:A10 and write them to B1:K1.
这是我正在研究的最简单/最短的副本。它应该从 A1:A10 中读取值并将它们写入 B1:K1。
Sub Problem1()
Dim a(1 To 10) As Single
Dim b(1, 10) As Single
Dim i As Integer
For i = 1 To 10
a(i) = Range("A" + CStr(i)).Value
Next i
For i = 1 To 10
b(1, i) = a(i)
Next i
Range("B1:K1") = b(1, 10)
End Sub
回答by Andy G
Range("B1:K1") = b(1, 10)
this only copies a single array-element.
这仅复制单个数组元素。
You could do the following:
您可以执行以下操作：
Range("B1:K1").Value = Application.WorksheetFunction _
.Transpose(Range("A1:A10"))
To store the original values in an array would (using my approach) require it to be declared as a Variant, unfortunately:
不幸的是，要将原始值存储在数组中（使用我的方法）需要将其声明为 Variant：
Sub Problem1()
Dim a As Variant
a = Range("A1:A10")
Range("B1:K1") = Application.WorksheetFunction.Transpose(a)
End Sub
If you still want to make use of both arrays:
如果您仍然想使用这两个数组：
Sub Problem1()
Dim a As Variant
Dim b(1 To 10) As Single
Dim i As Integer
a = Range("A1:A10")
For i = 1 To 10
b(i) = a(i, 1)
Next i
Range("B1:K1") = b
End Sub
回答by Nithin Bairi
Sub Problem1()
Dim a(1 To 10) As Single
Dim b(1, 10) As Single
Dim i As Integer
For i = 1 To 10
a(i) = Range("A" + CStr(i)).Value
Next i
For i = 1 To 10
b(1, i) = a(i)
Next i
Range("B1:K1") = b()
End Sub
回答by mango
For what you want, the better way that you could go about it would be:
对于您想要的，更好的方法是：
Sub Problem1()
Dim a(1 To 10) As Single
Dim i As Integer
For i = 1 To 10
a(i) = Range("A" + CStr(i)).Value
Next i
Range("B1:K1") = a
End Sub
As for what's wrong with your current implementation, it's that you assigned one single to the entire range of a group of cells. better would be to loop through the contents of the array to output it individually into each cell. But vba handles this natively (outputting an array to a range of cells) as illustrated by my example above.
至于您当前的实现有什么问题，是您将单个单元格分配给了一组单元格的整个范围。更好的是遍历数组的内容以将其单独输出到每个单元格中。但是 vba 本身就处理了这个问题（将数组输出到一系列单元格），如我上面的示例所示。
回答by MattD
Sub Problem1()
Dim i, j As Integer
j = 2
For i = 1 To 10
ActiveSheet.Cells(1, j).Value = ActiveSheet.Range("A" & i)
j = j + 1
Next i
End Sub
回答by ja72
Try to make the steps as clear as possible, and read/write all values in one step to avoid having the output change the input (overlapping cells). The flow should be [Read]->[Calc]->[Write].
尝试使步骤尽可能清晰，并在一个步骤中读取/写入所有值，以避免输出更改输入（重叠单元格）。流量应该是[Read]->[Calc]->[Write]。
Sub Problem1()
Dim a() as Variant, b() as Variant, i as Integer
'Get Values
a = Range("A1").Resize(10,1).Value2
'Transform the arrays in any way you need.
'Make sure the intent is clear
ReDim b(1 to 1, 1 to 10)
For i=1 to 10
b(1,i) = a(i,1)
Next i
'Set Values
Range("B1").Resize(1,10).Value2 = b
End Sub
