circular reference code
 

Hi everyone,
I have a code which allows for circular reference, I input a value i
column A and that value is added to column B. Ex. If input 100 in A,
will become 100, if I input 100 again in A, B will become 200.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim temp As Range

Set temp = Intersect(Target, Range("M4:M24"))
If temp Is Nothing Then
Else
Target.Offset(0, 1) = Target.Offset(0, 1) + Target
End If

End Sub

However, I need this to take place within the same cell, if A=100 and
input 100 in A, A will become 200. If I substitute Target.Offset (0, 1
to (0, 0) the program iterates many times. Can anybody help me solv
that problem? Thank you.

Marco

--
marksuz
-----------------------------------------------------------------------
marksuza's Profile: http://www.excelforum.com/member.php...nfo&userid=265
View this thread: http://www.excelforum.com/showthread.php?threadid=31498

circular reference code
 

Hi
at the beginning of your code add
application.enableevents=false
and at the end enable them again with
application.enableevents=true

"marksuza" wrote:


Hi everyone,
I have a code which allows for circular reference, I input a value in
column A and that value is added to column B. Ex. If input 100 in A, B
will become 100, if I input 100 again in A, B will become 200.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim temp As Range

Set temp = Intersect(Target, Range("M4:M24"))
If temp Is Nothing Then
Else
Target.Offset(0, 1) = Target.Offset(0, 1) + Target
End If

End Sub

However, I need this to take place within the same cell, if A=100 and I
input 100 in A, A will become 200. If I substitute Target.Offset (0, 1)
to (0, 0) the program iterates many times. Can anybody help me solve
that problem? Thank you.

Marcos


--
marksuza
------------------------------------------------------------------------
marksuza's Profile: http://www.excelforum.com/member.php...fo&userid=2659
View this thread: http://www.excelforum.com/showthread...hreadid=314988

circular reference code
 

try...

x = Range("whatever")
Range("whatever") = x + Range("whatever")

simple and wroks

"marksuza" wrote:


Hi everyone,
I have a code which allows for circular reference, I input a value in
column A and that value is added to column B. Ex. If input 100 in A, B
will become 100, if I input 100 again in A, B will become 200.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim temp As Range

Set temp = Intersect(Target, Range("M4:M24"))
If temp Is Nothing Then
Else
Target.Offset(0, 1) = Target.Offset(0, 1) + Target
End If

End Sub

However, I need this to take place within the same cell, if A=100 and I
input 100 in A, A will become 200. If I substitute Target.Offset (0, 1)
to (0, 0) the program iterates many times. Can anybody help me solve
that problem? Thank you.

Marcos


--
marksuza
------------------------------------------------------------------------
marksuza's Profile: http://www.excelforum.com/member.php...fo&userid=2659
View this thread: http://www.excelforum.com/showthread...hreadid=314988

circular reference code
 

Hi again,

I dont know if the code I inputed in my first message was the most
appropriate, or if that diverted from the real problem. Basically, I
want to input a number in a cell and add the number I input to the
previous number that was in that cell. Thanks again.

Marcos


--
marksuza
------------------------------------------------------------------------
marksuza's Profile: http://www.excelforum.com/member.php...fo&userid=2659
View this thread: http://www.excelforum.com/showthread...hreadid=314988

circular reference code
 

I believe you would have to include a public, module level variable in the
worksheet module to hold the current value.

This code goes in the Sheet1 module

Public A1Value As Double

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
A1Value = A1Value + Target.Value
Application.EnableEvents = False
Target.Value = A1Value
Application.EnableEvents = True
End If
End Sub

and this in the ThisWorkbook module

Private Sub Workbook_Open()
A1Value = Worksheets("Sheet1").Range("A1").Value
End Sub





On Fri, 19 Nov 2004 08:46:04 -0600, marksuza
wrote:


Hi again,

I dont know if the code I inputed in my first message was the most
appropriate, or if that diverted from the real problem. Basically, I
want to input a number in a cell and add the number I input to the
previous number that was in that cell. Thanks again.

Marcos