Macro to Restore Table Cell to Original Formula

I have a macro set up in workbook to restore a table to almost new.

It removes all the rows but the first one, then deletes the hard data from the first row, leaving the formulas in the rest of the columns.

I also want it to restore a cell back to it's original formula. Recoding a macro while doing so did not work. I can't plug the formula itself into the macro as it does periodically change.

Demo workbook attached... please let me know if further clarifications are required!

Hi,

check you posted file against you screenshot, something is not OK AE4 is empty(merged with Y4). If you mean AE6 this is only the value "Entered" in. Not a formula?

BR, Alexandra

If you mean AE6, if you press "Restore to Calculated Column Formula" then the formula will be displayed but a lot of references are wrong.
So we need the sample file with the original formula before we start your code! Then we can put the original formula in the code as string and return it back after your code is done!



BR, Alexandra

@cysu11: see reply above.

Then we can put the original formula in the code as string and return it back after your code is done!

As stated: I can't plug the formula itself into the macro as it does periodically change.

Originally Posted by Tamarissa

As stated: I can't plug the formula itself into the macro as it does periodically change.

I understand, but we can "save" the formula at the start your codes, and when your code is done, formula will be back!

BR, Alexandra

i mean like this:

Please Login or Register  to view this content.

And what happens every time the formula changes? That sounds like it's still hard coded.

The end-user of this is an 'Expert' in Excel because they can add and subtract... training them on the use of the real sheet indicates having to update a macro (or even another cell outside the table to update the macro) is beyond the skill level posessed.

Original formula AE6:

Please Login or Register  to view this content.

Once again, you want to use you code to delete rows etc., right?
When you start the code, we will integrate in you code that the formula is available in AE6, will be saved/wrote in the code, at the end of you code this saved formula will be written back in AE6.

Sub test()
1. save formula
2. your code...
3. writhe back the formula in AE6
end sub

no expert knowledge needed. So you can change your formula every time, as soon as you start the code, recent formula will be saved and write back! Understand?

BR, Alexandra

Once again, you want to use you code to delete rows etc., right?

Correct.

Understand?

Yes, I understand.

When you start the code, we will integrate in you code that the formula is available in AE6

There will not ever be a formula available in AE6 except when resetting the table to prep for the next period. It will be there for possibly half an hour at most, and then changed to 'Entered' for the remaining time.
By the time this macro is run, it will have been 2 weeks since a formula was seen in this cell. Any changes to the formula made at the begining period will also never be in this cell for very long. It will ALWAYS say "Entered" at the time of running the macro.


I just want a macro do exactly what is in my screenshot. Run the "Restore to Calulated Column Formula" process.

Check this:

Please Login or Register  to view this content.

Originally Posted by Tamarissa

There will not ever be a formula available in AE6 except when resetting the table to prep for the next period. It will be there for possibly half an hour at most, and then changed to 'Entered' for the remaining time.

Hi,

OK, then we can save the formula before you change the cell to "Entered", at this time is it the correct formula, right?

BR, Alexandra

@Tajan That cleared out the cell, it did not restore it to the original formula

@cycu11 Sure... if you can figure out how to save it for 2 weeks, and take into account any changes that might be made to after saving it that original time.

Here is the original formula again:

Please Login or Register  to view this content.

As it references many other table columns, sheets, tables, etc in the original workbook, I can't add it here properly.