Macro with Solver loop

**lichtensteinryan** · 12-15-2016, 09:27 PM

Hi -

I recorded my step for Solver as a Macro (code below) and am trying to have the macro replicate the steps for all cells in column AJ (there will be 60K+ cells in the workbook). I have no real VBA skills to speak and I need help please! Thanks!

Sub Macro1()
'
' Macro1 Macro
'

'
SolverOk SetCell:="$AJ$2", MaxMinVal:=2, ValueOf:=5, ByChange:="$AJ$2", Engine _
:=1, EngineDesc:="GRG Nonlinear"
SolverOk SetCell:="$AJ$2", MaxMinVal:=2, ValueOf:=5, ByChange:="$AJ$2", Engine _
:=1, EngineDesc:="GRG Nonlinear"
SolverSolve
End Sub

**lichtensteinryan** · 12-15-2016, 10:31 PM

For reference, these are my Solver specs:
Capture.PNG

**Alf** · 12-16-2016, 04:01 AM

Perhaps a macro like this then?

Please Login or Register  to view this content.

As you have constraints in your solver setup but not in the recorded macro I'm a bit unsure if they should be included or not. In this macro these constraints are included.

In the future don't forget to wrap you code when posting.

Alf

**lichtensteinryan** · 12-16-2016, 08:51 AM

Hi Alf - I copied the macro code into the VBA editor and ran the macro but nothing in the data changed. It's like it didn't run.

**Alf** · 12-16-2016, 09:11 AM

Strange. Could you click on solver and see if setup is changed i.e

target cell should be $AJ$xxx where xxx is the last row number with data in the AJ column. All the other parameters should be change accordingly

"AJxxx < = "AExxx" and so forth. This should indicate that the solver loop is working.

As for the data change could you upload a small sample file say a 100 rows so that I could do some testing.

Alf

**lichtensteinryan** · 12-20-2016, 02:39 PM

Hi Alf - Even with 25 rows the file is too large. Can we take this thread offline over email so that I can send you the file?

**MrShorty** · 12-20-2016, 02:51 PM

If I may interject, the indicated Solver model does not make sense to me. Your Solver model has both your objective function (the function to minimize in Set Target Cell) and your decision variables (the input to change in the By Changing cells) as AJ2. I would expect the Set target and the by changing to be different cells. Check your spreadsheet and your Solver model to make sure that you have defined these elements of the Solver model correctly.

**lichtensteinryan** · 12-20-2016, 03:16 PM

@mrshorty - makes sense. I've edited the Changing cell to AK2 which gives me back the same answer in AJ2 so I think that will work. I've edited the macro Alf wrote but it still is not running. I click run and nothing happens.

**MrShorty** · 12-20-2016, 03:28 PM

If AK2 is simply =AJ2 (or AJ2 is =AK2), I'm not sure you have really changed anything.

So that I understand, which way did you change it -- set AJ2 to a minimum by changing AK2 subject to the constraint that AK2 is between 0 and AE2, or did you go with set AK2 to a minimum by changing AJ2 subject to the constraint that AJ2 is between 0 and AE2, or some other variation?

In almost all of those cases -- assuming Solver doesn't error -- here's how I would solve the problem as currently specified

1) The smallest value for AJ2 (=AK2) that is between 0 and AE2 is 0, so set AJ2=0 (or set AK2 to 0, which is mirrored by AJ2).
2) If AP2 (some function of AJ2, I would guess???) is less than 5, adjust AJ2 (AK2) until AP2=5.

So, the solution will always be either AJ2=0 or the value of AJ2 that cause AP2 to be 5. If AP2 is always greater than 5, then AJ2 will always be 0, and it will look like Solver didn't run, because once AJ2 is 0, there will be nothing to change.

I think we need to better understand what function is in column AJ, how it relates to AP, and try to understand exactly what the objective function and decision variables should be.

**lichtensteinryan** · 12-20-2016, 03:35 PM

Gotcha, yeah it might help if i explain what I'm trying to achieve. I'll lay it out by what cells are what.

AE2: current price (this is a number value cell)
AI2: profit per unit at current price (this is a number value cell)
AJ2: promotional price (this is a formula cell)
AK2: promotional profit per unit (=AI2-(AE2-AJ2) (formula cell)
AP2: absolute value of promotion price/promotional profit per unit (=abs(AJ2/AK2)) (formula cell)

I'm trying to find what the minimum AJ2 would be that would give me an AP2 that is greater than 5 (5 at a minimum). AJ2 has to be greater than 0 (since it's a selling price) and has to be less than AE2 since it's a promotional price.

**lichtensteinryan** · 12-20-2016, 03:47 PM

to clarify for AJ2 = there is no formula in the cell currently. this is what I'm trying to solve for.

**MrShorty** · 12-20-2016, 05:17 PM

It looks like AP2 might be a better objective function/Set target cell. Some math/algebra before we get to programming:

1) AP2=ABS(AJ2/AK2)=ABS(AJ2/(AI2-AE2+AJ2)). You have not given any indication what is in AI2, but I would guess that it is some function of AE2.
2) At one extreme: AJ2=0, AP2 becomes ABS(0/AK2)=0
3) At the other extreme: AJ2=AE2, AP2 becomes ABS(AJ2/(AI2-AE2+AJ2))=ABS(AE2/AI2), which, I assume will always be greater than 5.

So, somewhere between AJ2=0 and AJ2=AE2, AP2 is equal to 5. Are you certain that there is only one possible value for AJ2 where AP2=5, or are there multiple possible values for AJ2 where AP2=5?

Here's how I would analyze the problem next:

4) Set AP2=5 and solve for AJ2 --- 5=ABS(AJ2/(AI2-AE2+AJ2)) This will probably involve two runs through, one where the denominator is positive and one where the denominator is negative (if the denominator can be both positive and negative, given the other constraints). If AI2 does not involve AJ2, then this should be fairly straightforward algebra, you will get one or two possible solutions for AJ2, choose the smaller. In this scenario, Solver is not even needed, since we found the solutions algebraically. You would only need to enter the appropriate formula into AJ2 at AP2=5.
4a) To be absolutely certain that this results in the desired solution, you may want to check what happens at AJ2 slightly larger than 0 or slightly less than AE2, and at other values for AJ2 to make sure that there are no surprises in the behavior of AP2.

I realize that this is all math and no programming so far, but this may yield easier programming solutions, once the math is understood.

**lichtensteinryan** · 12-20-2016, 06:43 PM

So I'm not necessarily trying to find the value that gives me AP2=5. I'd like to the find the minimum value of AJ2 that gets me as close as possible to AP2=5, therefore AP2 >= 5 in the constraints in Solver. AI2 is technically a function of AE2 but these are both specific data points that I have and are not formulas or variables that will change. AI2 will adjust at the same rate that AE2 does (i.e. if AE2 increased by 5, AI2 increases by 5). I don't believe it can be done in the manner you're saying because no there will not be just one possible value as I'm trying to find the minimum value and as AI2 and AK2 could be negative values.

**MrShorty** · 12-21-2016, 12:35 AM

Without more information, I will probably have to take your word for it that this cannot be solved algebraically. If AI2 is independent of AJ2, though, it really looks to me like the algebra should work out for a closed form solution of the equation/inequality. I think if I were solving this problem, I would pursue solving the above equation/inequality for AJ2. From that, I would learn how this function behaves and have a better idea how to solve it. If it ends up requiring a numeric Solver solution, I would expect to end up with an expression for AJ2 as a function of AP2. Then call Solver and tell it to set AJ2 to a minimum by changing AP2 subject to the constraint that AP2>=5 and any other constraints needed when the problem is rearranged in that way.

**lichtensteinryan** · 12-21-2016, 02:12 PM

I've ran solver manually on the first 20 cells and it is giving me the answer I need. I just need to be able to create a loop to do this calculation on every cell.

**MrShorty** · 12-21-2016, 03:12 PM

Alf's code in post #3 shows how to set up the loop. If that Solver model works, then it seems like that code should also work.

Do you or Alf know what happens when Solver is called from VBA and it encounters an error? I rarely if ever call Solver from VBA, and several of my Solver runs encountered the function's asymptote (#Div/0 error), and I wonder if that could cause Solver to exit without changing anything.

I would be curious to know if any of your solutions resulted in AP other than 5. My limited tests (without knowing typical values for AE or AI) all come to the solution where AP=5.

If it helps, I uploaded the simple spreadsheet I used to analyze this problem. The two algebraic solutions (assuming I did the algebra correctly) are given in rows 34 and 35 (I would expect that row 35 will always be the minimum solution). I note that the solutions obtained using the Solver model (row 40) are all the same as those in row 35, and the resulting values for AP are all 5. I included a chart that shows the basic shape of AP as a function of AJ (calculated in rows 4:27).

**MrShorty** · 12-21-2016, 04:02 PM

If it helps further, I adapted Alf's code so it would run across the columns in my file, and it seems to work just fine.

Please Login or Register  to view this content.

**Alf** · 12-21-2016, 05:45 PM

the function's asymptote (#Div/0 error), and I wonder if that could cause Solver to exit without changing anything

As the macro runs in a loop using the "SolverSolve Userfinish:=True" solver would just present the #Div/0 error and continue looping until the loop condition has reach its stopping point.

Taking the example above if the 5th value for i (i = 7) gives a #Dive/0 error you will get the solver result values for i from 2 to 6, the 7th value will be the string #Div/0 but the 8th value and all the following up to i = 14 will be "normal" values.

What could stop a solver macro is problem like this

The conditions for Assume Linear Model are not satisfied. (If you macro specify Linear Model)

The problem is too large for Solver to handle.

Solver encountered an error value in a target or constraint cell.

Stop chosen when maximum time limit was reached.

There is not enough memory available to solve the problem.

Another Excel instance is using SOLVER.DLL. Try again later.

Error in model. Please verify that all cells and constraints are
valid.

The problem with a macro is that you seldom get any feed back if solver encounter problems. Possible solution to this problem

ShowRef is a VBA function or macro to be called in place of displaying the Show Trial Solution dialog box. It is used when you want to regain control whenever Solver finds a new intermediate solution value.

Alf

**lichtensteinryan** · 12-22-2016, 02:50 PM

So I tried running Alf's code again and somehow it ended up working this time and has been working every time since! Thanks so much anyway!

One other question. What edit would you make to get the macro to only run on visible cells?

**Alf** · 12-23-2016, 02:35 AM

As I assume with visible cells that you got hidden rows you could try this modification on the original code:

Please Login or Register  to view this content.

Alf

**benlandgren** · 12-07-2017, 01:59 PM

I have tried Alf's code for similar issue and I think it partially works but as I am new to VBA I might have messed up something. I have 16 columns (from D to T) that have same formulas and different input values. I want to run the solver for each column individually. Solver goal is row 56 and I want it to result to 0 by changing values in rows 41:44. The solver runs through all of the columns but the solver goals do not result very close to 0. If I run solver individually for each column I get very close to 0. I also have 2 conditions: 48:51=0 and 41:44>=0.

Help would be much appreciated.

This is what I have been using but something is clearly wrong:

Please Login or Register  to view this content.

**Alf** · 12-07-2017, 05:09 PM

The solver runs through all of the columns

I'm a bit surprised by that as the only variable is the D column that goes from 56 to ??

To change column from D to T you would need a variable going from 4 to 20 So I would guesstimate something like

Please Login or Register  to view this content.

The first loop starts using the D column, it then loops through all i values and having done so it then should go back to the first loop and change cell address to the E column. I also think D41:D44 and D48:D51 should change to E41:E44 E48:E51 but as I have not seen your model I could be wrong.

Can you upload the file with your model?

Alf

**benlandgren** · 12-07-2017, 05:34 PM

Hi Alf and thank you for your reply. Yes, D41:D44 and D48:D51 changes to E41:E44 and E48:E51 respectively. All columns are basically identical with formulas and are independent to each other. Only the input values are different.

This VBA is still quite confusing to me and I need to ask you one thing before I try your code:
I have determined "i" as "Dim i as long". But how should I determine the j now?

Unfortunately I cannot upload the file as this is for work.

Cheers
Ben

**Alf** · 12-07-2017, 06:17 PM

Re dimming:

You can dim j and i as "Integer" the value of an "Integer" can vary between -32768 to 32767

If you dim j and i as "Long" the value can vary between -2147483648 to 2147483647. So you could use "Integer" without any problem but if the value of i going from 56 to Range("D" & Rows.Count).End(xlUp).Row went up to 41000 the macro would stop at 32767.

So the "D41:D44 and D48:D51" should first be expressed as:

Please Login or Register  to view this content.

and then

Please Login or Register  to view this content.

Have not tested it but hopefully it's right, if not tell me and I'll have another go at it

Alf

**benlandgren** · 12-07-2017, 06:29 PM

Ok so this is the code I am using now:

Please Login or Register  to view this content.

There is definitely something happening BUT after I run the solver the values that needs to change in cells 41:44 on column range D:E do not change at all. As a result of this the values that I want to become zero in cells D56:E56 are anything but zero.

**benlandgren** · 12-07-2017, 06:40 PM

Ok I tried to make the modifications that you suggest but for some reason the cells 41:44 on column range D:E do not change at all. As a result of this the values that I want to become zero in cells D56:E56 are anything but zero.

Here is the current code:

Please Login or Register  to view this content.

**Alf** · 12-07-2017, 07:15 PM

What do you wish to change? The target cell starts in column D and goes down from row 56 to ??. Having done this loop j changes to 5 i.e column E and changes row from 56 to ??.

The "ByChange" stays constant i.e. row number is constant 41 and 44 only column changes from D to E after the row loop i has gone through all values.

The same goes for the constraint, constant row number and after all i values have been tested column changes from D to E.

Since you can't upload the file try to explain what you hope to achieve after each change in i and after a change in j.

Alf

**benlandgren** · 12-08-2017, 08:55 AM

Hi, so I made a mistake in my previous post: I meant to say D:S instead of D:E.
I have modified the sheet in order to be able to post it here. Please see link below. Each column includes same formulas, only input values vary. Each column calculates values that are dependent on the input value and the column values can be plotted into a curve. In row 56 is solver goal which is to be 0. Rows 41:44 need to be solved. And Rows 48:51 =0. Rows 41:44>=0. The VBA code does not solve the values for some reason, maybe I have understood something wrong, but when I use solver individually for each column I can manage to get them solved correctly. Solving them individually takes along time however.

Link: https://www.dropbox.com/s/2pw8k8xmyt73214/SB.xlsm?dl=0

**Alf** · 12-08-2017, 10:31 AM

The first thing I noticed was that calculation was set to "manual" and solver was not to happy about it so I set it to "automatic". I then wrote a short macro for the range D to S:

Please Login or Register  to view this content.

It loops through columns D to S and tries its best to find proper solutions but it does not succeed that well. Uploading two images, the first is a snippet from you uploaded file and the second one is how the result looks after running the macro.

solver_org.jpg

Solver_macr.jpg

Alf

**benlandgren** · 12-08-2017, 10:55 AM

Thank you very much Alf, I will add some solver options to the code and see if that would help to reach better results. But otherwise the code seems to work well.

Ben

**Alf** · 12-08-2017, 03:04 PM

You are welcome and thanks for feedback

I'm sorry my mathematical know-how is not sufficient to analyze the "#num!" error must have something to with the exponent function.

Have been fiddling a bit with you constraint. I've added the value 0.1 to cell U35 and U36 and then I modefied the macro a bit i.e.

Please Login or Register  to view this content.

This time id did run without any Div0 or num error, so changing the constraint is of course the way to go. See image but still quite a bit away from what you are aiming for.

I also set "Objective cell" to min instead of 0.

solv_solution.jpg

Alf

**benlandgren** · 12-10-2017, 11:23 AM

Hi Alf,

For some reason I don't get any #num! errors and the solver runs very nicely now. Sometimes it is not able to find values for one or max two columns in 41-44 rows that result in zero in row 56. Then I just manually insert a good estimate to the cells in 41-44 that the solver did not solve properly and run the solver again. This helps 9 times out of 10. And if not I just manually insert another estimate and solve it again. I guess the solver is very sensitive to the initial values that are in 41-44 and based on those it either finds a solution or not. I have heard that for the solver there is possible to write a piece of code that gives some initial estimated values in order to result in better outcome, but I think it will be very large task and if the estimations are wrong then what happens.

Ben

**Alf** · 12-10-2017, 03:24 PM

solver is very sensitive to the initial values that are in 41-44 and based on those it either finds a solution or not.

Yes that's true especially is function is oscillating a bit. But it seems to me that you managed to overcome that problem.

Of course it's possible to let the macro check the target values in range D56:S56 and it one or more is not zero do a rerun on that particular column and set a new starting value perhaps using an input box. But to guesstimate starting value???

As far as I can see you consider this a minor problem i.e. not worth the effort so let's keep it that way.

Alf

Macro with Solver loop

Re: Macro with Solver loop

Re: Macro with Solver loop

Re: Macro with Solver loop

Re: Macro with Solver loop

Re: Macro with Solver loop

Re: Macro with Solver loop

Re: Macro with Solver loop

Re: Macro with Solver loop

Re: Macro with Solver loop

Re: Macro with Solver loop

Re: Macro with Solver loop

Re: Macro with Solver loop

Re: Macro with Solver loop

Re: Macro with Solver loop

Re: Macro with Solver loop

Re: Macro with Solver loop

Re: Macro with Solver loop

Re: Macro with Solver loop

Re: Macro with Solver loop

Re: Macro with Solver loop

Re: Macro with Solver loop

Re: Macro with Solver loop

Re: Macro with Solver loop

Re: Macro with Solver loop

Re: Macro with Solver loop

Re: Macro with Solver loop

Re: Macro with Solver loop

Re: Macro with Solver loop

Re: Macro with Solver loop

Re: Macro with Solver loop

Re: Macro with Solver loop

Re: Macro with Solver loop

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Solver Loop Macro Question

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions