Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Circular reference help | Excel Discussion (Misc queries) | |||
circular reference | Excel Worksheet Functions | |||
circular reference | Excel Discussion (Misc queries) | |||
Circular Reference | Excel Worksheet Functions | |||
Circular Reference | Excel Programming |