+ Reply to Thread
Results 1 to 7 of 7

Is it possible to have two separate Solver functions within a Loop?

  1. #1
    Registered User
    Join Date
    08-02-2017
    Location
    Kuala Lumpur, Malaysia
    MS-Off Ver
    2010
    Posts
    20

    Is it possible to have two separate Solver functions within a Loop?

    I want to loop through a series of calculations, and I need Solver to produce the result from the first part of the loop as input to the second part. My code is shown below, but the second part is not enacted. Additionally the first part only works for the first time through the loop. Thanks in advance for your time and help

    Please Login or Register  to view this content.

  2. #2
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: Is it possible to have two separate Solver functions within a Loop?

    Additionally the first part only works for the first time through the loop.
    My first impression is that setting calculation to manual at the start of a solver run is a bad idea. Have you tried removing this line and let solver run in an environment where calculation is automatic?

    Setting "Application.ScreenUpdating = False" does not seem to have much effect in a solver run in regard to speed on solving problems.

    Can you upload a worksheet with your model?

    Alf

  3. #3
    Registered User
    Join Date
    08-02-2017
    Location
    Kuala Lumpur, Malaysia
    MS-Off Ver
    2010
    Posts
    20

    Re: Is it possible to have two separate Solver functions within a Loop?

    Hi, removing the 'Set Calculations to Manual' doesn't seem to have made any difference, though the loop now leaves calculations as manual afterwards. For the range of changing variables would it be better to write it as some form of $BX$:$BW & i? Or does that not work for a loop (since this is how it is done on the regular solver)

  4. #4
    Registered User
    Join Date
    08-02-2017
    Location
    Kuala Lumpur, Malaysia
    MS-Off Ver
    2010
    Posts
    20

    Re: Is it possible to have two separate Solver functions within a Loop?

    Okay, think I might have figured it out. I don't think it could handle, for whatever reason, the two variables in the second part of the loop being in Columns B and EN. When I cut B so it was next to EN the loop works fine

  5. #5
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: Is it possible to have two separate Solver functions within a Loop?

    Could you also try to dim both j and k as integers.

    To answer your original question. Yes of course it is possible to have two solver functions in a loop or more if you should need it.

    And you also did it right i.e. starting the second loop with the SolverReset command as well as having it at the start of the first run since you.

    For the range of changing variables would it be better to write it as some form of $BX$:$BW & i?
    Don't think that matter as long as you do it right but for checking and trouble shooting it may be easier if you used the same king of description for all the cells involved in the solver macro.

    Have you tried to step through the macro using F8 and switching between worksheet and macro sheet to check if the logic in this macro is right?

    Alf

  6. #6
    Registered User
    Join Date
    08-02-2017
    Location
    Kuala Lumpur, Malaysia
    MS-Off Ver
    2010
    Posts
    20

    Re: Is it possible to have two separate Solver functions within a Loop?

    I have dimmed all 'j' and 'k' equivalents on other macros as integers in case this was causing any issues, but this particular problem seems to have been solved by putting the two changing variables together, which you dont need to be the case when just using the normal solver add-in. Thanks for letting me know that my double SOLVER loop was done correctly

  7. #7
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: Is it possible to have two separate Solver functions within a Loop?

    You are welcome and thanks for feedback

    Alf

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 7
    Last Post: 08-18-2017, 11:53 AM
  2. Replies: 9
    Last Post: 06-13-2017, 01:41 PM
  3. [SOLVED] How to loop the solver in the VBA?
    By nchinas in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 03-01-2014, 06:34 PM
  4. [SOLVED] Solver For Loop
    By Hayashi151 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-16-2014, 05:32 PM
  5. VBA Loop using solver.
    By ec41tp in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-17-2011, 05:10 AM
  6. Solver + Loop
    By k21k in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-22-2008, 10:00 AM
  7. Solver, VBA and functions
    By olo2000 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-20-2007, 03:51 PM

Tags for this Thread

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1