circular reference code

[marksuza]

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

 

[Guest]

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

 

[Guest]

try...

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

simple and wroks

 

[marksuza]

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

 

[Myrna Larson]

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