how to reset cells

Topic

New Reply

I have a spread sheet that automatically calculates recommended quantity for an order. However, sometimes the client wants to change the order quantity and I can overwrite the calculated amounts. Problem is that sometimes the client wants to go back to the calculated amount but since I have written over the formulas I can’t do it. I would like to have a button that says RESET so I can click it and reset those cells to their original state (the formulas). I have attached a small spreadsheet that illustrates this.

Reply | Quote

Replies

You’d have to program the formulas explicitly:

Code:

Sub ResetFormulas()
  Dim r As Long
  For r = 6 To 8
	Worksheets("Sheet1").Range("C" & r).Formula = "=B" & r & "*$C$3"
  Next r
End Sub

Reply | Quote

Thank you Hans, then how would i attach it to a button? In my example, I just created a shape and it seems I can only attach a macro. How would I attach this code to the button?

You’d have to program the formulas explicitly:

Code:

Sub ResetFormulas()
  Dim r As Long
  For r = 6 To 8
[tab][/tab]Worksheets("Sheet1").Range("C" & r).Formula = "=B" & r & "*$C$3"
  Next r
End Sub

Reply | Quote

Thank you Hans, then how would i attach it to a button? In my example, I just created a shape and it seems I can only attach a macro. How would I attach this code to the button?

The code that I posted *is* a macro, so:

[*]Right-click the shape.
[*]Select Assign Macro… from the popup menu.
[*]Select ResetFormulas from the list of macros.
[*]Click OK.

That’s all!

Note: the ResetFormulas macro should be in a standard module in the workbook, not in the worksheet or ThisWorkbook module.

Reply | Quote

Beautiful! Thanks you so much.

The code that I posted *is* a macro, so:

[*]Right-click the shape.
[*]Select Assign Macro… from the popup menu.
[*]Select ResetFormulas from the list of macros.
[*]Click OK.

That’s all!

Note: the ResetFormulas macro should be in a standard module in the workbook, not in the worksheet or ThisWorkbook module.

Reply | Quote