D
Dave Peterson
A formula can only update the value in the cell that holds it.
davidhub wrote:
davidhub wrote:
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
how can i change a value in a cell from a formula in another cell?
R
Ron Rosenfeld
On Tue, 5 Jul 2005 05:00:01 -0700, "davidhub"
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")
CellToBeUpdated.Value = CellToCheck.Value
End Sub
===========================
--ron
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")
CellToBeUpdated.Value = CellToCheck.Value
End Sub
===========================
--ron
T
Tom Ogilvy
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
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
R
Ron Rosenfeld
Just a heads up on a potential problem with the code suggested.
Thanks for pointing that out. I usually do remember to DisableEvents.
Brainf&&t this time around.
--ron