+ Reply to Thread
Results 1 to 7 of 7

Complete Solver by row in Macro

  1. #1
    Registered User
    Join Date
    04-09-2019
    Location
    Alaska
    MS-Off Ver
    Microsoft Office 365
    Posts
    3

    Complete Solver by row in Macro

    I need to repeat the solver code below over 100+ rows but for the life of my cannot find a solution to cycle through the formulas in the FormulaText fields. I've tried a For loop using Offset but it doesn't like the formula references (cell1 = 1-cell2-cell3). Essentially I need H2, J2 and L2 to be 3 proportions that add to 1. Any hints on what I could use to repeat the code below from row 2 through row 118?

    Sub dietmix()
    '
    ' dietmix Macro
    '

    '

    SolverAdd CellRef:="$AD$2", Relation:=1, FormulaText:="3"
    SolverAdd CellRef:="$H$2", Relation:=2, FormulaText:="1-$J$2-$L$2"
    SolverAdd CellRef:="$J$2", Relation:=2, FormulaText:="1-$H$2-$L$2"
    SolverAdd CellRef:="$L$2", Relation:=2, FormulaText:="1-$H$2-$J$2"
    SolverOk SetCell:="$AC$2", MaxMinVal:=1, ValueOf:=0, ByChange:="$H$2,$J$2,$L$2" _
    , Engine:=2, EngineDesc:="Simplex LP"
    SolverSolve

    End Sub

  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: Complete Solver by row in Macro

    Could you upload a file with your solver model? Easier to work with and test.

    To upload file click on "Go Advanced" button and on the new "page" that opens scroll down to blue text "Manage Attachment". Click on that line and select file to upload.

    Alf

  3. #3
    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: Complete Solver by row in Macro

    Perhaps something like this? But untested so it may not work. In this case an uploaded file is needed to fix code problem. This code is based on rows going from 2 to 150 modify as needed.

    I assume you have set a reference to solver in VBA.

    Please Login or Register  to view this content.
    When posting you should wrap your code. Click on # icon and write your code in between "["Code"]" "[/"Code"]" symbols you see. (Forum rule!!!!)

    Alf
    Last edited by Alf; 04-10-2019 at 08:49 AM.

  4. #4
    Registered User
    Join Date
    04-09-2019
    Location
    Alaska
    MS-Off Ver
    Microsoft Office 365
    Posts
    3

    Re: Complete Solver by row in Macro

    Thank you Alf. Did not have luck with the provided code. Attached is a slightly modified datafile for testing.
    Attached Files Attached Files

  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: Complete Solver by row in Macro

    As you file is an .xls file I assume that you are running Excel 2003 and the command "Engine:=2, EngineDesc:="Simplex LP" was first used in Excel 2010, excel 2003 will not understand this command.

    After opening the file in excel 2003 and setting a VBA reference to "Solver.xla" and adding the command to run solver in Simplex mode it looks like your model work and the settings you wanted can be seen in the solver window for the last row i.e row 118. The macro "SolvLoopMod" will run on Excel 2003 as well as Excel 2010 but I've not made any comparing between the two result to see how well they match.

    I will also recommend you to change the calculating settings to "Automatic" in stead of the "Manual" setting on the uploaded file. I've changed that in this uploaded file.

    As help given will depend on your Excel version I would suggest you update your forum user profile to show the excel version you use.


    Alf
    Attached Files Attached Files
    Last edited by Alf; 04-11-2019 at 01:01 AM.

  6. #6
    Registered User
    Join Date
    04-09-2019
    Location
    Alaska
    MS-Off Ver
    Microsoft Office 365
    Posts
    3

    Re: Complete Solver by row in Macro

    Worked like a charm. Thank you for your help and patience.

  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: Complete Solver by row in Macro

    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. [SOLVED] Step within Macro is taking forever to complete and locks up excel until complete
    By cubangt in forum Excel Programming / VBA / Macros
    Replies: 25
    Last Post: 05-16-2017, 11:58 AM
  2. [SOLVED] VBA Solver Loop while waiting to complete
    By richardking in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-16-2017, 10:52 AM
  3. Excel Solver on multiple cells (complete row) // Option payoff profile
    By jules_5 in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 11-09-2016, 07:47 AM
  4. Replies: 0
    Last Post: 07-20-2014, 12:45 PM
  5. macro not keeping solver solutions when solver is successful
    By jimmypants in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-10-2014, 01:45 AM
  6. solver macro + simulation code + not updating solver values
    By sabinemaria in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-24-2012, 11:37 AM
  7. using solver to complete a table
    By justhrowit in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-09-2006, 03:20 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