+ Reply to Thread
Results 1 to 11 of 11

macro solver based on selected row

  1. #1
    Registered User
    Join Date
    03-07-2018
    Location
    winnipeg, manitoba
    MS-Off Ver
    OFfice 2013
    Posts
    4

    macro solver based on selected row

    I have a specific solver that I need run, and all the information is organized per row. However, my data set is large and I don't need solver run for all rows. What I would like is to input a value into a cell (BB4), then have the macro search Column E for matching value. Once the row with the matching value is found, run a solver based on information in that row. All rows are set up the same, so column references will be the same regardless of which row is chosen.

    Input number into cell $BB$4.
    Search column E for cell value matching $BB$4
    Set variables for solver based on row

    Please Login or Register  to view this content.
    I am not sure if it is possible to run solver with variables, rather than cell references.
    Thanks,
    Last edited by bluescreamsblack; 03-09-2018 at 01:59 PM.

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: macro solver based on selected row

    It sounds like you can use INDEX and Match to pull the data to selected cells and then use these cells in solver.
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: macro solver based on selected row

    Please Login or Register  to view this content.
    Please take a few minutes to read the forum rules, and then edit your post to add CODE tags.
    Last edited by shg; 03-08-2018 at 07:57 PM.
    Entia non sunt multiplicanda sine necessitate

  4. #4
    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: macro solver based on selected row

    Perhaps one could add the line:
    Please Login or Register  to view this content.
    before the line
    Please Login or Register  to view this content.
    in shg's code?

    Alf

  5. #5
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: macro solver based on selected row

    Indeed, thanks Alf.

  6. #6
    Registered User
    Join Date
    03-07-2018
    Location
    winnipeg, manitoba
    MS-Off Ver
    OFfice 2013
    Posts
    4

    Re: macro solver based on selected row

    Thanks for your replies. The code works great. I was able to modify it into a loop as I quickly realized that I had more than 1 instance of a repeated input I required solving for. Sorry about my original post, I updated with the CODE tags as requested. Thanks again.

  7. #7
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: macro solver based on selected row

    You're welcome.

  8. #8
    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: macro solver based on selected row

    Thanks for feedback and rep

    Alf

  9. #9
    Registered User
    Join Date
    03-07-2018
    Location
    winnipeg, manitoba
    MS-Off Ver
    OFfice 2013
    Posts
    4

    Re: macro solver based on selected row

    I actually had one more question for this. Not a big deal, more for aesthetics than function. Since Solver needs a starting guess value in order to work (in my case I can't leave the cells blank as it won't solve), is it possible to install a value into cell with this method.
    Basically use your find , then say
    Please Login or Register  to view this content.
    I am unclear how this function works exactly and can't seem to choose the cell for input.
    Thanks

  10. #10
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: macro solver based on selected row

    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    03-07-2018
    Location
    winnipeg, manitoba
    MS-Off Ver
    OFfice 2013
    Posts
    4

    Re: macro solver based on selected row

    Nevermind - I was over complicating things. I just used Range.Select to choose variable, then Active.Cell.value to set. I should probably think things through a bit more before asking.

    Your way is simpler and cleaner though.

    Thanks again.

+ 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] How to make solver solve for selected cells
    By fgnkrkrt in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-20-2014, 05:27 PM
  2. [SOLVED] VBA macro to use solver on a user selected column of data
    By jdmccoskey in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-24-2014, 09:07 AM
  3. Replies: 0
    Last Post: 07-20-2014, 12:45 PM
  4. 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
  5. [SOLVED] VBA: Solver - Sheet must be selected?
    By R22 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 01-12-2014, 02:48 PM
  6. Macro based on selected range
    By wsphthefirst in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-09-2013, 10:23 AM
  7. 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

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