clear cell automatically

eyoung99

New Member
Joined
Feb 9, 2009
Messages
6
i have created a circular reference on purpose, with the intent to enter data in one column that is automatically added and stored in another column. for example, i have cell d3 set up as a formula =b3+d3. originally, d3 is set to 0. when i enter data into b3 it updates d3 by the value entered in b3.

if it were just the one cell there would be no problem, but i am doing this for d3, d4, and d5 respectively. the problem occurs as follows. when i enter my value for b3, d3 is updated correctly. if i enter a value in b4 to update d4 and have not cleared out the value in b3, the value for d3 is again increased by the value in b3.

i can manually clear out each value after i have entered them, but i was wondering if there is a way to clear contents of the cell once hitting the enter button or if there is a better way to avoid this problem.
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
I think that you would be better off with code. Remove the formulas, right click the sheet tab, select View Code and paste in

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B3:B5")) Is Nothing Then Exit Sub
Application.EnableEvents = False
With Target
    .Offset(, 2).Value = .Offset(, 2).Value + .Value
    .ClearContents
End With
Application.EnableEvents = True
End Sub

Now observe the effect of entering values in B3, B4 and B5.
 
Upvote 0
Hi and welcome to the board!!!
Use a macro
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column <> 2 Then Exit Sub
Cells(Target.Row, "C") = Cells(Target.Row, "C") + Target
Application.EnableEvents = False
Target.ClearContents
Application.EnableEvents = True
End Sub
Place in the WorkSheet module. RightClick the sheet tab and choose "View Code"

lenze
 
Upvote 0
No to BAlGaInTl. Probably the others are correct, but I have never used code before. Thank you; I'll give it a shot.
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,329
Members
448,564
Latest member
ED38

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