Why do fomulas disappear and get replaced by values after using VBA?

[SeekExcellence]

Hello,
In my more complicated excel files, where I use Vba to set formulas it happens that certain cells seems to switch directly after the imput of the formula, to merely the result of the formula. It looks as if the cell was treated with a Copy.PasteValues command.
It seems to happen to only certain cells, as if they where infected by something. Does somebody know where this comes from?

Thank you
Seb

 

[SkipVought]

Hi,

Please post the code in question.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SeekExcellence]

Hello Skip,

Thank you for answering my question.

Here is an example:
I run the following code through a commandbutton:

Range("FcTtc").FormulaR1C1 = "=FcTotHt+FcPort+FcTotTva"
Range("FcTotHt").FormulaR1C1 = "=SUM(DetC08)"
Range("FcTotTva").FormulaR1C1 = "=SUM(DetC10)"
Range("FcDtEch").FormulaR1C1 = "=FcDt+NbJrEch"
Range("FcTotDu").FormulaR1C1 = "=FcTtc-FcMntRgl"
Range("FcBsHt1").FormulaR1C1 = "=SUMIF(DetC09,Tax_1,DetC06)"

Afterwards in Range("FcTtc") I will find in stead of the formula I entered , "=FcTotHt+FcPort+FcTotTva", the result of the formula, in value format : for example 45.24 €

In the other ranges I will find the formula as entered through the code.
I've seen this often. It's a mystery to me...
Do you have any idea why this happens?

 

[combo]

No other code that is executed (esp. events in worksheet, other commandbutton code, possibly with invoked events)?

You can test the formula changes with:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("FcTtc")) Is Nothing Then
    MsgBox Range("FcTtc").FormulaR1C1
End If
End Sub

combo

 

[bubba100]

Under Format>Cell>Number what is the setting for offending cells? Just a guess it might be Text.

 

[SeekExcellence]

The cell format is general, number.
Thanks Combo for your idea. It is interesting. I tested your code. On the changes, the Msgbox will sometimes give me the entered formula, but then when I go look in the offending cell, i still find the value. Other times your msgbox will give me the value directly.
Sometimes everything works fine, and I find the appropiate formula in the cell.

The mystery remains....

Just beginning...love to learn from you.
Thanks for replying.

 

[Gavona]

A guess: If you put calculation to manual while populating the cells does that sort it? Check before and after calculating the worksheet. My logic is that in edit mode pressing F9 converts from formula to formula result.

Gavin

 

[SeekExcellence]

Hi Gavin,
My calculate is on manual. It seems to happens during calculate.
Pressing F9 in edit mode doesn't do anything. Should it? Edit mode is the same Design mode,right?

Just beginning...love to learn from you.
Thanks for replying.

 

[combo]

Other times your msgbox will give me the value directly.

If your code somewhere does not temporarily switch off event handling (or maybe changes name's range), the above message is displayed every time the formula in cell changes. It seems that other parts of the code in opened workbooks/add-ins change the range too and the total project should be analysed. As a first step add a break-point where the MsgBox is and after breaking the code go View>Call stack..., double-click preceding procedures in the dialog to jump to the code.

combo

 

[SeekExcellence]

Thanks combo
I tried to do what you said, but I am not sure if i do it right.
There is not really other code before this procedure to look at. I noticed that sometimes most of the range, that should show formulas, has turned to values. BUT not all cells in the range and not ALL the time. Sometimes formulas remain. I noticed it happens on 'Calculte', weither automatic ot manual. It happens in different workbooks and different sheets and ranges. It is very troublesome, because I need to all the time re-enter the formulas on every event if I want to trust my results. I really wish to solve this problem...

Just beginning...love to learn from you.
Thanks for replying.

 

[combo]

Can you see the stack dialog in break mode? Are there any other procedures in the list immediately after the formula was converted to value?
My guess is that other code occasionally changes the workbook. It can be in the same workbook or other excel extensions. To test external code, uninstall all add-ins, check if any workbook is automatically opened (personal, other, see if you can unhide window), disable macros.

combo

 

[yooneek]

Just a suggestion, but this could also be a corrupted xla (I'm assuming by the way you've wriiten some of your responses that this is an addin)... try opening a new book and copying in the sheets and modules, save as Test. Delete the addin from Excel's list. Close Excel and then open Test, see if you get the same results.