Excel 2007: Help creating a macro that will run Solver?

[chrys.tee]

Hello,

I've set up a Solver in an excel sheet, and I'm trying to create a macro that will initiate the Solver when the user clicks a textbox in the Excel sheet.

I recorded the macro, and it runs splendidly on Excel 2010. However, when I try to run it on Excel 2007, I receive the error message: "Solver: An unexpected internal error occurred, or available memory was exhausted."

I'm new to using VB, but I've checked the references and SOLVER is already checked. The Solver also works when I run it manually outside of the macro.

After reading online, I figured out how to unlock the SOLVER (SOLVER.XLAM) code, to try to make modificatinos, but I'm unclear as to what to do next.

My current macro code is as follows:

--------------------------------------------

Sub RunCalc()

' RunCalc Macro
'

'
Sheets("Set-up (DO NOT ALTER)").Select
Range("G13:G17").Select
SolverOk MaxMinVal:=1, ValueOf:="0", ByChange:="$G$13:$G$17"
SolverSolve
Sheets("K Calculator").Select
End Sub

 

[Claus Busch]

Hi,

Am Fri, 21 Mar 2014 18:48:53 -0700 (PDT) schrieb (e-mail address removed):

I've set up a Solver in an excel sheet, and I'm trying to create a macro that will initiate the Solver when the user clicks a textbox in the Excel sheet.

I recorded the macro, and it runs splendidly on Excel 2010. However, when I try to run it on Excel 2007, I receive the error message: "Solver: An unexpected internal error occurred, or available memory was exhausted."

for solver you have a range with values and a range with a formula. You
set a value for the range with the formula and set the range to change
to the range with values. E.g. this could look like:

'H13 is the cell with the formula
SolverOk SetCell:="$H$13", _
MaxMinVal:=3, _
ValueOf:="0", _
ByChange:="$G$13:$G$17" 'G13:G17 is the range
SolverSolve 'with the values


Regards
Claus B.

 

[chrys.tee]

Thank you for your response Calus.
I don't have any problems with the Solver code itself, just the Macro code to initiate the Solver. Instead of asking the user to initiate the Solver, manually, I'd like to be able to set up the Macro so that they click the textbox "Run calculator" and the Solver will automatically run.

Any tips?

 

[CT]

Hello Claus,

Thank you so much! I added a SetCell per your post and it now works!

My new code:

Sub RunCalc()
'
' RunCalc Macro
'

'
Sheets("Set-up (DO NOT ALTER)").Select
Range("G13:G17").Select
SolverOk SetCell:="$D$23", MaxMinVal:=1, ValueOf:="0", ByChange:="$G$13:$G$17"
SolverSolve
Sheets("K Calculator").Select
End Sub