Issue with Solver Using VBA

khabi21

New Member
Joined
Oct 12, 2016
Messages
35
I am using VBA to automatically enter maximum & minimum constraints for a cells value in Excel's solver. I have named ranges in the worksheet that I use to input a user defined maximum/minimum for the value.
It works for every value except when the user places "40" in the cell. When 40 is the value, the solver constraint is not added. There are no errors, the code runs fine, but the solver just decides not to add the 40 constraint. If I change 40 to any other number, it adds it just fine.

Here is an example of the code that adds the constraints to the solver. If the value of either xMAX or xMIN is 40, then it just decides not to add it to the solver. Any other value works fine which tells me it's not a code issue. HELP! 40 is a very common value.

Code:
'x must be between 0 & 40:    
SolverAdd CellRef:=Range("x").Offset(-1), Relation:=1, FormulaText:=Range("xMAX")
SolverAdd CellRef:=Range("x").Offset(-1), Relation:=3, FormulaText:=Range("xMIN")
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
It also cannot handle the value 1. So 1 and 40 create issues. I'm thinking of writing some code in the background where if the user inputs 1 or 40 to change the value to 0.99999999 or 39.99999999 respectively. Then format them so they look like 1 or 40. The vba code handles those values just fine.
 
Upvote 0

Forum statistics

Threads
1,224,820
Messages
6,181,162
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top