Forum Discussion
replacing text from a formula in another cell
I seek to interrogate the value in a cell (integer 0 or 1), and then arbitrarily force a text value into another free text entry cell. Example. A1 has an integer value that is unknown (and not relevant to be known). B1 has a value 0, or 1. I need a formula that will force A1 to 0, if B1 is 1. If B1 is 0, A1s values arent changed, irrespective of whether they have changed or not. Since A1 is guaranteed to change dynamically, it cannot contain a formula.
This requires VBA code (so it'll work only in the desktop version of Excel for Windows and Mac).
In your example:
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("B1").Value, Target) Is Nothing Then If Range("B1").Value = 1 Then Application.EnableEvents = False Range("A1").Value = 0 Application.EnableEvents = True End If End If End SubP.S. This assumes that the user enters 0 or 1 in cell B1. If B1 contains a formula that returns 0 or 1, use
Private Sub Worksheet_Calculate() If Range("B1").Value = 1 Then Application.EnableEvents = False Range("A1").Value = 0 Application.EnableEvents = True End If End Sub- Awesome. I didnt expect this to be a sheet specific thing, was hoping it could be cell specific because I Want to do this one cell update, to force values into a number of cells. Could I repeat line 5 above for each of the cells I want to reset?
- This actually generates a "Run-time error '424'...the explanation of which might as well be written in Mandarin for all the sense it makes. Error 424 says "object required" and the error line in debug is line 2.. "If Not Intersect(Range("E2").Value, Target) Is Nothing Then" (I am actually using E2 as the trigger and D2 as the data cell to change)
