1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

  3. 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?

Discussion in 'VBA Macros' started by Gregg Wolin, Nov 29, 2018.

  1. Gregg Wolin

    Gregg Wolin Member

    Messages:
    39
    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.

    Attached Files:

  2. Nebu

    Nebu Excel Ninja

    Messages:
    2,118
    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 (vb):
    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

    Attached Files:

  3. Gregg Wolin

    Gregg Wolin Member

    Messages:
    39
    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.
  4. Nebu

    Nebu Excel Ninja

    Messages:
    2,118
    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

    Attached Files:

  5. Gregg Wolin

    Gregg Wolin Member

    Messages:
    39
    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?

    Attached Files:

  6. Nebu

    Nebu Excel Ninja

    Messages:
    2,118
    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
  7. Gregg Wolin

    Gregg Wolin Member

    Messages:
    39
    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.

    Attached Files:

  8. Nebu

    Nebu Excel Ninja

    Messages:
    2,118
    Hi:

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

    Thanks

    Attached Files:

  9. Gregg Wolin

    Gregg Wolin Member

    Messages:
    39
    Where was this 1%?
  10. Nebu

    Nebu Excel Ninja

    Messages:
    2,118
    Sorry Typo, I mean to say you had two "Type 1" percentages in your file.
  11. Gregg Wolin

    Gregg Wolin Member

    Messages:
    39
    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.
  12. Nebu

    Nebu Excel Ninja

    Messages:
    2,118
    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

    Attached Files:

  13. Gregg Wolin

    Gregg Wolin Member

    Messages:
    39
    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.

Share This Page