Loop that adds decimal value until another cell = 0

[realtoast]

I have need to create a for next loop that repeatedly adds .00000000000001 to the value of a cell until another cell = 0.

For example, cell B2 is a single value that changes many cells through out a spreadsheet. The initial value of B2 is 1.0420229207198.

Another cell, A2, is affected by the value in B2, as well as another User Input cell, A3. The goal is to, after User input in A3, adjust B2 +/- until A2 is returned to 0. I would like a for loop that adds or subtracts .0000000000001 (13 decimals) to the beginning value of B2 until A2 reaches 0.

When user changes the input value in A3, the result in A2 changes, but B2 remains static. So the loop would incrementally nudge B2 until the result in A3 is back to 0.

I am able to use Solver, easily enough, to do all the above. But, my customer's Excel is throwing an error and he's not able to use Solver. So I need a VBA method to do the same.

Note, there are many dozens of linked formulas in the algorithm, with many variables and many other user input cells. So, this is not merely a math problem, as the initial value of B2 can change from User input in any one of the other cells. But the factor in B2, whatever that may be, can be manipulated to bring A2 to 0.

Thanks for any input!

R

 

[StephenCrump]

Writing a VBA loop to increment by .00000000000001 is a trivial exercise. But are you sure it's going to help?

Q1: What exactly do you mean when you say:

But, my customer's Excel is throwing an error and he's not able to use Solver. So I need a VBA method to do the same.

Why can't he use Solver? Or Goal Seek?

Q2: If you were going to program in VBA, what makes you think that successively incrementing by .00000000000001 is an efficient way to zero in a solution? Is there a chance you could be moving in the wrong direction? That you will take too long to get there? That you won't ever get to zero (within some specified tolerance)?

 

[realtoast]

Stephen, thank you so much for taking time to read my question and respond. The customer is throwing "Error 53, can't find Solver.dll". Perhaps he has a different version of Excel than mine(?). I've tried several workarounds, including having him build his own, new solver within his verson. Same error.

My question is unduly meandering. I'm really just looking for a for-loop able to increment one cell and stop when another reactive cell reaches 0. Assume that these two cells are connected tangentially through a complex of, say, 50 different cells and formulas, so not merely a question of addition/subtraction.

If I can get that much, I can engineer a solution in my gigantic algorithm.

 

[realtoast]

Here is a stripped down diagram of the idea. The total underlying math is not important. Just the indicated cells. In this model, the solution is 1.0242298520408.

The reason I want to increment such tiny numbers is, even 1.02422985 throws off the result from 0.

 

[realtoast]

Writing a VBA loop to increment by .00000000000001 is a trivial exercise. But are you sure it's going to help?

Q1: What exactly do you mean when you say:



Why can't he use Solver? Or Goal Seek?

Q2: If you were going to program in VBA, what makes you think that successively incrementing by .00000000000001 is an efficient way to zero in a solution? Is there a chance you could be moving in the wrong direction? That you will take too long to get there? That you won't ever get to zero (within some specified tolerance)?

Stephen, Goal Seek worked perfectly. Ignore my other posts. Thank you again for reading my question and your thoughtful reply. I'd never used Goal Seek before and would not have touched it without your tip.

Thanks again!

 

[StephenCrump]

Stephen, Goal Seek worked perfectly. Ignore my other posts. Thank you again for reading my question and your thoughtful reply. I'd never used Goal Seek before and would not have touched it without your tip.

Thanks again!

Great, I'm glad that worked for you.

There are some known issues with the Solver Add-in some versions of Excel. If your customer googles Error 53, can't find Solver.dll he'll find some suggested fixes. It could be as simple as unchecking the Add-in, restarting Excel, and then re-checking the Add-in. Worth a try, if he is interested in using Solver.