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:

SolverOK ByChange:=Range("A1:J1")

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.

Answers


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

Need Your Help

ActionFilterAttribute: Where is the 'Cancel' property?

model-view-controller override renderaction actionfilterattribute

Whatever happened to the Cancel property on the ActionExecutingContext? How would one abort a RenderAction when using an ActionFilterAttribute or is there another way to skin this cat?

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.