I am using excel 2010 and have a macro enabled worksheet that has been through a number of changes. The formulas presently in the worksheet work fine but one had an error. When I change the formula, the result of the formula properly enters into the cell but excel is then taking the formula result as the cell value and not keeping the formula. I've copied and pasted, typed it in, cut and pasted and all result in the same error. Any ideas?

✔ Best Answer

Our IT department rolled some update out over the weekend and now the excel sheet is working fine....not sure what the change was but now things appear to be working as expected.

This statement doesn't make sense:

When Ichangethe formula, the result of the formula properly enters into the cell but excel is then taking the formula result as the cell value and not keeping the formula.I assume you mean "

When I" You can'tenterthe formula...changethe formula if Excel has replaced it with a value.If that assumption is correct, then let's talk about the macro you mentioned. We are going to need to know more about that. Just as an example, this macro would do exactly what you are describing for any formula entered in A1.

Note:This is just one of many, many examples of how a macro could be causing what you are experiencing.

Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$1" Then Target = Target.Value End If End SubAs soon as you enter the formula in A1, (e.g. =SUM(B1,C1) Excel will calculate the SUM first and then the Worksheet_Change code will fire and the macro will replace the formula with the result.

BTW...if something like that is happening, it is not an "error". Excel is simply doing what the VBA code is telling it to do.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03

I'd be glad to send you the actual sheet. The macro is a form to "reset" certain fields back to a predetermined status and that's it. The formula's involved are located in cells not part part of the macro in anyway....at least none that i can see.

for example. The macro would reset cell A1 to "PICK FROM LIST" which is a data validation list item.

in Cell B1, if I insert a formula such as =2+2 the cell shows the result of 4 and the formula bar also only shows a 4. In fact, I've tried in multiple cells and that seems to be consistent.However, in Cell C1 I have an equation that was built when the form was originally created and it still has the equation set in the formula bar and it still operates. When I try to amend the formula I lose the formula entirely and again excel keeps only the result of the equation input.

I sent you a PM with an email address. Send the workbook and I'll take a look at it, but it won't be until this evening or tomorrow, EST.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

Our IT department rolled some update out over the weekend and now the excel sheet is working fine....not sure what the change was but now things appear to be working as expected.

Thanks for trying to help me solve this....greatly appreciated.

Ask Your Question

Weekly Poll

Do you think Salesforce should have bought Slack?

Discuss in The Lounge

Poll History