restoring original formulas

[Mr_Phil]

I feel I should know this but I am drawing a blank. I have a worksheet with a grid that is populated by a formula in each cell. But sometimes users won't like the result and will type over the formula to get the number they want. I've showed them how to copy down and across to put the formula back, but most forget to do it. Is there a way to create a macro to restore the formulas to the entire grid?

 

[johnnyL]

You could create a macro to write the formulas back to the range.

 

[Akuini]

Insert a helper sheet.
Copy-paste the range formula to the same range on the helper sheet.
Write a macro to copy back the formula from the helper sheet.

 

[Mr_Phil]

Insert a helper sheet.
Copy-paste the range formula to the same range on the helper sheet.
Write a macro to copy back the formula from the helper sheet.

I like it. I have a helper sheet already that has a lot of scattered pivot tables etc that provide aggregates to other areas of the workbook.
Is there a way to copy the formulas so that it doesn’t change the cell references automatically or is that why we’d do it to the same range?

I did copy the range using find/replace first to change the equal sign to “zebra”. But, when I pasted it back to the working sheet it messed with the formatting even after find/replace to “restore” the formulas. Your answer tells me I am not crazy and that there’s merit to what I was thinking. Thanks for the insight.

 

[Akuini]

Is there a way to copy the formulas so that it doesn’t change the cell references automatically or is that why we’d do it to the same range?

I did copy the range using find/replace first to change the equal sign to “zebra”. But, when I pasted it back to the working sheet it messed with the formatting even after find/replace to “restore” the formulas.

Try something like this:
In this example the formula are in Sheet1 cells B2:C2 & C6:D6.
Select range B2:D6 then run "Sub export_formula"

Book1
	A	B	C	D
1	N
2	U	NU	NU
3	D
4	W	Ryan	Ronald	Donna
5	B
6	G		BG	QS
7	Q
8	S
Sheet1

Cell Formulas
Range	Formula
B2:C2	B2	=A1&A2
C6	C6	=A5&A6
D6	D6	=A7&A8

The result in sheet Helper is like this (note: sheet Helper must be a clean sheet):

Book1
	A	B	C	D
1
2		A1&A2	B1&B2
3
4
5
6			A5&A6	A7&A8
7
helper

To send the formulas back to Sheet1:
Select the range in sheet Helper
Run Sub import_formula

If the formulas are in a fixed range then you can replace "Selection" in the macro with the range, so you don't need to select the the range before running the macro.

Sub export_formula()
Dim c As Range
Application.ScreenUpdating = False
Application.EnableEvents = False
    For Each c In Selection.SpecialCells(xlCellTypeFormulas)
      Sheets("Helper").Range(c.Address) = Mid(c.Formula, 2)
    Next
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub


Sub import_formula()
Dim c As Range
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlManual

For Each c In Selection.SpecialCells(xlCellTypeConstants)
    With Sheets("Sheet1")
        If Not .Range(c.Address).HasFormula Then
            .Range(c.Address) = "=" & c
        End If
    End With
Next

Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlAutomatic
End Sub

 

[Mr_Phil]

Try something like this:
In this example the formula are in Sheet1 cells B2:C2 & C6:D6.
Select range B2:D6 then run "Sub export_formula"

Book1
	A	B	C	D
1	N
2	U	NU	NU
3	D
4	W	Ryan	Ronald	Donna
5	B
6	G		BG	QS
7	Q
8	S
Sheet1

Cell Formulas
Range	Formula
B2:C2	B2	=A1&A2
C6	C6	=A5&A6
D6	D6	=A7&A8

The result in sheet Helper is like this (note: sheet Helper must be a clean sheet):

Book1
	A	B	C	D
1
2		A1&A2	B1&B2
3
4
5
6			A5&A6	A7&A8
7
helper

To send the formulas back to Sheet1:
Select the range in sheet Helper
Run Sub import_formula

If the formulas are in a fixed range then you can replace "Selection" in the macro with the range, so you don't need to select the the range before running the macro.

Sub export_formula()
Dim c As Range
Application.ScreenUpdating = False
Application.EnableEvents = False
    For Each c In Selection.SpecialCells(xlCellTypeFormulas)
      Sheets("Helper").Range(c.Address) = Mid(c.Formula, 2)
    Next
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub


Sub import_formula()
Dim c As Range
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlManual

For Each c In Selection.SpecialCells(xlCellTypeConstants)
    With Sheets("Sheet1")
        If Not .Range(c.Address).HasFormula Then
            .Range(c.Address) = "=" & c
        End If
    End With
Next

Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlAutomatic
End Sub

I think that will do it. Thank you so much for your help.

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.