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

Question

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

User · Accepted Answer

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

User · Answer

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   amp   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

User · Answer

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

User · Answer

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

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

Examples related to arrays

Examples related to excel

Examples related to vba