Adding cells to change in solver
I have a range from A1 to J1 that I would like to change using solver to optimize a certain value. Usually, I do the following:
However, my program randomly fills in some cells in the range A1:J1 with fixed numbers that must remain there. How do I only add the empty cells to the SolverOk ByChange:= argument?
A1 B1 C1 D1 E1 F1 G1 H1 I1 J1 0 1 1 0
For example I might have the above setup and I would only want to add cells B1, D1, E1, G1, H1, and I1 to the solver change cells argument.
I tried looping through the empty cells and adding them to solver but that didn't work because only the last blank cell would be added and not the other ones. Any help would be really appreciated. Thanks so much.
You can capture any truly blank cells in a range (using SpecialCells(xlBlanks)) then feed this address into the Solver VBA (ByChange:=rng1.Address), ie sample code below which runs if there are blank cells
Sub Macro1() Dim rng1 As Range On Error Resume Next Set rng1 = Range("A1:J1").SpecialCells(xlBlanks) On Error GoTo 0 If Not rng1 Is Nothing Then SolverOk SetCell:="$B$2", MaxMinVal:=1, ValueOf:=0, ByChange:=rng1.Address, Engine:= _ 1, EngineDesc:="GRG Nonlinear" End If End Sub