Pretty basic loop?

[Gregg Wolin]

I have a series of six proforma, 36-month cash flow streams that represent a variety of different sized transactions that i might execute (i.e transaction Types 1 - 6).

I created an annual "randomizer" table that generates an allocation of project types across each of 10 years which is triggered by a macro attached to the "Calculate Portfolio" button [at the moment, the macro only creates the randomizing allocation table].

Next I created a table that, depending on the year input [AW9], creates a weighted average transaction cash flow for the year indicated [cv_CF.AnnualCalc]. I started a sub to copy the and paste the value from this range into the Year 1 column of the matrix [cv_CF.Year1] and realized that this seems like a good place for a loop but my VBA skills are woefully inadequate.

Ideally, I would like to macro to loop through each of the 10 years, then paste the resulting values into the corresponding column in the Project Type Distribution Table.

 

[Nebu]

Hi:

I do not think you need a loop for this, the macro will trigger when ever you change the year in AW9 and copy the values to respective columns.

Private Sub Worksheet_Change(ByVal Target As Range)
With Me
If Not Intersect(Target, .Range("AW9")) Is Nothing Then
.Range("AW11:AW47").Copy
i& = WorksheetFunction.Match(.[AW9], .Range("BB10:BK10"), 0) + 53
.Cells(11, i).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Application.CutCopyMode = False
End If
End With
End Sub

Thanks

 

[Gregg Wolin]

I apologize if I didn’t fully communicate my intent but the idea was to not have to “enter” anything in AW9. I’d like to press the Calculate Portfolio button and have it run thru and get the results for each year (1 thru 10) and paste the resulting numbers to the matrix (I.e the macro “enters” 1 in cell AW9, then pastes those results to the Year 1 column, then “enters” 2 in AW9 and pastes those results to the Year 2 colum, etc.

 

[Nebu]

Hi:

You do need need additional macro for this just use the following formula.

=SUMPRODUCT(($AD45:$AH45)*(INDIRECT("$AD$"&BB$10+10&":$AH$"&BB$10+10)))

Thanks

 

[Gregg Wolin]

Hi:

You do need need additional macro for this just use the following formula.

=SUMPRODUCT(($AD45:$AH45)*(INDIRECT("$AD$"&BB$10+10&":$AH$"&BB$10+10)))

Thanks

Again, apologies for my inexperience (and poor communication skills) but I don't follow how these things work. When I make your macro live, it copies the values from column AW based on the value that is manually input into AW9, but then it overwrites the sumproduct formula you added to the destination matrix. That said,I was able to create to create a loop to achieve the result that I was seeking, but it sure seems to run slow. Take a look and see what you think?

 

[Nebu]

Hi:

In case you are using Sumproduct formula yo do not need a macro(ignore my macro). I guess you need to go with formula which will be much faster considering you have only limited nof rows to do the calculation.

Thanks

 

[Gregg Wolin]

Hi:

In case you are using Sumproduct formula yo do not need a macro(ignore my macro). I guess you need to go with formula which will be much faster considering you have only limited nof rows to do the calculation.

Thanks

I moved some things around and now your sumproduct formula stopped working. I updated all the cell references but I don't understand how the formula is working. Yout formula is MUCH faster than the macro I assembled so if you could show me how I broke it, I'd really appreciate it.

 

[Nebu]

Hi:

I have fixed the formula, since you had to type 1 % i had to create a helper row in BF.

Thanks

 

[Gregg Wolin]

Hi:

I have fixed the formula, since you had to type 1 % i had to create a helper row in BF.

Thanks

Where was this 1%?

 

[Nebu]

Sorry Typo, I mean to say you had two "Type 1" percentages in your file.

 

[Gregg Wolin]

Sorry Typo, I mean to say you had two "Type 1" percentages in your file.

Actually, there aren't (or shouldn't be). It looks like you added a "Type 7" which shouldn't be there. Why the sumproduct formula (BQ7) only reference 5 of the 6 columns in the cash flow table? It was working before, I'd just like to understand the mechanics so i can do it myself.

 

[Nebu]

Hi:

I have highlighted in yellow the helper row I have created and have highlighted in red rectangle where you have Type 1 repeating. The formula is straight forward just use evaluate the formula to understand what I have done.

Thanks

 

[Gregg Wolin]

Thanks Nebu. I have it working now. This exercise made me realize that I need to look at the analysis another way (to confirm that the results from this model are close enough). I will create another post as it's probably what I should have asked at the outset.