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

Why do you want to use an array? Am I correct in understanding that you want to copy the (changing) values into C4:G4 into the row below? — chuff
– chuff, Commented Feb 10, 2013 at 4:12
Hi Jonathon, can you pls add an example of what you are doing, perhaps a screenshot? It isn't clear to me (at least) — brettdj
– brettdj, Commented Feb 10, 2013 at 4:16

CuberChase · Accepted Answer · 2017-07-22 22:03:33Z

9

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

edited Jul 22, 2017 at 22:03

answered Feb 10, 2013 at 4:14

CuberChase

4,5185 gold badges36 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jonathan Charlton Over a year ago

Thank you Cuber Chase! Using your answer and Chuff's, I'm getting a better understanding of this. I really do appreciate it.

CuberChase Over a year ago

No worries :). Using arrays is almost always faster and beneficial than working directly with ranges. It's a good habit to get into.

Taylor Raine Over a year ago

In line 3 of the second block, you are missing a space character.

chuff · Accepted Answer · 2013-02-10 04:34:27Z

3

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

edited Feb 10, 2013 at 4:34

answered Feb 10, 2013 at 4:17

chuff

5,9061 gold badge23 silver badges26 bronze badges

1 Comment

Jonathan Charlton Over a year ago

Thank you Chuff. The reason I'm looking to use an array is because currently I'm running about 10,000 - 100,000 simulations. Currently, I'm just copying the range, and then pasting it in the right place using Active Cell Offset. I really appreciate the advice. Just one question, would this work as fast as an array? I'm trying to get my run time down from 1 hour to 5 to 10 minutes.

chris neilsen · Accepted Answer · 2013-02-10 09:00:57Z

2

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

answered Feb 10, 2013 at 9:00

chris neilsen

53.3k10 gold badges91 silver badges127 bronze badges

Comments

Moosli · Accepted Answer · 2016-07-01 11:40:48Z

0

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

answered Jul 1, 2016 at 11:40

Moosli

3,2853 gold badges22 silver badges48 bronze badges

Collectives™ on Stack Overflow

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

4 Answers 4

3 Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related