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

#### sssb2000

##### Well-known Member
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?

### Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the \$ sign).
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?

The formula being impacted is:

Code:
``=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.

???

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

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!

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.

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)?

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!

OK - so what should the formula look like?

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.

Replies
4
Views
347
Replies
1
Views
216
Replies
0
Views
92
Replies
11
Views
223
Replies
4
Views
269

1,216,297
Messages
6,129,956
Members
449,545
Latest member
SURY

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back