PDA

View Full Version : [SOLVED] Formula being replaced with result



zagrijs
06-10-2015, 07:08 AM
Hi All,

The formulas in certain column, e.g. in c1 "=b1-a1", is replaced with the result.

I assume it is caused by a setting somewhere that I am unaware of.

Your assistance would be much appreciated.

Zagrijs

Paul_Hossler
06-10-2015, 09:34 AM
The only setting I know that affects that area is under Options

Are you sure that you or a macro did not do a PasteValues to those cells?

zagrijs
06-10-2015, 10:49 AM
The only setting I know that affects that area is under Options

Are you sure that you or a macro did not do a PasteValues to those cells?



Thanks for your reply, Paul

The data has been "imported" with a macro from other workbooks, yes.

Seems that I would have to rewrite the macro.

Zagrijs

Aflatoon
06-11-2015, 12:05 AM
Just in case: this will also happen if you use a formula in a table header, or if you have a userform control whose controlsource is linked to the cell.

zagrijs
06-11-2015, 03:25 AM
Just in case: this will also happen if you use a formula in a table header, or if you have a userform control whose controlsource is linked to the cell.


Thanks Aflatoon.

It is not linked to a table header or use form control, not that I am aware of.

I have a destination workbook into which the information from source workbooks have to be imported.

In the sub I used >> worksheet.range(a,z).value = worksheet.range(a,z).value <<

The specific column had the formula to calculate the difference between two dates in the source workbook. Obviously only the value, and not the formula, was copied to the destination workbook. When I try to change the value ion the destination workbook to a formula again, it immediately reverts back to the value.

I tried to create the formula in another cell that is unaffected and copied it to a cell in the affected column. So far so good! But when I try to copy that cell's contents, the formula, to the other cells in the column, the source cell immediately reverts back to the value.

I have never come across this and it leaves me baffled.

It would seem that something was changed in the destination workbook when the values were transferred from the source workbooks and I don't have an idea what changed and how to correct is

Regards

Aflatoon
06-11-2015, 04:21 AM
It sounds as though there is a Worksheet_Change event (probably in the destination workbook) which is converting the formula back to a value. Right-click the worksheet tab, choose 'View Code' and then see if there is any code shown.

zagrijs
06-11-2015, 05:33 AM
It sounds as though there is a Worksheet_Change event (probably in the destination workbook) which is converting the formula back to a value. Right-click the worksheet tab, choose 'View Code' and then see if there is any code shown.

Thanks!

Not Worksheet_Change event, Worksheet_SelectionChange event.

Zagrijs.

PS. I have not been on the forum for a while and I don't see the "tab" where I can mark it "SOLVED"; I have put "SOLVED" in the title.

Paul_Hossler
06-11-2015, 05:43 AM
Top right under [Thread Tools] -- you (as the originator) get an extra button on your post to mark it solved

zagrijs
06-11-2015, 06:12 AM
Thanks! Done!