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

sssb2000

Well-known Member
Joined
Aug 17, 2004
Messages
1,169
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?
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,014
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
Joined
Aug 17, 2004
Messages
1,169
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
Joined
Jun 19, 2002
Messages
10,014
1. After the macro runs what should the formula look like?
2. This formula was in Y6, correct?
 

sssb2000

Well-known Member
Joined
Aug 17, 2004
Messages
1,169

ADVERTISEMENT

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
Joined
May 19, 2002
Messages
541
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
Joined
Jun 19, 2002
Messages
10,014

ADVERTISEMENT

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
Joined
Aug 17, 2004
Messages
1,169
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!
 

OdinsDream

Well-known Member
Joined
May 19, 2002
Messages
541
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,199
Messages
5,623,333
Members
415,968
Latest member
Chabal74

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top