Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: Solver to run multiple columns separately

  1. #1
    New Member
    Join Date
    Dec 2017
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Solver to run multiple columns separately

    Hi, I have made a spreadsheet where all columns are similar in terms of formulas etc. Only the input values for each column are different. Each column has 4 variable cells that the solver needs to find a solution for. I have tried to make a solver setup that finds solution for the 4 variable cells of all of the columns but it seems to work only sometimes and seems to be computationally taxing.

    As all of the columns are independent from each other, I was looking to find a solution to run the solver separately, one column at a time, but I have not been able to find any help on how this could be done. I have seen this been done with goal seek, but I would prefer solver.

    I am also quite new to VBA.

    Any idea how this could be done?

  2. #2
    New Member
    Join Date
    Dec 2017
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Solver to run multiple columns separately

    To clarify the issue more:
    I have created a solver that works well for the first column. Then I recorded a macro from it and according to some instructions that I found I managed to make it work more or less for the rest of the columns. I want the target value to become zero. But for some reason it does not become exactly zero. How can I make the target value become more accurate (closer to zero)?

    Here is the code I used:

    Sub SolvLoop()


    Dim i As Long


    For i = 52 To Range("D" & Rows.Count).End(xlUp).Row


    SolverReset
    SolverOk SetCell:="D" & i, MaxMinVal:=3, ValueOf:=0, ByChange:="$D$41:$D$44", _
    Engine:=1, EngineDesc:="GRG Nonlinear"
    SolverAdd CellRef:="$D41:D44$", Relation:=3, FormulaText:="0"
    SolverAdd CellRef:="$D48:D51$", Relation:=2, FormulaText:="0"
    SolverSolve Userfinish:=True
    Next
    End Sub

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •