Conditional Loop - Copy Paste to new Workbook

[FrankieG]

Dear All,

First of all thanks for your help in advance.

I have a complicated excel model to make projections for the future. These projections are contingent on many inputs, and as such I am using a single input cell (or actually two) to pull in all the related inputs at the same time per person.

In the example enclosed these inputs are provided in cells C25 and C26. For simplicity I have named them Person 1, Person 2 (and the n=500) whilst there are also different scenarios per person possible (scenario 1-10).

Depending on the combination in cell C25 and C26 a certain table with results is produced, do note that this table is pretty extensive in itself with roughly 100 rows x 100 columns.

It is not required to run all persons and all scenarios all the time, and hence there is an YES/NO switch per person and a YES/NO switch per scenario. Only the activated combinations matter.

I am trying to find a way to:
1. Run all relevant selected combinations of cells C25 and C26 (in this example: Person 1 & Scenario 1, Person 1 & Scenario 3, Person 5 & Scenario 1, Person 5 & Scenario 3 etc). This is a lot of combinations, whilst excluding the combinations that are not selected is equally important to save computing power.
2. Create a new workbook
3. Copy paste all results next to each other to this new workbook. Note that all relevant results per person/per scenario are captured in a single table.

So in short: want to change nothing to the model as-is, with the exception to run a loop for all possible scenarios, and create a new workbook to store these results in one go.

Regards
FG

 

[offthelip]

You need to change the code like this:

headers = Range("NamedRangeofHeadersinExcel")
For i = 1 To UBound(headers,2)
 outarr(1, i) = headers(1, i)
Next i
Range(Cells(1, 1), Cells(indi + 7, 30)) = outarr

Note the changes to the indexing, this is because Headers is now a two dimensional array starting at 1 instead of a one dimensional array starting a zero

 

[FrankieG]

Thank you, as always. This set-up still seems to result in an error if i try (all though I am probably the one making the mistake). Below the debugger message.

"Results.and.Export.Header" is the Name Range of the hardcoded header values in excel (effectively a single row with many columns, Year / Person ID / Age etc etc).

[As you can see the real row count is 386, real column count is 130]

 

[offthelip]

you haven't said what the error is, and there is nothing that I can see in your code that shoud cause an error. Are you sure that the name you have in the vba code is identical to the name you have defined on the worksheet. Also I note you have change indi + 7 in my code to indi + 386 in yours. You don't need to do this , the reason there is a plus 7 in there is because you have asekd for 7 rows of results for each set of results., the Indi value determines the 386 bit is that is what it ends up at.

 

[FrankieG]

Error code is:

Run-time error '1004'. Method 'Range of object '_Global' failed.

Probably not relevant, but the headers (Range name: "Results.and.Export.Header") are in a the same worksheet as the results (Range name: "Results.and.Export.Total".

I am sure the referred name range is correct.

 

[FrankieG]

Update: After reading up on potential solutions yesterday / this morning, can it be the case that our VBA code for headers tries to reference the Range Name in the newly created sheet (= non-existent), as opposed to the underlying calculation file?

 

[FrankieG]

I think I got it fixed now - I placed the headers before the Workbook.Add. This way the Range Name of headers refers to the calculation workbook, as opposed to the newly created file. May I ask you to sense-check this set-up? I will be using this formula for a very long time to come so trying to avoid any errors or inefficiencies Thanks for all the help so far.

Sub ExportBI()
Dim outarr
cols = 130
' Assume 386 rows for each result and columns 1 to 130 ( A to AD)

parr = Range("Persons")
sarr = Range("Scenario")
ReDim outarr(1 To (UBound(parr, 1) * UBound(sarr, 1) * 386), 1 To cols) As Variant
indi = 0
For i = 1 To UBound(parr, 1) ' Persons loop
If parr(i, 2) = "Yes" Then
For j = 1 To UBound(sarr, 1) ' scenario loop
If sarr(j, 2) = "Yes" Then
Range("Persons") = parr(i, 1)
Range("Scenario") = sarr(j, 1)
temparr = Range("Results.and.Export.Total") ' pick up results Note yo didn't say where they were!!
For k = 1 To 386 ' copy results to output buffer
For kk = 1 To cols
outarr(indi + k, kk) = temparr(k, kk)
Next kk
Next k
indi = indi + 386
End If
Next j
End If
Next i

headers = Range("Results.and.Export.Header")
For i = 1 To UBound(headers, 2)
outarr(1, i) = headers(1, i)
Next i

Workbooks.Add

ActiveWorkbook.SaveAs
Sheets("Sheet1").Name = "Export"

Range(Cells(1, 1), Cells(indi + 386, 130)) = outarr

End Sub

 

[offthelip]

Your fix is absolutely correct, I should have thought of that myself, sorry. Your change to add 386 rows per results is also looks correct. I would have thought you might want to put the save of the new workbook after you have written the results tothe worksheet, i.e after :

Range(Cells(1, 1), Cells(indi + 386, 130)) = outarr

otherwise you just save a blank workbook

 

[FrankieG]

No problem, thanks for all the help - really much appreciated.