wgjack75
10-31-2019, 02:16 AM
Hi,
I want to run Solver from VBA, and I would like to vary the "ByChange" cells depending on certain conditions in other cells. (This is to minimise the number of ByChange cells, and remove any redundant cells i.e. ones that definitely will have no effect on the target cell). This means that the range of ByChange cells will be non-contiguous. I have got the code to work okay to define this non-contiguous range (using the If and Union functions). However, I cannot get the Solver VBA code to accept the resulting defined range. It may be that this is just beyond the capabilities of Solver, but I thought I would ask in case anybody knows a way around this.
The relevant code (for Solver) is simply as below. SolverRange is the range that I have defined earlier in the subroutine using the If and Union functions. For example, SolverRange might equal the range (A4:A7,A10:A14,G8:G12)
SolverOk SetCell:="JanNetCost", MaxMinVal:=2, ValueOf:=0, ByChange:=Range("SolverRange"), Engine:=3, EngineDesc:="Evolutionary"
I have also tried it without the Range function, i.e. ByChange:=SolverRange, but that doesn't work either.
Any help gratefully appreciated!
Thank you,
Jack.
I want to run Solver from VBA, and I would like to vary the "ByChange" cells depending on certain conditions in other cells. (This is to minimise the number of ByChange cells, and remove any redundant cells i.e. ones that definitely will have no effect on the target cell). This means that the range of ByChange cells will be non-contiguous. I have got the code to work okay to define this non-contiguous range (using the If and Union functions). However, I cannot get the Solver VBA code to accept the resulting defined range. It may be that this is just beyond the capabilities of Solver, but I thought I would ask in case anybody knows a way around this.
The relevant code (for Solver) is simply as below. SolverRange is the range that I have defined earlier in the subroutine using the If and Union functions. For example, SolverRange might equal the range (A4:A7,A10:A14,G8:G12)
SolverOk SetCell:="JanNetCost", MaxMinVal:=2, ValueOf:=0, ByChange:=Range("SolverRange"), Engine:=3, EngineDesc:="Evolutionary"
I have also tried it without the Range function, i.e. ByChange:=SolverRange, but that doesn't work either.
Any help gratefully appreciated!
Thank you,
Jack.