Excel VBA - 将一行单元格值传递给数组，然后将该数组粘贴到单元格的相对引用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14794556/
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
Excel VBA - Pass a Row of Cell Values to an Array and then Paste that Array to a Relative Reference of Cells
提问by Jonathan Ross Charlton
Using Excel (2010) VBA, I am trying to copy (pass) a constant range of cells (whose values recalculate) to an array. Then I am trying to pass that array to a new range of cells, directly below it. After I have done this, I want to again copy (pass) the constant range's new values to the array, and pass these new values to a range directly below the one I previously passed.
使用 Excel (2010) VBA，我试图将恒定范围的单元格（其值重新计算）复制（传递）到一个数组。然后我试图将该数组传递给它正下方的新单元格范围。完成此操作后，我想再次将常量范围的新值复制（传递）到数组，并将这些新值传递到我之前传递的范围正下方的范围。
I know this code is atrocious (I am new to arrays in VBA).
我知道这段代码很糟糕（我是 VBA 中的数组的新手）。
Sub ARRAYER()
Dim anARRAY(5) As Variant
Number_of_Sims = 10
For i = 1 To Number_of_Sims
anARRAY = Range("C4:G4")
Range("C4").Select
ActiveCell.Offset(Number_of_Sims, 0).Select
ActiveCell = anARRAY
Range("C4").Select
Next
End Sub
I sure do appreciate your help!
我真的很感激你的帮助！
Thank you.
谢谢你。
Respectfully,
尊敬，
Jonathan
乔纳森
回答by CuberChase
You are off slightly on a few things here, so hopefully the following helps.
您在这里的一些事情略有偏差，所以希望以下内容有所帮助。
Firstly, you don't need to select ranges to access their properties, you can just specify their address etc. Secondly, unless you are manipulating the values within the range, you don't actually need to set them to a variant. If you do want to manipulate the values, you can leave out the bounds of the array as it will be set when you define the range.
首先，您不需要选择范围来访问它们的属性，您只需指定它们的地址等。其次，除非您正在操作范围内的值，否则您实际上不需要将它们设置为变体。如果您确实想要操作这些值，您可以省略数组的边界，因为它将在您定义范围时设置。
It's also good practice to use Option Explicitat the top of your modules to force variable declaration.
Option Explicit在模块顶部使用强制变量声明也是一种很好的做法。
The following will do what you are after:
以下将做你所追求的：
Sub ARRAYER()
Dim Number_of_Sims As Integer, i As Integer
Number_of_Sims = 10
For i = 1 To Number_of_Sims
'Do your calculation here to update C4 to G4
Range(Cells(4 + i, "C"), Cells(4 + i, "G")).Value = Range("C4:G4").Value
Next
End Sub
If you do want to manipulate the values within the array then do this:
如果您确实想操作数组中的值，请执行以下操作：
Sub ARRAYER()
Dim Number_of_Sims As Integer, i As Integer
Dim anARRAY as Variant
Number_of_Sims = 10
For i = 1 To Number_of_Sims
'Do your calculation here to update C4 to G4
anARRAY= Range("C4:G4").Value
'You can loop through the array and manipulate it here
Range(Cells(4 + i, "C"), Cells(4 + i, "G")).Value = anARRAY
Next
End Sub
回答by chuff
No need for array. Just use something like this:
不需要数组。只需使用这样的东西：
Sub ARRAYER()
Dim Rng As Range
Dim Number_of_Sims As Long
Dim i As Long
Number_of_Sims = 10
Set Rng = Range("C4:G4")
For i = 1 To Number_of_Sims
Rng.Offset(i, 0).Value = Rng.Value
Worksheets("Sheetname").Calculate 'replacing Sheetname with name of your sheet
Next
End Sub
回答by chris neilsen
Since you are copying tha same data to all rows, you don't actually need to loop at all. Try this:
由于您将相同的数据复制到所有行，因此您实际上根本不需要循环。尝试这个：
Sub ARRAYER()
Dim Number_of_Sims As Long
Dim rng As Range
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Number_of_Sims = 100000
Set rng = Range("C4:G4")
rng.Offset(1, 0).Resize(Number_of_Sims) = rng.Value
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
回答by Moosli
When i Tried your Code i got en Error when i wanted to fill the Array.
当我尝试您的代码时，当我想填充数组时出现错误。
you can try to fill the Array like This.
您可以尝试像这样填充数组。
Sub Testing_Data()
Dim k As Long, S2 As Worksheet, VArray
Application.ScreenUpdating = False
Set S2 = ThisWorkbook.Sheets("Sheet1")
With S2
VArray = .Range("A1:A" & .Cells(Rows.Count, "A").End(xlUp).Row)
End With
For k = 2 To UBound(VArray, 1)
S2.Cells(k, "B") = VArray(k, 1) / 100
S2.Cells(k, "C") = VArray(k, 1) * S2.Cells(k, "B")
Next
End Sub
