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)

"David Wakeling" wrote in message
om...
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