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

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

How do I fix this? I'd truly appreciate the assistance!

Hi,

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

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.
--
Vista Ultimate SP2 / Windows7 SP1
Office 2007 Ultimate SP3 / 2010 Prodessional

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?

On Saturday, March 22, 2014 12:15:45 AM UTC-7, Claus Busch wrote:
Hi,



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



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.

--

Vista Ultimate SP2 / Windows7 SP1

Office 2007 Ultimate SP3 / 2010 Prodessional

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


On Saturday, March 22, 2014 12:15:45 AM UTC-7, Claus Busch wrote:
Hi,



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



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.

--

Vista Ultimate SP2 / Windows7 SP1

Office 2007 Ultimate SP3 / 2010 Prodessional