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

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Greg Truby

MrExcel MVP
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

Well-known Member
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.

???

Greg Truby

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

sssb2000

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

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

MrExcel MVP
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

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

MrExcel MVP
OK - so what should the formula look like?

OdinsDream

Well-known Member
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
2
Views
76
Replies
2
Views
180
Replies
14
Views
221
Replies
11
Views
272
Replies
2
Views
35

1,171,864
Messages
5,877,961
Members
433,304
Latest member
niresh28

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.

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