Optimizing goal and seek code.

Hey!

I'd really appriciate if someone could suggest how to optimize this code. As you can see it is repeating itself over and over and therefore runs slowley.
I'm a beginner at this so help me out if you can.
Any suggestions?

Please Login or Register  to view this content.

Instead of "here's my code" a better way is "here's what I want to do" along with "here's what's in S68, S10, and L68."

This forum can be pretty strict about putting code inside of code tags. If you need instructions, check out the FAQ and Forum Rules links, and edit your post to conform to the rule (this could be an interesting discussion, and I would hate to have it shut down for such a little thing).

Be sure to include some of the little things (like turning screen updating off).

In part, this is difficult to give a complete answer without understanding the math behind the S68 calculation and how i fits into that calculation. If you explored the math around the problem, you might find a completely different solution than the kind of solution you are proposing here.

One thing I notice is that you are using some kind of "brute force" algorithm, counting i from 1 to 1000 and capturing different results. You indicate that you are a beginner, does this mean you are a beginner only to VBA, or are you also a beginner to numerical methods/algorithms as well? If you understand the math behind what you are doing, you should be able to come up with a different "root finding algorithm" (bisection or method of false position come immediately to my mind based on what you have posted) that will allow you to find each solution in <100 iteration rather than the 1000 iterations you are currently using. (Wikipedia description of different root finding algorithms: http://en.wikipedia.org/wiki/Root-finding_algorithm ).

If you are unfamiliar with the actual behavior of the function(s) being solved here, you might consider spending some more time becoming more familiar with the behavior of the function(s). An improved solution may become obvious as you become more familiar with the problem.