Is there a way NOT to override a formula?????

[sssb2000]

hi,
quick question....
i have a cell with a formula.....and a macro that depending on certain conditions adds a 1 to that cell.
Now the problem is that once that 1 is added to the cell, the formula is getting erased and instead, a number is replaced. The number is in fact the correct number, but i need to have a formula in there for future uses.


Is there anyway to have the cell keep it's formula?

 

[Greg Truby]

Something like:

rng.formula = rng.formula & "+1"

might work. But this could create a rather lengthly formula after many iterations. (Eventually hitting the 1,024 character limit?)

What's the actual formula being impacted?

 

[sssb2000]

The formula being impacted is:

=IF(T6=100,"",IF(W6>$H$6,TRUNC(W6*V6),""))

but the cell doesn't always get effected with the macro.....so what i'm ending up with is that after running the macro, some cells (in column Y) still have the formula, while the effected cells holding only values and no formula.

???

 

[Greg Truby]

1. After the macro runs what should the formula look like?
2. This formula was in Y6, correct?

 

[sssb2000]

Yes, the formula is in Y6

i would like the formula to be exactly the same....but the value shown be one unit higher than what it was before the macro!

 

[OdinsDream]

To do what you're asking, your formula will need to be changed, as mentioned above, and the last bit of it will have to be "+1"

You can't add anything to a cell without either replacing it, altering the formula itself, or altering the data that the formula references.

 

[Greg Truby]

So the new formula would be:

=IF(T6=100,"",IF(W6>$H$6,TRUNC(W6*V6)+1,""))

which would increase the value by 1, but only of the condition of T6=100 is met?

Or the new formula is:
=IF(T6=100,"",IF(W6>$H$6,TRUNC(W6*V6),""))+1

which would do as you say, "increase the value by one" (no matter what, i.e. if T6 does not equal 100, show 1)?

 

[sssb2000]

Thanks for the responses...
i think Odinsdream answered the question though...

Greg Truby,
i can't change the formula to what you suggested because the condition is different that T=100.....that's just a side condition....
the real condition is if the value in that cell is the lowest value within that column.....

see the macro right now looks within the column, finds the lowest number, and then adds 1 to the VALUE! in the process, i lose the formula within that cell so basically if i'm trying to reset, or go back a step, i would have to write the formula again!

 

[Greg Truby]

OK - so what should the formula look like?

 

[OdinsDream]

You could copy the text of the formula into another cell somewhere, and if you ever needed it again to go "back" a step, you could copy it back over into the original cell.