VBA macro to for Solver on a user selected column of data

[jdmccoskey]

I currently have a script set up to run the solver to minimize an output value from 2 inputs. The sheet is formated with the inputs in columns A and B. Each row has the data for 1 end value that I am trying to optimize. Currently I can highlight the entire column of values I want to optimize and run the macro to optimize each individual value with respect to that row's inputs. I recently realized that there is a complicated case that I need to set as a constraint so I have the calculations for that in a different sheet.

My current issue is trying to figure out how to have the script increment the range on a different sheet and pass that to solver. In my script the part I need to fix is currently commented out. I need the

SolverAdd CellRef:="$D$1:$K$1", Relation:=3, FormulaText:="0"

to target the other sheet in my document while not breaking the rest of the script and the next itteratíon stillr eading values from the original sheet.

Solver could also be the issue because it may not be letting me use constraints from other sheets but I dont know.

The rest of the code is as follows (sorry for any bad programing technique, this is pretty much my first usefull VBA script):

Sub SolverAutomationForReal()
'
' SolverAutomation Macro
'
'
'make sure to go to Tools>References and then click the box next to solver
Set Rng = Range(Selection.Address)
Dim cell As Object
Dim count As Integer
Dim i As Integer
Dim rw As Integer
rw = Range(Selection.Address).Row
count = 0
    For Each cell In Selection
        count = count + 1
    Next cell
    
For i = 1 To count
    SolverReset
    SolverOk SetCell:=Range(Selection.Address)(i), MaxMinVal:=2, ValueOf:=0, ByChange:=Range(Cells(rw - 1 + i, 1), Cells(rw - 1 + i, 2)), _
        Engine:=1, EngineDesc:="GRG Nonlinear"
    SolverAdd CellRef:=Cells(rw - 1 + i, 1), Relation:=1, FormulaText:="25"
    SolverAdd CellRef:=Cells(rw - 1 + i, 1), Relation:=3, FormulaText:="15"
    SolverAdd CellRef:=Cells(rw - 1 + i, 2), Relation:=1, FormulaText:="2"
    SolverAdd CellRef:=Cells(rw - 1 + i, 2), Relation:=3, FormulaText:="1"
    'SolverAdd CellRef:="$D$1:$K$1", Relation:=3, FormulaText:="0"
    SolverSolve True
    Next i
End Sub

 

[Worf]

Hello and welcome to the Board

Solver won’t accept a direct reference to other sheets, but you can use a formula like this and pass the formula cell to Solver:

='Answer Report 1'!C30

If this is a changing cell, Solver will overwrite the formula when finding a solution.