Macro or Formula to add a cell to itself

MovieMerc

New Member
Joined
Nov 24, 2009
Messages
5
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:
????
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

ravishankar

Well-known Member
Joined
Feb 23, 2006
Messages
3,566
Hi
Right click on the tab and choose "View code". paste the following codes
in the right hand pane.
Code:
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

Well-known Member
Joined
Mar 10, 2008
Messages
2,638
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

New Member
Joined
Nov 24, 2009
Messages
5
Hi
Right click on the tab and choose "View code". paste the following codes
in the right hand pane.
Code:
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

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,014

ADVERTISEMENT

...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.
 
Last edited:

MovieMerc

New Member
Joined
Nov 24, 2009
Messages
5
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

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,014

ADVERTISEMENT

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

New Member
Joined
Nov 24, 2009
Messages
5
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*
 

Watch MrExcel Video

Forum statistics

Threads
1,127,528
Messages
5,625,339
Members
416,096
Latest member
forevans

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top