+ Reply to Thread
Results 1 to 3 of 3

Trying to run Excel's Solver add-in N times and record each run's results

  1. #1
    Registered User
    Join Date
    12-01-2022
    Location
    New York
    MS-Off Ver
    2016
    Posts
    2

    Trying to run Excel's Solver add-in N times and record each run's results

    Hi,
    I have two tabs on the same worksheet, called 'Model' and 'Results'.
    The Model tab (Sheet1) optimizes (minimizes) the cost on a project given some assumptions and constraints.
    This is done using Excel's Solver add-in with the Evolutionary algorithm option
    As Solver seems to output a different optimized cost amount for each round that Solver runs, I would like to be able to automate the running of Solver multiple times and record each run's results in the Results tab.
    Attached pic is what I've come up with in terms of code.
    First error is the line that begins with "ResultsArray=..."; the error window detects a "Subscription out of range" error.
    I'd appreciate it if anybody could help me out with the rest of the code as well (see attached text file). Thanks
    Attached Files Attached Files

  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: Trying to run Excel's Solver add-in N times and record each run's results

    Can't help you with the "ResultsArray" problem but the solver lop macro seems strange to me i.e. you are repeating the same line thee times.

    As far as I can see this code snippet should be sufficient for the solver part.

    Please Login or Register  to view this content.
    When looping solver it is often a good idea to reset solver after each loop. Specifying solver engine one only need "Engine:=" + a number. 1 = GRG NonLinear, 2 = Simplex LP and 3 = Evolutionary.

    Alf

  3. #3
    Registered User
    Join Date
    12-01-2022
    Location
    New York
    MS-Off Ver
    2016
    Posts
    2

    Re: Trying to run Excel's Solver add-in N times and record each run's results

    Hi Alf, thanks for the reply.
    I've attached (image 1) of the code window after making the changes you suggested.
    I wonder if the "Subscription out of range" problem is due to the fact that I'm assigning to ResultsArray a range from worksheet 2 (c10:j20) but the macro was created and run while worksheet 1 was open. I would think that the code being saved as "Module1" in the Module folder and not in the "Microsoft Excel Objects" folder (image 2) would eliminate any cross-worksheet problems
    Attached Images Attached Images
    Last edited by tsp216; 12-02-2022 at 06:24 AM.

+ 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. EXCEL SOLVER-how to get all combination results for a desired sum
    By jayarajmarydasan in forum F1 Get the most out of Excel Formulas & Functions
    Replies: 1
    Last Post: 05-21-2019, 11:38 AM
  2. How do I record the results of a run in Excel automatically?
    By Sourpowerpete in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-19-2016, 06:26 PM
  3. Excel macro recording DOES NOT record actions but results! Useless.
    By dejudicibus in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-26-2015, 05:11 AM
  4. Replies: 1
    Last Post: 03-10-2006, 08:00 AM
  5. How to record macro for Solver in Excel?
    By Samirkc in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 11-22-2005, 01:10 AM
  6. [SOLVED] Using Excel Solver multiple times with macros
    By Barry T in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-09-2005, 04:06 PM
  7. MS Access record delete from excel database query results
    By sdnicsm in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 01-23-2005, 03:55 AM

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