+ Reply to Thread
Results 1 to 8 of 8

Solver in a Loop for multiple rows by changing multiple columns

  1. #1
    Registered User
    Join Date
    04-25-2018
    Location
    Ceará, Brazil
    MS-Off Ver
    MS Office 2013
    Posts
    4

    Solver in a Loop for multiple rows by changing multiple columns

    Hello guys.

    I was having trouble running the Solver in multiple rows. However, I saw in this forum several threats that helped me evolve a lot using VBA (I'm starting my studies now in this language and solved the problem with Solver in a Loop).

    I know it must be something simple that I'm missing, however, I cannot do that for two columns at a time (in this case, the P and the R columns).
    Here are the spreadsheet and the code.
    I would also like to add the R column.

    Best Regards,
    Gláuber.

    Please Login or Register  to view this content.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    03-13-2004
    Location
    Gothenburg/Stockholm, Sweden
    MS-Off Ver
    Excel 2003, Excel 2007 & reluctant Excel 2010 user
    Posts
    4,540

    Re: Solver in a Loop for multiple rows by changing multiple columns

    Try changing
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.
    Not that the line "Engine:=1, EngineDesc:="GRG Nomlinear" is wrong but it's sufficient to just specify "Engine:=1"

    Not sure I understands your logic behind the setting for the object function, i.e. change P and R value to set AI value = AI value?

    Alf

    Ps Your formula setting on uploaded file was set to "Manual" I would recommend you use "Automatic" when running solver just to be sure values are updated.
    Last edited by Alf; 04-29-2018 at 04:17 AM.

  3. #3
    Registered User
    Join Date
    04-25-2018
    Location
    Ceará, Brazil
    MS-Off Ver
    MS Office 2013
    Posts
    4

    Re: Solver in a Loop for multiple rows by changing multiple columns

    Dear Alf,

    it works perfectly! Thank you very much.

    Not sure I understands your logic behind the setting for the object function, i.e. change P and R value to set AI value = AI value?
    What I would like to optimize was the AI column by changing the P and the R. I must have spelled the code wrong or did not explain well...

    As for
    Your formula setting on uploaded file was set to "Manual" I would recommend you use "Automatic" when running solver just to be sure values are updated.
    Yeah, I had noticed before and made the change. Thanks for the tip.

    Best regards,
    Gláuber.

  4. #4
    Forum Expert
    Join Date
    03-13-2004
    Location
    Gothenburg/Stockholm, Sweden
    MS-Off Ver
    Excel 2003, Excel 2007 & reluctant Excel 2010 user
    Posts
    4,540

    Re: Solver in a Loop for multiple rows by changing multiple columns

    You are welcome and thanks for feedback

    What I would like to optimize was the AI column by changing the P and the R.
    Well you did that by setting the command "MaxMinVal:=2," i.e. this solver "translates" as sett (AI + i) values to min by changing cells (P + i) and cells (R + i)
    and then you specify that cells (AI + i) should be equal to cells (AI + i) using the "SolverAdd CellRef:=Cells(i, "AI").Address, Relation:=2, FormulaText:=Cells(i, "AI").Address"

    This is the part I don't understand.

    The formula in the cells (AI + i) say in cell AI24

    Please Login or Register  to view this content.
    refers to cells that have no value in the uploaded file so changing cells (P + i) and cells (R + i) will have no effect when I run the macro. That I have no problem with and it could be that when all these cells have values then then the setting cells (AI + i) = cells (AI + i) make sense.

    But if you are happy with the macro modification then I'm happy too.

    Alf

    Ps If this solves your problem don't forget to mark your thread "Solved" as per Forum rules
    To mark your thread solved do the following:
    -Go to the top of the first post
    -Select Thread Tools
    -Select Mark thread as Solved
    Last edited by Alf; 04-29-2018 at 08:11 AM.

  5. #5
    Forum Expert
    Join Date
    03-13-2004
    Location
    Gothenburg/Stockholm, Sweden
    MS-Off Ver
    Excel 2003, Excel 2007 & reluctant Excel 2010 user
    Posts
    4,540

    Re: Solver in a Loop for multiple rows by changing multiple columns

    Hi sandy666

    Thanks for rep, tried to post this on you tab for "Visitor Messages" but as you have removed this I'll post it here and hopefully you will see it.

    Alf

  6. #6
    Registered User
    Join Date
    04-25-2018
    Location
    Ceará, Brazil
    MS-Off Ver
    MS Office 2013
    Posts
    4

    Re: Solver in a Loop for multiple rows by changing multiple columns

    Hello, Alf.

    I looked at the spreadsheet and saw that the cells you referred to actually have values.
    Anyway, I am uploading another file with the cells now visible, only because I hope it is possible to understand (because I will mark as SOLVED since it helped to understand and solve the problem).

    Gláuber.
    Attached Files Attached Files

  7. #7
    Forum Expert
    Join Date
    03-13-2004
    Location
    Gothenburg/Stockholm, Sweden
    MS-Off Ver
    Excel 2003, Excel 2007 & reluctant Excel 2010 user
    Posts
    4,540

    Re: Solver in a Loop for multiple rows by changing multiple columns

    I looked at the spreadsheet and saw that the cells you referred to actually have values.
    So right you are! I did not look properly at the column letters so I did not realized you had hidden columns in the first uploaded file. My bad

    Alf

  8. #8
    Registered User
    Join Date
    04-25-2018
    Location
    Ceará, Brazil
    MS-Off Ver
    MS Office 2013
    Posts
    4

    Re: Solver in a Loop for multiple rows by changing multiple columns

    No problem, Alf.
    I was really worried that I was mistaken in something on the spreadsheet. But everything went well.

    Thanks again for the attention.

    Gláuber.

+ 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 - Multiple By Changing Variables
    By PingPongYeah in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-21-2018, 02:20 PM
  2. Search Multiple Columns and Rows - Show results in Multiple columns and rows
    By heykeighley in forum Excel Programming / VBA / Macros
    Replies: 20
    Last Post: 08-12-2015, 10:16 AM
  3. Filter - Multiple Columns - Loop through Visible Rows issue
    By Vinod Krishna.C in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-11-2014, 05:30 PM
  4. [SOLVED] Using "SOLVER" to match two columns, but how to copy it for multiple rows ??? (URGENT)
    By hshahad in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-16-2013, 11:31 AM
  5. Replies: 3
    Last Post: 03-27-2013, 03:58 PM
  6. Run Solver Multiple Times (while changing constraints and goal for each iteration)
    By pmw1218 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-03-2013, 04:17 PM
  7. Changing Multiple Columns into Multiple Rows
    By Tester36 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-07-2011, 02:11 PM

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