Excel Solver VBA, Multiple dynamic input.

[Hightwalker]

Hi there, I’m using the solver add-in for excel, I’m using it to optimize problem. However I’m running my head against a wall.

I must say I’m no pro at excel, so it might be a simple question, but I’ve searched the net for hours now, so I hope you can help me.

The variables set in ByChange needs to be something like:

SolverOk SetCell:="$A$8", MaxMinVal:=2, ValueOf:="0", ByChange:="$B$4”:”$B$12”, “$C$4”

The problem is that “$B$12” can and will change all depending on another factor (nRow) that is including in the solving progress.

I have then been using this:

ByChange:= .Range(Cells(4 , 2) , Cells(4+nRow , 2).Address

But with this I cant seem to add cell “C4” to the list of variables, it keeps making an error and wont run my VBA Script.

I Hope you can help me.
Thank you very much

Best regards
Peter Hansen

 

[ZVI]

Hi Peter and welcome to MrExcel Board!

Try this:
ByChange:=Union(Range("B2:B" & 4 + nRow), Range("C4")).Address

Regards