...What you need for this situation is a Circular Reference using iterations...In Excel 2007, click the Office button then Excel Options. Click Formulas, and near the top check the box for 'Enable Iterative Calculation'. Then, set the value for 'Maximum Iterations' to 50...
Hi Tomlinson,
I interpreteded the OP's request to be that he was just wanting this to keep a running tally, i.e. Max iterations would be set to 1.
@ MovieMerc,
You might want to keep Tomlinson's solution in your pocket in case you have any issues with macros and security.
Also - assuming that a macro based solution works for you - you might consider using named ranges in lieu of hardcoding cell addresses. Hard-coding addresses makes your code vulnerable to insertion or deletion of rows or columns. If this worksheet/workbook will be shared, you would want to consider protecting the worksheet if you retain the use of hard-coded cell addresses.
Even if you do keep using hard-coded addresses, it is a best practice to corral "magic numbers" (or "magic addresses") at the top of your code in constants, this keeps you from having to hunt them down in code should you later need to alter them.
It is also a best practice to disable events if you are CHANGING cell values inside a CHANGE event handler lest you inadvertantly create an infinite loop.
Code:
Private Sub worksheet_change(ByVal target As Range)
Const c_strMonitoredRange1 As String = "M6"
'// this code assumes that the name was created to worksheet
'// scope, not workbook scope.
Const c_strMonitoredNamedRange2 As String = "IamCurrentlyCellM9"
Const c_intOffsetToRunningTotal As Integer = 2
'// check first one
Dim rngMonitored1 As Excel.Range, rngMonitored2 As Excel.Range
Set rngMonitored1 = Me.Range(c_strMonitoredRange1)
Set rngMonitored2 = Me.Range(c_strMonitoredNamedRange2)
If Not Intersect(target, rngMonitored1) Is Nothing Then
Application.EnableEvents = False
With rngMonitored1
.Offset(0, c_intOffsetToRunningTotal) _
= .Value + .Offset(0, c_intOffsetToRunningTotal)
End With
Application.EnableEvents = True
End If
If Not Intersect(target, rngMonitored2) Is Nothing Then
Application.EnableEvents = False
With rngMonitored2
.Offset(0, c_intOffsetToRunningTotal) _
= .Value + .Offset(0, c_intOffsetToRunningTotal)
End With
Application.EnableEvents = True
End If
End Sub
Also, if you have many more cells to which you wish to do this. Then I would probably just create one named range to bind them all and then loop each cell in the named range using a FOR EACH statement.
