change cell value, but keep formula

[David Wakeling]

Hello,
Could someone tell me how to change the value
of a cell, without also changing the formula?
The following code illustrates my problem:

Dim r As Range
Application.EnableEvents = False
Set r = Range("A1")
r.Formula = "= 42 / 100000"
r.Value = "negligible"
MsgBox "r.Formula = " & r.Formula & ", r.Value = " & r.Value
Application.EnableEvents = True

Assignment to the value seems to destroy the formula.

Many thanks,

David Wakeling

 

[Bob Phillips]

You cannot.

If there is a formula in the cell, then the value is the result of that
formula, they are not independent.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[keepITcool]

yep... the formula evaluates to the value,
setting either destroys the other.

you probably want to set then number format

..NumberFormat = "[<0.1]""negligible"";General"


keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >