Results 1 to 5 of 5

Thread: Do Until Loop with Many If Statements

  1. #1

    Red face Do Until Loop with Many If Statements



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Production Exercise.xlsx

    Hello,

    I am very new to VBA and could use a little help with creating something to replace approximately 7 "if" statements that got out of control.

    I have attached a spreadsheet that outlines the problem I am having.

    Essentially, I am trying to forecast the production of widgets. I have only purchased 1,500 units of resources to make those widgets and once I run out I quit the business. The production inputs are the following:
    • Peak year - I will be increasing my levels of production until then, this needs to be able to change.
    • Growth rate - the rate each year I will be increasing the production until the peak year, this needs to be able to change.
    • Decline rate - after the peak year, my production will be declining, this needs to be able to change.
    In 2000, I produced 200 widgets and that's what I'm basing the future production on (i.e. in 2001, it grows to 200*(1+growth rate)).

    The problem is, I have three plants, each of which cannot produce more than a certain amount each year. So when I make the simple production profile that goes "grow at x% until peak year, then decline at y% until my resources run out" is complicated by the fact that there is a ceiling to annual production. When the production hits that ceiling, it doesn't waste the resources and instead saves them for next year, thus the production profile is somewhat extended by those excess resources.

    Basically, I need it to say "grow at x% until peak year as long as it's under annual capacity, then decline at y% until my resources run out and it's under annual capacity".

    I would in utmost sincerity appreciate any help and leads on this.

    Thank you a bunch!

    Anastasia
    Last edited by LadyTerp; 2011-11-24 at 06:49 AM.

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,208
    Articles
    57
    Blog Entries
    14
    Hi there,

    Sorry, I was out sick yesterday. If someone else doesn't get to this first, I'll try to have a look at it tonight.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,208
    Articles
    57
    Blog Entries
    14
    Hi there,

    I've worked up two tables in here, based on what I think you're after. (No need for VBA.)

    This assumes consistent growth of 5% per B4, and a linear decline of 10% per B5 once capacity is reached.

    Based on my calculations the plants reach capacity in the following years:
    • Plant 1: 2005
    • Plant 2: 2004
    • Plant 3: 2004
    This means the overall capacity is reached in 2005. (I've added formula to calculate that automatically.

    I then prepared a second table that shows the growth AND decline. Keep in mind that, because you're declining at 10% of the prior year annually, it will never reach 0.00 widgets. you may want to figure out a minimum production capacity so that it can be factored in as well.

    Hope this is along the right lines of what you were looking for.

    (Oh, and I hid some of your original work using outlining. Just click the + next to row 24 to show it again.)
    Attached Files Attached Files
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  4. #4

    Thank you so much!

    I am glad to delay studying VBA

  5. #5
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,208
    Articles
    57
    Blog Entries
    14
    Don't be too glad. It's SUPER useful... just not always the best way.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Posting Permissions

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