Can not clear cell contents which are part of a range causing typemismatch in Solver call

[ferdaozdemirsonmez]

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

$J$2:$J$27,$R$2:$R$27,$Z$2:$Z$27

on these cells from previous unsuccessful run).

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.

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

 

[WK505]

LPOptimization1Sheet

Sub deleteSelect()

'from Michael at StackOverflow
'Get User Selected Range

Dim myRange As Range
Set myRange = Selection

'User added line below
myRange = ""

End Sub