Increment Until value is reached

[rizmomin]

This is little complex for me so i need help...

I have a value in cell P1 and is always -ve.
This value is calculated by some tedious calcualtion in some other part of the sheet.

By entering value in Cell P2, the value in cell P1 will progress towards +ve number.

What i want is a formula or Macro to increment the value in Cell P2 by 0.01
until value in Cell P1 becomes +ve...

Below is the example of my manual calculation.

When i have
P2=0 P1=-23.58
P2=0.01 P1=-14.78
P2=0.02 P1= -9.30
P2=0.03 P1= -2.92
P2=.04 P1= 1.05

Since i have achieved P1 to be positive, calculation should stop with value in P2=0.04

I have to do this for 31 column and hence manually doing it takes long time



Please help and let me know if you have any questions..

Riz Momin

[mikerickson]

This will find the value you want. But it will be stuck in an infinite loop if P1 doesn't get to 0, (like if the formula in P1 gets replaced by a negtive constant.)

Please Login or Register  to view this content.

If the function in P1 is always increasing as P2 is increasing there are fancier (faster) routines.

Have you looked at Excel's GoalSeek or Solver?

[rizmomin]

Thank you for your response...

Since the value in p1 is derived from some tedious calculation, i will need to
add time delay for every increment...

Please help to add code for time delay...

Riz Momin

[mikerickson]

What happened when you tried that code?
The code waits until the Calulate is done.

[rizmomin]

When i run the code, it gives error...Yellow highlight on

Please help..

Riz

[mikerickson]

What error does it say?

This bullet proofs the code against non numeric values in P2

Please Login or Register  to view this content.

[rizmomin]

Thank you so much for your kind help.
This works great...

i need to ask 1 more questions.

i have data in 31 columns and they are 4 colums apart...
eg : 1st Colum P,T,X, and so on......

How would i do that all in one go...


Thank u in advance

Riz

[mikerickson]

Please Login or Register  to view this content.

[rizmomin]

Sorry, but i am little confused how to complete a full coding:

Below coding works great for Column P:


Now i want to extend that thru 31 columns with 4 colums apart as mentioned earlier.

I would appreciate if you would help me with comlete coding.

Thank U

Riz

[mikerickson]

This combines the two routines.

Please Login or Register  to view this content.

[rizmomin]

Once again thank you.
This coding will just do the 1st loop only ie: it will do for Column 16 and stop..

Please let me know the solution.

Riz

[mikerickson]

It advances for me. Each word "Range" should be preceeded by a period.
Can you post the code from your spreadsheet that you are using for this?

[rizmomin]

Oh great...i got it working...

Thank u very much for your generous help and hope you will help me in the future needs

Riz

[mikerickson]

Glad to have helped.