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

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

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

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

[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