• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Pretty basic loop?

Gregg Wolin

Member
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.
 

Attachments

Nebu

Excel Ninja
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.

Code:
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
 

Attachments

Gregg Wolin

Member
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

Excel Ninja
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
 

Attachments

Gregg Wolin

Member
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?
 

Attachments

Nebu

Excel Ninja
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 no:eek:f rows to do the calculation.

Thanks
 

Gregg Wolin

Member
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 no:eek:f 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.
 

Attachments

Gregg Wolin

Member
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

Excel Ninja
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
 

Attachments

Gregg Wolin

Member
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.
 
Top