ferdaozdemirsonmez
New Member
- Joined
- Nov 18, 2019
- Messages
- 2
Hi,
I am trying to run excel solver add in from vba code. Before I run the macro function I clear the contents of the varying cells manually ( cause there are the range definition like
on these cells from previous unsuccessful run).
When I run the code, at line
I was getting type mismatch error. From your site I read a suggestion that I should qualify the range cells prior to calling SolverOK function.
Later, I used various ways of clearing the contents of the cells as below, nothing works. I still get the type mismatch error, and those varying cells still has the range definition. I think that, if I can find a way to clear the range definition, I will also get rid of the type mismatch error coming from the Solver.
I have been struggling with this LPOptimization1 method for a while. I will appreciate any help to solve my problem.
Best Regards,
Ferda
I am trying to run excel solver add in from vba code. Before I run the macro function I clear the contents of the varying cells manually ( cause there are the range definition like
$J$2:$J$27,$R$2:$R$27,$Z$2:$Z$27 |
When I run the code, at line
SolverOK SetCell:="$H$31", MaxMinVal:=1, ByChange:=rngAll
I was getting type mismatch error. From your site I read a suggestion that I should qualify the range cells prior to calling SolverOK function.
Later, I used various ways of clearing the contents of the cells as below, nothing works. I still get the type mismatch error, and those varying cells still has the range definition. I think that, if I can find a way to clear the range definition, I will also get rid of the type mismatch error coming from the Solver.
VBA Code:
Set rngAll = Union([J2:J27], [R2:R27], [Z2:Z27])
LPOptimization1Sheet.Range("$J$2:$J$27").Cells.ClearContents
LPOptimization1Sheet.Range("$R$2:$R$27").Cells.ClearContents
LPOptimization1Sheet.Range("$Z$2:$Z$27").Cells.ClearContents
For i = 2 To 28
'LPOptimization1Sheet.Range(Cells(i, 9).Address, Cells(i, 17).Address, Cells(i, 26).Address).ClearContents
LPOptimization1Sheet.Range(LPOptimization1Sheet.Cells(i, 9).Address).ClearContents
LPOptimization1Sheet.Range(LPOptimization1Sheet.Cells(i, 17).Address).ClearContents
LPOptimization1Sheet.Range(LPOptimization1Sheet.Cells(i, 26).Address).ClearContents
Next i
'1 Maximize 2 Minimize 3 Match a Specific Value
SolverOK SetCell:="$H$31", MaxMinVal:=1, ByChange:=rngAll
SolverOptions MaxTime:=100000, Iterations:=10000, Precision:=0.0000001, AssumeLinear:=False, StepThru:=False, Estimates:=1, Derivatives:=1, SearchOption:=1, _
IntTolerance:=0, Scaling:=False, Convergence:=0.001, AssumeNonNeg:=True
SolverSolve UserFinish:=False
SolverFinish KeepFinal:=1
I have been struggling with this LPOptimization1 method for a while. I will appreciate any help to solve my problem.
Best Regards,
Ferda