MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

clearing cells value without deleting the formula I've made in cell from excel formula , HOW?

  Asked By: Conrad    Date: Nov 14    Category: MS Office    Views: 4892

I want to input data to the sheet cells, with some value from textbox, but every
time I inputed a value, the formula that I've made in the cell with excel
formula (not macro) will be cleared too. It is any way to keep the formula



8 Answers Found

Answer #1    Answered By: Milton Robinson     Answered On: Nov 14

cell  can only hold one thing - a formula  or a value.

Explain in more detail what you actually want to achieve - there's probably a
different way to do it.

Answer #2    Answered By: Vinit Online     Answered On: Nov 14

cell  can only hold one thing - a formula  or a value.

Explain in more detail what you actually want to achieve - there's probably
a different way to do it.

Answer #3    Answered By: Jake Williams     Answered On: Nov 14

if it's this simple, you can use the & to put formulas together in cells  or put
text next to a formula  etc.

try that to see if it works for you...

Answer #4    Answered By: Muriel Dunn     Answered On: Nov 14

I've made  one RefEdit, two text box,which the first text boxt express the
begining value, and the second textbox  is the upper value. I select a cell  with
RefEdit, and the I do For-Next procedure. And it's handled by a command button.
Sub CommandButton1_Click ()

For n = TextBox1.Value to TextBox2.Value
Range(RefEdit).Value = n
Next n
End If

But that procedure means replace the cell value  I've select, with inputed
value in textboxes, and also clears the formula  within cells. Is there any way
to keep the formula in the cell and the value we've inputed in the textbox just
change the value no the formula.

Answer #5    Answered By: Trae Thompson     Answered On: Nov 14

If the refedit cell  has a formula  in than it must be the result of a
calculation of some sort dependent on other cells. It it the other
cells which must change to give the desired values in the refedit cell
so one or more of those other cells  must be changed, so perhaps you
could use Goal seek...

For n = TextBox1.Value to TextBox2.Value
Range(RefEdit).GoalSeek Goal:=n, changing Cell:=Range("C6")
Next n

You will have the value  you want in the refedit cell and the formula
will remain.

Note: C6 above is only an example cell address.

Answer #6    Answered By: Rochelle Elliott     Answered On: Nov 14

I've uploaded 'GoalSeekvalues in loop.xls' in the 'Code Sample Files'
folder of the files section of this group. I'll delete it in a few days.

Answer #7    Answered By: Silvia Chapman     Answered On: Nov 14

As I indicated before, a cell  can only hold one thing - either a value  or a

Your Range(RefEdit).Value is going to completely replace the formula/value
contents of all the cells  in your range with the value of "n".

As you are executing it from within a loop, the cells will be given each value
of "n" in turn, but of course you'll only see the last one.

There is really no such thing as a value in a cell (as a separate item). The
value is actually just a different view of the contents of the cell. For
instance, try

Range("a1").Value = "=3+1"

If you look at A1, it'll be showing 4, but if you edit it, you'll see that it
actually contains "=3+1". I.e. it doesn't matter whether you put the formula
into the cell via .Value or .Formula.

However, if you look at the .Value and .Formula properties of the cell, you'll
get "4" and "=3+1" respectively. I.e. you get the same thing back - just two
different views of it:

MsgBox (Range("a1").Value) returns "4"
MsgBox (Range("a1").Formula) returns "=3+1"

I suppose the question is what are you trying to achieve? What sort of formula
would you want to have in a cell that you also want to store a value in? What
are you trying to do in these cells?

Answer #8    Answered By: HD Racer     Answered On: Aug 04

Delete all input cells, but keep the formulas!

Your worksheet may be set up with formulas that operate on a number of input cells. Here's an easy way to clear all input values while keeping the formulas intact.

Press F5 to display the Go To dialog box, and click the Special button. In the Go To Special dialog, choose the Constants button and select Numbers. When you click OK, the nonformula numeric cells will be selected. Press Delete to delete the values. The Go To Special dialog box has many other options for selecting cells of a particular type

HD Racer
from jwalk