# Macro or Formula to add a cell to itself

#### MovieMerc

##### New Member
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:
????

### Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

#### ravishankar

##### Well-known Member
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
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
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

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

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

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

Replies
9
Views
165
Replies
5
Views
184
Replies
5
Views
196
Replies
7
Views
156
Replies
0
Views
113

1,127,567
Messages
5,625,547
Members
416,116
Latest member
Joemamasuka

### 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?

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