Search: | |||||

| ||||

Home » Forum » MS Office | RSS Feeds |

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

still.

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.

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.

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.

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

formula.

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?

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

Didn't find what you were looking for?
Find more on clearing cells value without deleting the formula I've made in cell from excel formula , HOW?
Or get search suggestion and latest updates.

Related Topics:

- Copy Value produced by formula in cell C3 to cell D3
- formula returns to multiple cells, without Array object?
- VBA code to add values in two cells which have formulas
- Copying current cell's value to another cell
- Action taken by Excel when a condition causes a cell's value
- Ctrl+Shift+Arrow Fails To Select Cells When Used in Formula Bar
- cells containing formula that refer to user-defined VBA function
- Using an array formula as a hyperlink cell reference
- Update a cell based on a DDE updated cell value
- HOW TO FIND LAST CELL VALUE IN PARTICULAR COLUMN AND ROW IN EXCEL
- Fill VBA form with Cell values of an Excel sheet
- HOW TO FIND LAST CELL VALUE IN PARTICULAR COLUMN AND ROW IN EXCEL
- Need of an Excel Formula
- Formula to match,lookup and then retun alternate value
- copy down formulas in excel
- how are cell coordinates referenced on a clicked cell
- to see if text in a cell in a column is contained in any cell
- Moving data in the last cell of row to another column cell
- Function needed to split the content of a cell intomany cells
- Function needed to split the content of a cell into many cells
- Finding and replacing the cell next to a cell with a certain string
- active cell no longer advances to cell below
- A Paragraphs in one cells --> convert into a line in per cells
- Cell validation based on the contents of previous cell
- Copy cell contents to same cell on different worksheet if contains