Forum Discussion
If Statement that paste/freezes another cell
My file links an online source for daily Gold Prices. I need an if statement, where if I enter a date in a certain cell (ie, cell A1), then the calculated Daily Gold Price per Gram in cell A2, sh...
The code is not a macro, but a so-called event procedure. Excel will execute it automatically each time you change the value of a cell in that worksheet. (but it will only do something when you enter a value in cell A1). You don't have to run it yourself (actually, you cannot run it since it is not a macro).
The code is stored with the worksheet, so if you duplicate the worksheet, the code will be duplicated to the copy, and run there automatically.
Thank you Hans.
It worked perfectly, not a big deal, but any reason, when I delete the date, why the previous formula does not repopulate?
It worked perfectly, not a big deal, but any reason, when I delete the date, why the previous formula does not repopulate?
- It was reference to another tab’s specific cell.
For example, tab ‘Gold Prices’’!A1 where a formula calculated Gold Price per Gram.
The code that replaced formula with a value, the formula was just a reference to the above.
Hope that was clear.Try this:
Private Sub Worksheet_Change(ByVal Target As Range) ' Did A1 change? If Not Intersect(Range("A1"), Target) Is Nothing Then Application.EnableEvents = False If Range("A1").Value <> "" Then ' Remove the formula Range("A2").Value = Range("A2").Value Else ' Restore the formula Range("A2").Formula = "='Gold Prices'!A1" End If Application.EnableEvents = True End If End Sub