change cell value, but keep formula
 

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

change cell value, but keep formula
 

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

change cell value, but keep formula
 

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


(David Wakeling) wrote:

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