If Statement that paste/freezes another cell

[Ghulampopal]

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, should be frozen or pasted, keeping the Gold Price per Gram of that days.

I am not as familiar with VBAs, but any assistance is welcomed.

Thank you.

[#2]

@Ghulampopal 

Right-click the sheet tab.

Select 'View Code' from the context menu.

Copy the code listed below into the worksheet module.

Switch back to Excel.

Save the workbook as a macro-enabled workbook (*.xlsm).

Make sure that you allow macros when you open the workbook.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("A1"), Target) Is Nothing Then
        If Range("A1").Value <> "" Then
            Application.EnableEvents = False
            Range("A2").Value = Range("A2").Value
            Application.EnableEvents = True
        End If
    End If
End Sub

[#3]

@Hans Vogelaar 

Thank you good sir.

Question, do I always have to run the macro or once it’s enabled & the file type is updated/saved, then as long as the first condition is met (date inserted in A1), the paste will happen automatically? 

also, if I write the macro in tab 1, but duplicate the tab, will the macro now be enabled in tab 2 (copy of tab 1) or would I need to rewrite the macro in the tab 2? 

thank you again Hans

[#4]

@Ghulampopal 

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.

[#5]

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?

[#6]

@Ghulampopal 

The code replaces the formula with its value.

What was the formula?

[#7]

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.

[#8]

@Ghulampopal 

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

[#9]

Hans thank you for all your support.

The coding worked perfect to a tee.

Thanks again.

[#10]

Hans thanks again.

Was not trying to bother you & figure the code out myself, but I couldn’t get it.

Below was your final code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("B2"), Target) Is Nothing Then
Application.EnableEvents = False
If Range("B2").Value <> "" Then
Range("E9").Value = Range("E9").Value
Range("G9").Value = Range("G9").Value
Else
Range("E9").Formula = "='Live Gold & Silver Prices'!$J$2"
Range("G9").Formula = "='Live Gold & Silver Prices'!$J$3"
End If
Application.EnableEvents = True
End If
End Sub

How can I add to the above code, if cell B11 is less than or equal to 0, then paste & freeze that day’s date in B3 (that does not update every time I re-open the file), otherwise just show “Not Paid” in B3?

[#11]

@Ghulampopal 

Will cell B11 be edited by the user or does it contain a formula?

[#12]

It contains a sum formula from another column.

[#13]

@Ghulampopal 

Do you want to react each time a cell in that column is changed, or only when B2 is changed?

[#14]

The new request should have no effect on the previous request/code.

To give context, the date inserted in B2 (which freezes the gold/silver prices) is the date the user inserts when he/she has completed their cost analysis.

The new request/code for B3 is a date generated when the last payment has been paid.

Further detail on the new request, the user inserts all payments paid in column J, the total sum is captured in B11.
When B11 is above $0, B3 should display “Not Fully Paid”.
When B11 is equal to or less than $0, B3 should display that day’s date & should frozen, unless B11 goes above $0 again.

Hope it was clear, please let me know if you need further clarification.

[#15]

@Ghulampopal 

Thanks. Try this version:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("B2"), Target) Is Nothing Then
        Application.EnableEvents = False
        If Range("B2").Value <> "" Then
            Range("E9").Value = Range("E9").Value
            Range("G9").Value = Range("G9").Value
        Else
            Range("E9").Formula = "='Live Gold & Silver Prices'!$J$2"
            Range("G9").Formula = "='Live Gold & Silver Prices'!$J$3"
        End If
        Application.EnableEvents = True
    End If
    If Not Intersect(Range("J:J"), Target) Is Nothing Then
        Application.EnableEvents = False
        If Range("B11").Value > 0 Then
            Range("B3").Value = "Not fully paid"
        Else
            Range("B3").Value = Date
        End If
        Application.EnableEvents = True
    End If
End Sub

[#16]

Hans thanks again, worked perfectly.