In the cell that you wish to change (e.g. A2) enter a formula that refers to
the cell with the formula (e.g. A1)
For example, in cell A2 enter:
=A1
If having a formula in A2 is not acceptable, then you will need to use a VBA
solution. Using an event macro, you can check the contents of A1 and write an
appropriate number into A2.
To enter the VB routine, right click on the worksheet tab and select View Code.
Then paste the code below into the window that opens.
Make the appropriate changes in your cell reference assignments.
==============================
Private Sub Worksheet_Change(ByVal Target As Range)
Dim CellToBeUpdated As Range
Dim CellToCheck As Range
Set CellToBeUpdated = Range("A2")
Set CellToCheck = Range("A1")
Just a heads up on a potential problem with the code suggested.
If you modify the macro slightly to illustrate the problem and enter a
single value in any cell, you will see, that it runs recursively:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim CellToBeUpdated As Range
Dim CellToCheck As Range
Static cnt As Long
cnt = cnt + 1
Debug.Print cnt
Set CellToBeUpdated = Range("A2")
Set CellToCheck = Range("A1")
CellToBeUpdated.Value = CellToCheck.Value
End Sub
Last number for cnt on a single cell entry was 228 (so Excel senses the
problem and corrects after 228 runs).
to prevent this, you need to suppress events
Private Sub Worksheet_Change(ByVal Target As Range)
Dim CellToBeUpdated As Range
Dim CellToCheck As Range
On Error goto ErrHandler:
Set CellToBeUpdated = Range("A2")
Set CellToCheck = Range("A1")
Application.EnableEvents = False
CellToBeUpdated.Value = CellToCheck.Value
ErrHandler:
Application.EnableEvents = True
End Sub
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.
