Macro with Solver

[yichuansancun]

Hi All,

I tried to record a macro for Solver in Excel. After recording the macro, I
ran it and the message was that the "SolverOK" which is the first word in the
body is not a defined Sub or function.

Sub Calculate()
'
' Calculate Macro
'

'
SolverOk SetCell:="$N$7", MaxMinVal:=2, ValueOf:="0", ByChange:="$N$3"
SolverSolve
End Sub

How to solve this problem?

I am using excel 2007

Thanks,
Perri

 

[yichuansancun]

I need to correct my question, the macro for solver works, but it doesn't complete the entire step. When I run the macro, the Solver Results window will pop up and I have to click "ok" to complete the steps.

How can I incorporate the clicking "ok" into the macro?

Thanks,
Perri

 

[shg]

Try

SolverSolve UserFinish:=True

 

[yichuansancun]

Thanks!!!

 

[shg]

You're welcome.

Help is your friend.

 

[yichuansancun]

Wait... I said too early

Once I added

userfinish=true, the macro will work only the first time but won't work the 2nd and after...

Why is that?



Perri

 

[yichuansancun]

I meant to say add

userFinish:=True

then the macro won't work after the first time
</pre>

 

[shg]

Can you post the code as you have it now, and explain exactly what you do that results in an error?

You might start the code with SolverReset to clear out any prior model (not the Set and Changing cells so much as prior constraints).

 

[yichuansancun]

Sub Macro8()
'
' Macro8 Macro
'

'
SolverOk SetCell:="$N$7", MaxMinVal:=2, ValueOf:="0", ByChange:="$N$3"
SolverSolve userFinish:=True

End Sub

When I run the macro, the solver results "#num!".

Is there a way for me to upload this file somewhere on this board?

Thanks,
Perri

 

[shg]

No, but you can store it on box.net and post a link.