Solver VBA code across several sheets

[pedrostur]

Hello everyone,

I was wondering if there was a way to run solver across multiple sheets with just one button? For example, I have a file with around 100 sheets. This file has the same format (the data is placed in the same row/column in every sheet) for every sheet. Also I'm not really sure how to add constraints. Bellow, I will attach the excel file with the solver arguments.

I tried to use the following code:

Please Login or Register  to view this content.

[pedrostur]

Thanks in advance

[pedrostur]

I had to decrase the file size because of the attachment restricted size.

[shg]

Maybe ...

Please Login or Register  to view this content.

[pedrostur]

I add some constraints. The code seems to be running but the results are wrong when compared to when I perform the solver procedure individually (for each sheet)

Please Login or Register  to view this content.

[shg]

Step through the code and look at the constraints in the Solver dialog before SolverSolve. Are they correct?

[pedrostur]

I think they are. I recorded the following macro:

Please Login or Register  to view this content.

[shg]

Not really what I asked.

Put a Stop statement before SolverSolve. When the code breaks, open the Solver dialog and look carefully at the model. Is it correct?

[pedrostur]

I'm sorry, I donīt really understand. My knowledge in VBA is very basic.

[pedrostur]

I just recorded the macro so you could see the procedures I took

[shg]

Right. You say you get different results when running via VBA versus the UI. So:

1. Copy the original input values to re-use below, set up the model in the Solver dialog, and run Solver manually. Good result, correct?

2. Restore the original input values and run the code from VBA. Bad result, correct?

How could that be? Answer: Only if the model set in step 1 is different from that set via VBA. So,

3. Restore the original input values, and step through the code until just before SolverSolve. Look at the model in the Solver dialog in the UI. Is it exactly the same as when you set it up manually?

[pedrostur]

I feel really dumb :P. I don't understand what you mean by original input values and stepping through the code until just before SolverSolve.

After I run the code, the solver dialog is the same as before running the code (manually).

After running the code:
In the first sheet I get #NUM error in the target cell and the changing cells seem "normal". In the rest of the sheets I get a negative value in the target cell (which is not supposed to happen) and in the three changing cells I get 1's.

[shg]

Post a workbook with a single worksheet that illustrates the problem, with ...

o the Solver model manually configured and ready to go, so I can get the correct result just by pressing Solve

o the code installed and ready to go that gives the errant result

[pedrostur]

Hey, I managed to run the code. I was missing one line of code. Bellow is the code I used. Thanks for your help shg!

Please Login or Register  to view this content.

[shg]

Well done, good job.