+ Reply to Thread
Results 1 to 7 of 7

Looping macro to run various scenarios through a model returning each answer automatically

  1. 01-14-2016, 08:49 PM #1
    millwos
    millwos is offline
    Registered User
    Join Date
    01-14-2016
    Location
    Launceston, Australia
    MS-Off Ver
    2007
    Posts
    4

    Looping macro to run various scenarios through a model returning each answer automatically

    Hi, hope is well.

    I have a financial model that works by the user entering 7 input assumptions that returns an answer.

    I want to be able to run (say 50+) different rows of scenarios using a looping macro to input each new row of assumptions and return the answer for each row using the financial model.

    Please find attached a basic example of my model. This example model simply sums the inputs together to calculate the answer, but I'm after a macro to do this task, as my real model isn't as simple as just summing the assumptions together, but the concept will be the same.

    Also I can't use the scenario manager as this requires each scenario to be input individually.

    Any help much appreciated.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 01-14-2016, 09:11 PM #2
    mehmetcik
    mehmetcik is offline
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Looping macro to run various scenarios through a model returning each answer automatic

    What you ask for is quite simple.

    I have written this so it can be run from any sheet.

    Please Login or Register  to view this content.

    Your Modelling will get interesting when you decide to have multiple sheets and enter your data in random cells.
    When you reach that point come back to me.

    I will have worked out something for you by then.
    Attached Files Attached Files
    Last edited by mehmetcik; 01-14-2016 at 09:13 PM.
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.
    Reply With QuoteRegister To Reply
  3. 01-14-2016, 10:06 PM #3
    mehmetcik
    mehmetcik is offline
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Looping macro to run various scenarios through a model returning each answer automatic

    This will create a Database for you.

    Once you have pondered on it we can work on the next stage.



    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  4. 01-14-2016, 10:26 PM #4
    millwos
    millwos is offline
    Registered User
    Join Date
    01-14-2016
    Location
    Launceston, Australia
    MS-Off Ver
    2007
    Posts
    4

    Re: Looping macro to run various scenarios through a model returning each answer automatic

    Hi Mehmetcik,

    Thank you so much for your reply, I am very grateful!

    I will work through the database coding and add it into my working model.

    This is the first time I have worked with VB and have used this forum, amazingly helpful!
    Reply With QuoteRegister To Reply
  5. 01-14-2016, 11:50 PM #5
    millwos
    millwos is offline
    Registered User
    Join Date
    01-14-2016
    Location
    Launceston, Australia
    MS-Off Ver
    2007
    Posts
    4

    Post Re: Looping macro to run various scenarios through a model returning each answer automatic

    Attachment 440553Hi Mehmetcik,

    Thanks again for your help, however I'm having trouble implementing the code into the spreadsheet.

    My original spreadsheet example probably wasn't the best to use, because it is a slightly different to my working model.

    I have attached an example that is closer to my real life model.

    Can I please request your assistance to return the profit figure from the inputs page, by changing the blue inputs for each scenario row?

    Any help much appreciated, thankyou.
    Attached Files Attached Files
    Last edited by millwos; 01-15-2016 at 02:02 AM.
    Reply With QuoteRegister To Reply
  6. 01-15-2016, 09:17 AM #6
    mehmetcik
    mehmetcik is offline
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Looping macro to run various scenarios through a model returning each answer automatic

    I had to rearrange your input sheet slightly to make the coding logical.



    Please Login or Register  to view this content.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  7. 01-15-2016, 08:22 PM #7
    millwos
    millwos is offline
    Registered User
    Join Date
    01-14-2016
    Location
    Launceston, Australia
    MS-Off Ver
    2007
    Posts
    4

    Re: Looping macro to run various scenarios through a model returning each answer automatic

    Perfect! Thanks Mehmetcik legend!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. returning right answer from 3 cells
    By sdtsdt in forum Excel General
    Replies: 1
    Last Post: 09-13-2015, 07:53 AM
  2. Nested IF Returning False - Need to Return True for Three Scenarios
    By rhelmer in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-18-2015, 05:50 PM
  3. [SOLVED] cells automatically colour differently with 'what if' scenarios
    By Kenny @ TLGC in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 06-12-2014, 05:49 PM
  4. EVM Model that Updates Automatically
    By jylekarrett in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-07-2013, 10:10 AM
  5. Formula sometimes returning correct answer and sometimes returning 0
    By redimp in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-09-2010, 06:28 AM
  6. Solver not returning answer
    By jealkon in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-01-2010, 05:15 PM
  7. answer: Returning a 0 Value
    By PeterAtherton in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 09-06-2005, 04:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1