Hello,
I’m trying to use solver through VBA since my constrains will change. The problem im having is that my cell value is “0,1251” and after I run my code the solver used 1251 instead. How do I code my VBA so that the solver can read the decimal point?
The value in for example cell(17,31) is 1,251 %
Here is my code:
Please Login or Register to view this content.
Last edited by Pepe Le Mokko; 04-20-2020 at 06:18 AM. Reason: Code tags
Administrative Note:
Welcome to the forum.
We would very much like to help you with your query, however you need to include code tags around your code.
Please take a moment to add the tags. Posting code between tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.
Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here
I did it for you this time. Please read forum rules. Thanks
Solver does not always take kindly to the use of Scandinavian delimiter "," but sometimes one can fix it by using the US/UK delimiter "."
Still the surest way to get help is to upload a file so forum members can test in order to find a solution.
Alf
Hello Alf,
Thank you for responding.
I have tried to change the settings in Excel to use “.” Instead of “,”. It creates an error message:
“Solver: an unexpected internal error occurred, or available memory was exhausted”
I will attach the Excel file (although a bit messy). Hopefully, this makes it clearer.
For every new std I want it to maximize my return.
Last edited by Swestudent; 04-20-2020 at 03:47 PM.
There is a problem with your model. The line
tells solver to ignore any solver message and keep on running to the end of the loop. Replacing this line withPlease Login or Register to view this content.
will make the solver result box pop up with the message "Solver could not find a feasible solution". In the macro you specified that cell R33 should be equal to cell (17, y) i.e. AE17 a constraint that solver can't meet.Please Login or Register to view this content.
Not sure but I think solver may have a problem with the MMULT formula in cell R32.
You could try to contact the solver help desk
https://www.solver.com/excel-solver-online-help
and as a student you may be able to get the advanced solver for free.
https://www.solver.com/welcome-students
Alf
Thank you very much for trying to help me Alf!
Although i know it cannot find the answer to the solution since 1251 is a number that is way to high. If I click on the solver after the macro it tells me that it tried 1251 instead of 0,1251 (my cell value in AE17). But I will try and find an answer with solver.
Thank you again.
Last edited by Swestudent; 04-21-2020 at 08:30 AM.
Hi again,
If i change the code:
toPlease Login or Register to view this content.
It works, but for every iteration i need for AE17 to change on cell to the right (i.e next cell to be in the solver is AF17). Can i somehow code so that the second one "AE$17" changes one cell to the right in the next for-sling?Please Login or Register to view this content.
So I had another go at it and this modification of you macro seems to solve the problem as far as I can see. I just added ".Address" to the CellRef:=$R$33 line and simplified your code a bit in the beginning.
By the way you need to set the % format on cell AF17.
AlfPlease Login or Register to view this content.
Last edited by Alf; 04-21-2020 at 10:02 AM.
Alf, you are a lifesaver! Thank you so much!! It works and i have been pulling my hair over this "little" problem for days.
THANK YOU!
You are welcome
Thanks for feedback and rep. Yes it's funny that a "small" could cause so much problems. I was so fascinated by this I spent most of today indoors (just what "folkhälsamyndigheten" recommends by the way) tinkering with it.
If you are running your macro for a number of values you can speed shorten the run time by adding these two lines:
andPlease Login or Register to view this content.
AlfPlease Login or Register to view this content.
You are welcome
Thanks for feedback and rep. Yes it's funny that a "small" thing like this could cause so much problems. I was so fascinated by this I spent most of today indoors (just what "folkhälsamyndigheten" recommends by the way) tinkering with it.
If you are running your macro for a number of values you can shorten the run time by adding these two lines:
andPlease Login or Register to view this content.
AlfPlease Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks