Completely new to VBA, need help on setting up a solver loop

[tsumannai]

I need help on how to set up a loop that solves a column of objective cells vs a column of variable cells.

It doesn't matter that type of solver it is, "GRG Nonlinear" seems to work fine.

My objective cells are E7:E207
Where the solution is minimized or =0 because I want a real root.

My variable cells are D7:D207

The variables that it is solving are a cubic equation where it outputs the real root (no imaginary parts). I've looked around the forum but I can't really seem to make much sense. Any help would be much appreciated!

I've made a basic macro so far

Please Login or Register  to view this content.

tsumannai

[MrShorty]

Does it have to be Solver? I built a tutorial spreadsheet showing how to find roots of polynomials directly in the spreadsheet using circular references with iteration turned on: http://www.excelforum.com/showthread.php?t=1111494 The NR algorithm for polynomials is quite simple. This may be a good approach if you and others who will use the spreadsheet are not bothered by having iteration enabled.

Here's another recent thread looping through a Solver algorithm. http://www.excelforum.com/showthread.php?t=1166949 The OP recorded a macro calling Solver, which Alf then adapted to a loop in post #3. I modified the code in post #17 to loop across columns instead of down rows, and Alf adapted it in post #20 to run on visible rows. What parts of these Solver loops do you have trouble understanding or implementing?

[tsumannai]

I played around with the script in the second link you sent me i think I managed to do it. Could you just skim over it to check if it's fine?

From column D and E and rows 7-107.

Please Login or Register  to view this content.

[MrShorty]

After a quick skim over, it looks good. I notice that you removed the Stop statement I had in there. Does that mean that you stepped through it in the debugger enough times to be confident that it runs correctly?

[tsumannai]

It ran fine without the stop statement and the results that I plotted seemed to match what It was meant to be. (physically in the real world.)

Thankyou very much for helping me out, I've definitely learnt a few things today!

[MrShorty]

Glad it is working.

As someone who is "completely new to VBA", I would encourage you to put a Stop statement back into the procedure and spend some time becoming familiar with VBA's debugging tools, if you did not spend much time in the debugger already. Half of programming is writing the code, and the other half of programming is debugging the code -- including becoming familiar with available debugging tools.