Excale solver looping

[pk6472]

HI guys,

I have a excel sheet that i have to optimize an error and make the value to zero and i want to change some cells to get that goal,I have problem running the code I put the code below so anybody can help me,and i want to set it to GRGNonlinear as well,

Sub SolverMacro2()
'
' SolverMacro2 Macro
'
nn = 3651
r = o
For r = 15 To nn
SolverReset
SolverOk SetCell:="$CX$" & r", MaxMinVal:="3", ValueOf:=0, ByChange:="$CR$ & r:$CU$ & r"
SolverSolve True
Next r
End Sub

 

[Denial]

You don't say what version of "Excale" you are using, nor do you say what sort of "problem running the code" you have. Both of which deficiencies make it a bit difficult to give you any specific help.

Firstly, have you "referenced" the Solver from within VBA (VBA>Tools>References)? This little Microsoft gotcha gets nearly everyone the first time they try to run Solver from within VBA.

Secondly, to find out how to do things like set your method to GRG Nonlinear, run Solver with macro recording turned on and see what clues that gives you. (Hint: you might need an argument like EngineDesc:="GRG Nonlinear" in your call to SolverOK, but this might depend upon the version of Excel you are using.) (But, for Excel 2010 at least, EngineDesc:="GRG Nonlinear" is the default so you'd be getting that method anyway.)

As for the VBA code from which you are running Solver, I cannot help but notice a couple things that seem like oddities to me. (1) Why do you initialise the variable r to the variable named "o"? (2) I don't think you need to run SolverReset at every iteration, but could run it just once before you begin the loop.