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.
I know this code is atrocious (I am new to arrays in 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
To Fill a Dynamic ArrayOn the Tools menu, point to Macro and then click Macros. In the Macro dialog box, click fill_array, and then click Run.
Enter an array formula Select the cells where you want to see your results. Enter your formula. Press Ctrl+Shift+Enter. Excel fills each of the cells you selected with the result.
Arrays can be passed to proceedures by putting () after the name of the array variable. Arrays must be passed by reference. If no passing mechanism is specified, e.g. myFunction(arr()) , then VBA will assume ByRef by default, however it is good coding practice to make it explicit.
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 Explicit
at the top of your modules to force variable declaration.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With