CODEWITHSUNDEEP
vbaexcelsolver
Newskooler
Newskooler

Reputation: 4245

How to Target Excel Solver's Value Cell

With the code below, I am trying to use solver via Excel VBA. The reason for doing it is because I am using it for a large list of values and because I wish to target the value I want to solve for via a cell, however for some reason I am having issues with the ValueOf:=SingleCell.Offset(-1, 1).Value, _ If I change it to ValueOf:="-180" (or any other number) it work fine. My goal is to link it to a cell, as the cell varies.

Info: SingleCell.Offset(-1, 1).Value, _ has the value of -180

EDIT 1

Sub WatchlistSolver()

Dim SingleCell As Range

Range("N2").Select

'Need to link account value
Do
    ActiveCell.Offset(1, 0).Select
    SolverReset
    SolverOk SetCell:=ActiveCell.Offset(0, 11).Address, _
             MaxMinVal:=3, _
             ValueOf:=SingleCell.Offset(-1, 1).Value, _
             ByChange:=ActiveCell.Address
             SolverSolve userFinish:=True

    ActiveCell.Offset(0, 0) = Int(ActiveCell.Value)

Loop Until ActiveCell.Offset(0, -1).Value = ""

End Sub

EDIT 2

Sub WatchlistSolver()

Range("N2").Select

'Need to link account value
Do
    ActiveCell.Offset(1, 0).Select
    SolverReset
    SolverOk SetCell:=ActiveCell.Offset(0, 11).Address, _
             MaxMinVal:=3, _
             ValueOf:=CStr(Range("O1").Value), _
             ByChange:=ActiveCell.Address
             SolverSolve userFinish:=True

    ActiveCell.Offset(0, 0) = Int(ActiveCell.Value)

Loop Until ActiveCell.Offset(0, -1).Value = ""

End Sub

Upvotes: 0

Views: 3078

Answers (1)

Gary's Student
Gary's Student

Reputation: 96753

UNTESTED

Try changing:

ValueOf:=SingleCell.Offset(-1, 1).Value

into:

ValueOf:=CStr(SingleCell.Offset(-1, 1).Value)

That is because Solver does not appear to want a number for the parameter value, it wants the string equivalent of the number.

EDIT#1:

If you want to use a specific single cell on the Active worksheet then:

ValueOf:=CStr(Range("O2").Value)

Upvotes: 1

Related Questions