Issue with formula in Cell not saving after making changes

[creaseA]

Hello all, first post here.

I am having an issue with excel not saving a new formula in specified cells. Here is some background:

Within the cells of my workbook: certain cells have the following function contained in the cell: =function_name(data1,data2,data3,etc........)

I made edits to the VBA code called "function_name" which requires me to adjust the cell input to the following: =function_name(data1,data X,data2,data3,etc........)
where "data X" is a new value I pass to the cell.

When I make the change to add the cell containing " Data X" it works fine. When I save and refresh my workbook this change does not get saved and I have to re-add data X to the cell every time I want to run the function.....

How do I make this change permanent?

Thanks!

 

[Sufiyan97]

Hi & Welcome to MrExcel.


Just make sure that you have calculation option marked as automatic under formula tab

 

[creaseA]

Hi & Welcome to MrExcel.


Just make sure that you have calculation option marked as automatic under formula tab

View attachment 87941

The calculation iption is automatic and the issue still occurs. This function exists in a table and that table has a "clear table" macro attached. Could this be causing an issue? Every input into the table I have to forcibly add "data X" as to not get an error

 

[Sufiyan97]

Sorry, I am not much familier with vba and macro, so I can't help if it is related to it.

 

[creaseA]

To update this thread: I have discovered that it is indeed the macros I have running that is causing this error. The macros I have running clears all previous data within the row the a table. However, it also reverts the change I made to the formula back to the previous formula. Is there a specific issue causing this? This part of the code was not written or recorded by me so I am not super familiar with it (fairly new to VBA language).

 

[creaseA]

To update this thread: I have discovered that it is indeed the macros I have running that is causing this error. The macros I have running clears all previous data within the row the a table. However, it also reverts the change I made to the formula back to the previous formula. Is there a specific issue causing this? This part of the code was not written or recorded by me so I am not super familiar with it (fairly new to VBA language).

Second update. I have discovered I line of code within the clearing macro that forcibly re-instates the function the to cell in which it was cleared. (The reason this took so long is that the table is extensive.)

My question is now: Is there a way around this? It seems extremely tedious (credit to the original author for the mass of equations they did this for) to have to hard code the equation back into the cell after the cell was cleared. (The used Cells(row,col).Value = "")

Would something like .ClearContents work better and clear the contents but not the formula?

Thanks,

-Aidan

 

[MARK858]

No, a formula is a content and so ClearContents will remove it