Macro or Formula to add a cell to itself

[MovieMerc]

I will use the actual Cells as my examples.

Situation:
M6 - Input number
O6 - Results of M6 + O6

If M6 = 0 then O6 = 0 ; If M6 = 2 then O6 = 2
If M6 = 8 then O6 = 10 ; and so on.
I want O6 to increase each time by the amount entered in M6.

Problem:
I can not find or create a formual that will do this. I'm not sure HOW to write a macro to do this either. I have tried to create a formula and each time I enter a number in M6 the result in O6 is {n * 50}. I type 1 in M6 and O6 equals 50. I then type 3 in M6 and O6 equals 200 {50 + 150}.

Solution:
????

 

[ravishankar]

Hi
Right click on the tab and choose "View code". paste the following codes
in the right hand pane.

Private Sub worksheet_change(ByVal target As Range)
If Not Intersect(target, Range("M6")) Is Nothing Then
Cells(6, 15) = Cells(6, 15) + Cells(6, 13)
MsgBox "Updated"
End If
End Sub

Save it. Now everytime you change M6, O6 gets cumulatively updated
ravi

 

[mvptomlinson]

Hi MM,

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.

Go back to your spreadsheet and put the formula

=M6+O6

into cell O6. When you type a 1 in M6, 50 will appear in O6. If you then delete the value in M6, O6 remains. If you type a 3 in M6, O6 will change to 200 (50 + 3*50).

Hope that helps!

 

[MovieMerc]

Hi
Right click on the tab and choose "View code". paste the following codes
in the right hand pane.

Private Sub worksheet_change(ByVal target As Range)
If Not Intersect(target, Range("M6")) Is Nothing Then
Cells(6, 15) = Cells(6, 15) + Cells(6, 13)
MsgBox "Updated"
End If
End Sub

Save it. Now everytime you change M6, O6 gets cumulatively updated
ravi

Ravi,

Thank you so much. This formula is exactly what I was looking for.
One last Question. How would I apply the same formula to another cell? Say "M9" for instance?

 

[Greg Truby]

...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.

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.

 

[MovieMerc]

Greg,

Would this Macro then make both "M6" and "M9" behave the same?
As in when I input a number into M6 it makes O6 keep a running total, and the same when I input a number into M9?
I'll try it and see what happens..*S*

 

[Greg Truby]

The macro is showing you how to handle both. In the case of M6, we simply define the address as a constant at the top of the routine. In the case of M9, the code assumes that you created a worksheet-level name called IAmCurrentlyCellM9 which points to cell M9.

 

[MovieMerc]

Greg,

Sorry but my level of expertise is not up to yours. *Smile*

I copy/paste this macro into sheet 1 and it isn't working.
I tried following what you said and it really is over my head.
Do you ahve a *simple* version for *simple* people like me..*smile*

 

[MovieMerc]

D'oh!...
Ok I figured it out.
Thanks Greg...it works perfectly!!