This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

How do I retrieve a value from cell 2 to cell 1 if cell 2 contains a value and cell 1 is empty, but prioritize to let a value be input in cell 2 while the formula in cell 2 is protected in the background?

I have this formula in cell 1 (although it needs to be changed according to my requirement I state in this question):

=IFERROR(IF(T5<>"";T5;W5);"")

In cell 2, I only enter a number like 79 or 79.5 for example.

So first priority is that I can enter a value (for example 89 or 89.5) in cell 1 while the formula is protected in the background.

Second priority is that if cell 1 is empty (the formula still there, so it's really not 'empty') the value from cell 2 should be retrieved to cell 1 while the formula is protected in the background.

Third priority is that if cell 2 is also empty no error message or anything should be written in cell 2 ("iferror").

I have this formula in cell 1 (although it needs to be changed according to my requirement I state in this question):

=IFERROR(IF(T5<>"";T5;W5);

In cell 2, I only enter a number like 79 or 79.5 for example.

So first priority is that I can enter a value (for example 89 or 89.5) in cell 1 while the formula is protected in the background.

Second priority is that if cell 1 is empty (the formula still there, so it's really not 'empty') the value from cell 2 should be retrieved to cell 1 while the formula is protected in the background.

Third priority is that if cell 2 is also empty no error message or anything should be written in cell 2 ("iferror").

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with Premium.
Start your 7-day free trial.

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

A cell can have a value or a formula, not both.

A formula can not be protected in the background.

When you type a value in the cell, the formula is lost.

The "correct" solution is to have input cells for the values, and result cells with formulas.

With VBA it is possible to restore the formula when the cell value is deleted.

It could be like this (code in the worksheet module), using A1 for the cell with the formula.

The formula here is:

If A2 is not empty (or an empty string) use the value in A2, else display an empty string.

```
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing And Target.Count = 1 Then
Application.EnableEvents = False
If IsEmpty(Target) Then
Target.Formula = "=IF(A2<>"""",A2,"""")"
End If
Application.EnableEvents = True
End If
End Sub
```

S28 contains this formula: =IFERROR(S27+S10+S11+S12+S

S29 contains this formula: =IFERROR(S28;"")

If cell S28 is empty (or rather no value there, formula is still there), also leave S29 empty (no error message).

If cell S28 contains a value, copy this value to S29.

I might want to modify this copied value, and do that. Then I might want to go back to the value that was retreived from S28 instead, so I want the formula to run again (restore the formula when the cell value is deleted).

I tried Microsoft Excel Objects and chose the sheet I am working with, but I can't insert any module here.

I tried now to close the VBA Editor and return to the worksheet, but it didn't work, the formula was deleted when I overwrote S29 with a value.

(Instead of A1 and A2, I replaced with S28 and S29.)

To restore the formula in S29, S29 must be compared to Target

Intersect(Target, Range("S29"))

And it must be the formula for S29 that is used

Target.Formula = "=IFERROR(S28,"""")"

So when you delete S29, the formula is inserted again.

I think the following code does what you want.

Set value in S29 to value in S28 when S28 change as result of calculation, and S29 has a formula.

If S29 has a value nothing happens.

When S29 is deleted, the formula is restored.

Replace all existing code in the worksheet module.

```
Option Explicit
Dim S28Value As Variant
Private Sub Worksheet_Calculate()
If S28Value <> Range("S28") And Range("S29").HasFormula = True Then
Application.EnableEvents = False
Range("S29") = Range("S28")
Application.EnableEvents = True
End If
S28Value = Range("S28")
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("S29")) Is Nothing And Target.Count = 1 Then
Application.EnableEvents = False
If IsEmpty(Target) Then
Target.Formula = "=IFERROR(S28,"""")"
End If
Application.EnableEvents = True
End If
End Sub
```

"Sub or function not defined"

The event macros start by the events worksheet calculation and worksheet change.

Macro names can not have spaces, but you don't have to create names.

The names must be exactly as they are in order to work.

And must be in the worksheet module they have to work for.

When you open a workbook with macros, a message appears between the worksheet and the ribbon.

Use that to activate macros.

It is possible to enable all macros, and avoid the message (Excel settings, security).

Microsoft recommend not to, but it is also annoying to do it all the time.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trialS28Value holds the previous value for S28, to compare with on the next calculation.

The variable is for one value only, and is not designed to have all 8 values for the range P28:W28.

Try the code below for the range P28:W29.

If the value in a cell in P28:W28 change, and the cell below in P29:W29 has a formula, the value in the cell replace the formula.

Restore of the formula is done one by one, so when you delete W29 only W29 get the formula restored.

I have added error trapping to be sure that EnableEvents are set again if an error occurs, which is more likely the more code there is.

It does not happen automatically when the sub ends.

```
Option Explicit
Dim KeepValue(1 To 8) As Variant
Private Sub Worksheet_Calculate()
Dim i As Integer
On Error GoTo ErrorOut
Application.EnableEvents = False
For i = 1 To 8
If KeepValue(i) <> Cells(28, i + 15) And Cells(29, i + 15).HasFormula = True Then
Cells(29, i + 15) = Cells(28, i + 15)
End If
KeepValue(i) = Cells(28, i + 15)
Next i
ErrorOut:
Application.EnableEvents = True
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("P29:W29")) Is Nothing And Target.Count = 1 Then
On Error GoTo ErrorOut
Application.EnableEvents = False
If IsEmpty(Target) Then
Target.FormulaR1C1 = "=IFERROR(R[-1]C,"""")"
End If
ErrorOut:
Application.EnableEvents = True
End If
End Sub
```

http://stackoverflow.com/questions/18639581/why-does-excel-vba-prompt-me-for-a-macro-name-when-i-press-run-sub

I don't understand what you are doing.

You don't need to change anything.

When you copy the entire code to the worksheet module, replacing everything there is, it should start automatically.

And you can't have 2 Worksheet_Calculate subs in the same module.

Try attached file.

When you open it, a message box should appear saying "Events are enabled".

If not run the sub ReEnable_Events() in the worksheet module.

Then you can change the cells on the sheet, and see what is happening.

Restore-formula.xlsm

Also, I should be able to enter a new formula in any cell. But the main thing is: I should be able to overwrite a formula with anything in any cell, but if I delete this contents and the cell is empty the old formula should be restored.

Or maybe it's not feasible to have it for the whole workbook because if I would make small adjustments like moving areas etc. the old formulas would be retrieved and I would loose control (would get formulas in cells I loose control over).

It's like a table, and I will have this table 14 more times on the same worksheet, having totally 15 exactly the same copies of this table below each other, so the columns are always the same, only need to add 14 x 2 more rows in the subroutine.

The second is expanding the compare solution to have more ranges.

It has a specific defined formula to restore.

This will work even if the cells has a value at start.

Here is a test with 3 blocks.

There are 2 named ranges, CompareRange and RestoreRange.

CompareRange =Sheet1!$P$28:$W$28,Sheet1

RestoreRange =Sheet1!$P$29:$W$29,Sheet1

RestoreRange is the rows below CompareRange, and that is the cells where the formula is restored when cell value is deleted.

Go to Names on the Formula tab to add more ranges to the names.

Select the name and the ranges are shown in the reference box at the bottom.

Use the icon to the right of the reference box and define all ranges by pointing.

Select the first part of the range.

Hold down Ctrl key and select the other parts with the mouse.

The Ctrl key can be released between selections, but if you forget to press again at next selection, all is lost and you have to select once more.

When all is selected go back to the names definitions (use the icon to the right), use the acceptance icon to the left, or press Enter and accept the warning.

The first scenario is to restore any formula.

I have incorporated that in the same workbook.

The formulas are read at first selection change, and it takes a few seconds to initialize.

Later changes works much faster.

VBA knows the last cell used of the sheet, and here that is Z73, so all formulas from A1 to Z73 are remembered.

If something is added to the right or below that range, the remembered range is expanded.

You can also copy formulas.

But if you insert or delete rows or columns it can be messed up, since the original formulas are linked to the original rows and columns.

Try delete column Y where there is a sum formula in Y10.

The formula will be restored in Y10, since Z10 is empty, but then the value in Z9 is in the same column as the formula.

So to make major changes to the sheet set up, open the workbook without macros enabled, make the changes, save and reopen with macros.

The scenarios you set up are interesting to explore how to make, but I think this question has grown way beyond the initial one with just one cell.

Restore-formula-2.xlsm

Microsoft Excel

From novice to tech pro — start learning today.

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with Premium.
Start your 7-day free trial.

=IF(T5<>"";T5;IF(W5="";"";