VBA Solver help needed

[Kernkraft]

Hello, I added the reference Solver in tools/references which cleared up one problem. Now I have another.

I cannot get the ValueOf portion of the code to reference the cell I would like to set another cell equal to... essentially it skips over it.

I've tried to name the cell, reference it outright by using ValueOf:=Range("C3") or ValueOf=Range("trgA") ... no dice, it will not reference the cell.

Sub Solver3()
'
'
SolverReset
SolverOk SetCell:="$E$23", MaxMinVal:=3, ValueOf:=Range("trgA"), ByChange:="$E$11:$E$16,$E$18:$E$22"
SolverSolve True
End Sub

Any help would be greatly appreciated.

 

[Kernkraft]

I should also mention that I tried Peltier's version, but have no idea how to reference a particular cell for which I want the value to equal.

Sub Solver2()
'
'
'
    Application.Run "SolverReset"
    Application.Run "SolverAdd", "$E$23", 3, "trgA"
    Application.Run "SolverOk", "$E$23", 3, "trgA", "$E$11:$E$16,$E$18:$E$22"
    Application.Run "SolverSolve", True
End Sub

 

[Kernkraft]

Can I get some help please?

 

[shg]

Does trgA change in reponse to any of the changing cells? That's not allowed.

 

[Kernkraft]

Does trgA change in reponse to any of the changing cells? That's not allowed.

No it does not, it's the cell I want to be able to physically manipulate within the spreadsheet and then based on that change, I want it to change $E$11:$E$16,$E$18:$E$22 so that E23 is equal to trgA

 

[shg]

Try

..., ValueOf:=Range("trgA").Value2, ....

Then open Solver in the UI and look at the model.

 

[Kernkraft]

Try



Then open Solver in the UI and look at the model.

You are my hero

Thank you so much!

 

[shg]

You're welcome, glad it worked for you.