CODEWITHSUNDEEP
arraysexcelvba
user3737057
user3737057

Reputation: 121

Putting a whole row of data into a 2-d array

I have a row which recalculates on a each simulation and I want to keep track of all of the values that are calculated.

Currently I save each simulation directly onto the spreadsheet using copy/paste and offsetting in a loop.

I'm trying to switch this so that as the model is run all simulation results are stored in a 2-D vba array rather than on the sheet and then the whole array is printed to excel right at the end. I have little experience in using arrays, so my attempt is:

Dim res() As Variant
ReDim res (1 To nbSims, 1 To nbLoans)
For i = 1 to nbSims
    ****Code for calculations of each sim***
    res(i,@@@) = Range(cells("",""),Cells("","")).Value
Next i

Normally if I was filling an array one element at a time I'd use res(i,j) and have it reference one cell, but here I'm trying to take a whole row of results and put it into the array at one time. Any suggestions?

EDIT: I think I've determined that I need find out how to populate an array with a set of arrays, is this possible?

Upvotes: 2

Views: 342

Answers (1)

Dunn
Dunn

Reputation: 515

You could use 2 arrays, and combine them together with a loop; where your i in array 1 is locked, and you cycle through j in array one (your full array res()) and through j in array two (new 1D array).

sub RowAdd()
dim i,j,k as long
dim res() as variant
dim arr() as variant
dim numcols as long 'number of items in the row you're adding

ReDim res (1 To nbSims, 1 To nbLoans)
ReDim arr(numcols)

for i=1 to nbSims

   arr=mysheet.range().value

   for j=1 to numCols

      res(i,j)=arr(j)
   next j
next i

Note that you need to redim your array to the right size, or there might be data loss.

This question is similar to one I asked recently:

Putting separate ranges into 2D array

If you'd like to take a look.

Upvotes: 1

Related Questions