Build and Add To an Array

[jkguy]

Hello everyone,

Patience with the newbie please. I've been able to find lot's of VBA help here over the years, and am completely stumped with my new task and need help.

I have a model, powered by VBA, that runs multiple iterations. Each iteration results in an output like: (the 9 data cells change each iteration. Companies and headers stay the same)

[TABLE="width: 269"]
<tbody>[TR]
[TD]Company[/TD]
[TD] Cost[/TD]
[TD] Profit[/TD]
[TD] Margin[/TD]
[/TR]
[TR]
[TD]Company A[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10%[/TD]
[/TR]
[TR]
[TD]Company B[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]11%[/TD]
[/TR]
[TR]
[TD]Company C[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]15%[/TD]
[/TR]
</tbody>[/TABLE]

I need to capture each iteration as a row (sample margin %'s are rounded...I know):

[TABLE="width: 780"]
<tbody>[TR]
[TD="align: center"]Iteration[/TD]
[TD="align: center"]Company[/TD]
[TD="align: center"]Cost[/TD]
[TD="align: center"]Profit[/TD]
[TD="align: center"]Margin[/TD]
[TD="align: center"]Company[/TD]
[TD="align: center"]Cost[/TD]
[TD="align: center"]Profit[/TD]
[TD="align: center"]Margin[/TD]
[TD="align: center"]Company[/TD]
[TD="align: center"]Cost[/TD]
[TD="align: center"]Profit[/TD]
[TD="align: center"]Margin[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]90[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]11%[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]40[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]15%[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]60[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]13%[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]85[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]11%[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]75[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]8%[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]65[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]12%[/TD]
[/TR]
</tbody>[/TABLE]

Rather than copy / paste each row of the iteration into another area, I'd like to capture all the data in an array. Each new iteration would build a new row in the array. If I run 10 iterations, I would want to output the final array of 10 data rows and 13 columns. (The real set of iteration data in the model has about 100 rows and 25 columns, running 5,000 iterations.)

I hope this makes sense.

Any help with VBA code to build and output this array would be of enormous help.

Thanks!

jkguy

 

[NdNoviceHlp]

Hi jkguy and Welcome to the Board. Here's an outline for an array approach. HTH. Dave

Option Base 1
Private Sub Test()
Dim TotCpm() As Variant
'loop rows here
ReDim TotCpm(RowCnt, 3, 3)
'row number, 3 Companies, 1:cost 2:profit 3:margin
'load array
'ie. row #1 , company A
TotCpm(1, 1, 1) = cost 'company A
TotCpm(1, 1, 2) = profit 'company A
TotCpm(1, 1, 3) = margin 'company A
'ie. row #1 , company B
TotCpm(1, 2, 1) = cost 'company B
TotCpm(1, 2, 2) = profit 'company B
TotCpm(1, 2, 3) = margin 'company B
'.etc
'ie. row #2 , company A
TotCpm(2, 1, 1) = cost 'company A
TotCpm(2, 1, 2) = profit 'company A
TotCpm(2, 1, 3) = margin 'company A
'etc.
End Sub

 

[NdNoviceHlp]

That wasn't right. This is better. Dave

Option Base 1
Private Sub Test()
Dim TotCpm() As Variant
'loop rows here
ReDim Preserve TotCpm(3, 3, Rowcnt)
'3 Companies,(1:cost 2:profit 3:margin), row number
'load array
'ie. company A, row #1  ,
TotCpm(1, 1, 1) = cost 'company A
TotCpm(1, 2, 1) = profit 'company A
TotCpm(1, 3, 1) = margin 'company A
'ie. company B, row #1 
TotCpm(2, 1, 1) = cost 'company B
TotCpm(2, 2, 1) = profit 'company B
TotCpm(2, 3, 1) = margin 'company B
'.etc
'ie. company A, row #2  ,
TotCpm(1, 1, 2) = cost 'company A
TotCpm(1, 2, 2) = profit 'company A
TotCpm(1, 3, 2) = margin 'company A
'etc.
End Sub

 

[Norie]

jkguy

How does the model produce the output for each iteration and where does the output go?

 

[jkguy]

NdNoviceHlp...Thank you! I'll test it shortly when I get to work.

jkguy

How does the model produce the output for each iteration and where does the output go?

It's a simple 'calculate' function to run an iteration. The model is set to Application.Calculation=xlManual.

The outputs will be put in another sheet to be used for charting.

jkguy
<code>
</code>

 

[Norie]

So every iteration produces a table like the first table in the original post and you would want that 'flatten' out into one row in and added to an array?

Do the columns/iterations vary?

 

[NdNoviceHlp]

I'm guessing Norie will provide U a better solution but here goes another adaptation of the previous code. HTH. Dave

Option Explicit
Option Base 1
Private Sub Test23()
Dim TotCpm() As Variant, LastRow As Double, RowCnt As Integer, cnt As Integer
With Sheets("bob")
    LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
End With
ReDim TotCpm(LastRow - 1, 3, 4)
'row number, 3 Companies, 1:company, 2:cost 3:profit 4:margin
'load array
For RowCnt = 2 To LastRow
TotCpm(RowCnt - 1, 1, 1) = Sheets("bob").Cells(RowCnt, "B") 'Co A
TotCpm(RowCnt - 1, 1, 2) = Sheets("bob").Cells(RowCnt, "C") 'cost
TotCpm(RowCnt - 1, 1, 3) = Sheets("bob").Cells(RowCnt, "D") 'profit
TotCpm(RowCnt - 1, 1, 4) = Sheets("bob").Cells(RowCnt, "E") 'margin
TotCpm(RowCnt - 1, 2, 1) = Sheets("bob").Cells(RowCnt, "F") 'Co B
TotCpm(RowCnt - 1, 2, 2) = Sheets("bob").Cells(RowCnt, "G") 'cost
TotCpm(RowCnt - 1, 2, 3) = Sheets("bob").Cells(RowCnt, "H") 'profit
TotCpm(RowCnt - 1, 2, 4) = Sheets("bob").Cells(RowCnt, "I") 'margin
TotCpm(RowCnt - 1, 3, 1) = Sheets("bob").Cells(RowCnt, "J") 'Co C
TotCpm(RowCnt - 1, 3, 2) = Sheets("bob").Cells(RowCnt, "K") 'cost
TotCpm(RowCnt - 1, 3, 3) = Sheets("bob").Cells(RowCnt, "L") 'profit
TotCpm(RowCnt - 1, 3, 4) = Sheets("bob").Cells(RowCnt, "M") 'margin
Next RowCnt
'***test array output
'row#2  (array pos'n #1 ) all companies names
For cnt = LBound(TotCpm) To UBound(TotCpm)
MsgBox TotCpm(1, cnt, 1)
Next cnt
'cost/profit/margin output   row#3(array pos'n #2 ) for Company C(3)
For cnt = LBound(TotCpm) + 1 To UBound(TotCpm) + 1
MsgBox TotCpm(2, 3, cnt)
Next cnt
'all cost(2) outputs for Company A(1) ie. all rows
For cnt = LBound(TotCpm) To UBound(TotCpm)
MsgBox TotCpm(cnt, 1, 2)
Next cnt
End Sub

 

[jkguy]

So every iteration produces a table like the first table in the original post and you would want that 'flatten' out into one row in and added to an array?

Do the columns/iterations vary?

That's correct. Once assumptions are set, columns / rows remain the same for months in the model. Rows will change at some point, but I count the number of rows and store them in a NumProg (number of programs) variable. Rows will range from 50-100...they are investments the company is pursuing in a given year. # of total iterations is a model input and will vary from 100-10,000.

Once an iteration is complete, I only store the results in the array if it meets criteria. This code for the array falls under a "if these 3 columns are >= set criteria, then save this iteration" in which the array will populate a new row with the acceptable iteration.