Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Excel VBA - Pass a Row of Cell Values to an Array and then Paste that Array to a Relative Reference of Cells

Tags:

arrays

excel

vba

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

like image 601
Jonathan Charlton Avatar asked Feb 10 '13 03:02

Jonathan Charlton


People also ask

How do I populate an array in Excel VBA?

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.

How do you populate an array in Excel?

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.

How do you pass an array to a sub in VBA?

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.


1 Answers

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
like image 164
CuberChase Avatar answered Sep 24 '22 23:09

CuberChase