Solver to run multiple columns separately

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

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
  •  

 

 
DMCA.com